Power BI - Essential SQL Queries for IT & Technology

 

Essential SQL Queries for IT & Technology 

System Logs & Monitoring

• Recent error logs:

SELECT LogID, Timestamp, Severity, Message
FROM SystemLogs
WHERE Severity = 'Error'
ORDER BY Timestamp DESC;

• Count of errors by application:

SELECT ApplicationID, COUNT(*) AS ErrorCount
FROM SystemLogs
WHERE Severity = 'Error'
GROUP BY ApplicationID;

• Average response time per application:

SELECT ApplicationID, AVG(ResponseTime) AS AvgResponseTime
FROM AppPerformance
GROUP BY ApplicationID;

---

User Activity & Security

• Active users in the last 24 hours:

SELECT COUNT(DISTINCT UserID) AS ActiveUsers
FROM UserActivity
WHERE LoginTime >= DATEADD(DAY, -1, GETDATE());

• Failed login attempts:

SELECT UserID, COUNT(*) AS FailedAttempts
FROM LoginAttempts
WHERE Status = 'Failed'
GROUP BY UserID;

• Top 10 users by system usage:

SELECT UserID, SUM(SessionDuration) AS TotalUsage
FROM UserSessions
GROUP BY UserID
ORDER BY TotalUsage DESC
LIMIT 10;

---

Infrastructure & Network

• Server uptime percentage:

SELECT ServerID,
       SUM(CASE WHEN Status = 'Up' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS UptimePercent
FROM ServerStatus
GROUP BY ServerID;

• Average CPU utilization:

SELECT ServerID, AVG(CPUUsage) AS AvgCPU
FROM ServerMetrics
GROUP BY ServerID;

• Network latency by region:

SELECT Region, AVG(Latency) AS AvgLatency
FROM NetworkLogs
GROUP BY Region;

---

IT Operations & Support

• Tickets by category (IT helpdesk):

SELECT Category, COUNT(*) AS TicketCount
FROM SupportTickets
GROUP BY Category;

• Average resolution time for IT issues:

SELECT AVG(DATEDIFF(HOUR, CreatedDate, ResolvedDate)) AS AvgResolutionTime
FROM SupportTickets
WHERE Status = 'Closed';

• Escalated tickets:

SELECT TicketID, Category, EscalationLevel
FROM SupportTickets
WHERE EscalationLevel > 0;

---

Key Notes

• Indexes: Use on UserID, ServerID, ApplicationID, and Timestamp for faster queries.
• Dashboards: These queries feed into IT monitoring dashboards (Power BI).
• Best practice: Always validate log timestamps and ensure normalization across systems.

---