Since I had to redo our System Center Dashboard today, I took the time to pull off most of our SQL Snippets and thought I'd post them here.
Some of these have been posted in other places, and I take no credit for all of them, but I forget half the places I got them from. So if they are your SQL and you're reading this, thank you. Leave a comment and I'll add a reference to you.
Configuration Manager SQL Snippets
Overall Machine Count
Type - Linear Gauge
Title - Overall Machines Count
Description -
Actual Value - OverallClientCount
Operator - <
Goal - 3000
Scale Start – 0
Scale End - 3100
Aggregation - Min
SELECT
count(DISTINCT name0) AS OverallClientCount
FROM v_R_System
WHERE client0=1 and obsolete0=0 and active0=1
Machines Count By Network
Type - Pie Chart
Title - Machines Count By Network
Legend - Network
Y-Axis - ClientCount
SELECT
CASE sms_assigned_sites0
WHEN 'SC1' THEN 'Network 1'
WHEN 'SC2' THEN 'Network 2'
END AS "Network", count(distinct name0) as ClientCount
FROM v_RA_System_SMSAssignedSites sas join v_R_System sys on sas.resourceID=sys.resourceID
WHERE client0=1 AND obsolete0=0 AND Active0=1
GROUP BY CASE sms_assigned_sites0
WHEN 'SC1' THEN 'Network1'
WHEN 'SC2' THEN 'Network 2'
END
ORDER by 2 desc
Machines Count By Operating System
Type - Pie Chart
Title - Machines Count By Operating System
Legend - Operating System
Y-Axis - Client Count
SELECT TOP 5
OPSYS.Caption0 as 'Operating System', COUNT(*) AS 'Client Count'
FROM v_GS_OPERATING_SYSTEM OPSYS
INNER JOIN v_R_System sys on OPSYS.ResourceID = sys.ResourceID
INNER JOIN v_RA_System_SMSAssignedSites Asys on sys.ResourceID = Asys.ResourceID
WHERE
sys.client0=1 AND sys.obsolete0=0 AND sys.active0=1 AND
Asys.SMS_Assigned_Sites0 LIKE @SiteCode
GROUP BY OPSYS.Caption0
ORDER BY Count(*) desc
Machines Count By Domains
Type - Pie Chart
Title - Machines Count By Domains
Legend - Clientdomain
Y-Axis - ClientCount
SELECT TOP 10
Resource_domain_OR_Workgr0 as ClientDomain, count(distinct name0) as ClientCount
FROM v_R_System
WHERE client0=1 AND obsolete0=0 AND Active0=1
GROUP BY Resource_domain_OR_Workgr0
ORDER BY 2 desc
Machines Count By Server/Workstation OS Type
Type - Pie Chart
Title - Machines Count By Server/Workstation OS Type
Legend - ClientOSType
Y-Axis - ClientCount
SELECT
ClientOSType = CASE
WHEN Operating_System_Name_and0 LIKE '%workstation%' THEN 'Total WorkStation OS'
WHEN Operating_System_Name_and0 LIKE '%server%' THEN 'Total Server OS'
END,
COUNT(*)AS ClientCount
FROM v_r_system (NOLOCK)
WHERE
client0 =1 AND obsolete0 =0 AND Active0=1
AND Operating_System_Name_and0 IS NOT NULL
GROUP BY CASE
WHEN Operating_System_Name_and0 LIKE '%workstation%' THEN 'Total WorkStation OS'
WHEN Operating_System_Name_and0 LIKE '%server%' THEN 'Total Server OS'
END
Machines Count By 32/64 bit OS Type
Type - Pie Chart
Title - Machines Count By 32/64 bit OS Type
Legend - MachineType
Y-Axis - ClientCount
SELECT systemtype00 as MachineType, count(distinct os.name0) as ClientCount
FROM Computer_System_DATA pro
JOIN v_R_System os on os.resourceid=pro.machineid
WHERE
os.client0=1 AND os.obsolete0=0 AND Active0=1
AND systemtype00 in ('X86-based PC','x64-based PC')
GROUP BY systemtype00
ORDER BY 2 desc
Machines Count By Desktops/Notebooks Type
Type - Pie Chart
Title - Machines Count By Desktops/Notebooks Type
Legend - Workstation Type
Y-Axis – ClientCount
SELECT
CASE ChassisTypes0
WHEN '1' THEN 'Server'
WHEN '2' THEN 'Server'
WHEN '3' THEN 'Desktop'
WHEN '4' THEN 'Low-Profile Desktop'
WHEN '5' THEN 'Pizza-Box'
WHEN '6' THEN 'Desktop'
WHEN '7' THEN 'Desktop'
WHEN '8' THEN 'Mobile Device'
WHEN '9' THEN 'Mobile Device'
WHEN '10' THEN 'Mobile Device'
WHEN '11' THEN 'Hand-Held'
WHEN '12' THEN 'Mobile Device'
WHEN '13' THEN 'All-in-One'
WHEN '14' THEN 'Sub-Notebook'
WHEN '15' THEN 'Desktop'
WHEN '16' THEN 'Desktop'
WHEN '17' THEN 'Server'
WHEN '18' THEN 'Mobile Device'
WHEN '19' THEN 'Sub-Chassis'
WHEN '20' THEN 'Bus-Expansion chassis'
WHEN '21' THEN 'Peripheral Chassis'
WHEN '22' THEN 'Storage Chassis'
WHEN '23' THEN 'Server'
WHEN '24' THEN 'Sealed-Case PC'
WHEN '65' THEN 'Tablet PC'
ELSE 'Unknown'
END AS "Workstation Type", count(distinct sys.name0) as ClientCount from
v_GS_SYSTEM_ENCLOSURE ENC
INNER JOIN
v_R_System SYS ON ENC.ResourceID = SYS.ResourceID
WHERE
sys.client0=1 AND sys.obsolete0=0 AND active0=1
GROUP BY
CASE ChassisTypes0
WHEN '1' THEN 'Server'
WHEN '2' THEN 'Server'
WHEN '3' THEN 'Desktop'
WHEN '4' THEN 'Low-Profile Desktop'
WHEN '5' THEN 'Pizza-Box'
WHEN '6' THEN 'Desktop'
WHEN '7' THEN 'Desktop'
WHEN '8' THEN 'Mobile Device'
WHEN '9' THEN 'Mobile Device'
WHEN '10' THEN 'Mobile Device'
WHEN '11' THEN 'Hand-Held'
WHEN '12' THEN 'Mobile Device'
WHEN '13' THEN 'All-in-One'
WHEN '14' THEN 'Sub-Notebook'
WHEN '15' THEN 'Desktop'
WHEN '16' THEN 'Desktop'
WHEN '17' THEN 'Server'
WHEN '18' THEN 'Mobile Device'
WHEN '19' THEN 'Sub-Chassis'
WHEN '20' THEN 'Bus-Expansion chassis'
WHEN '21' THEN 'Peripheral Chassis'
WHEN '22' THEN 'Storage Chassis'
WHEN '23' THEN 'Server'
WHEN '24' THEN 'Sealed-Case PC'
WHEN '65' THEN 'Tablet PC'
ELSE 'Unknown'
END
ORDER BY 2 desc
Machines By Physical/Virtual Type
Type - Pie Chart
Title - Machines By Physical/Virtual Type
Legend - Machine Type
Y-Axis - ClientCount
SELECT
CASE Model0
WHEN 'VMware Virtual Platform' THEN 'Virtual Machine'
ELSE 'Phyical Machine'
END AS "Machine Type",
COUNT(distinct sys.name0) as ClientCount
FROM Computer_System_DATA ass join v_R_System sys on ass.machineID=sys.resourceID
WHERE
client0=1 AND obsolete0=0 AND Active0=1
GROUP BY
CASE Model0
WHEN 'VMware Virtual Platform' THEN 'Virtual Machine'
ELSE 'Phyical Machine'
END
ORDER BY 2 desc
PrinterCount
Type - Bar Chart
Title - Count of Printers
Description -
Legend - Type
Y-Axis - Count of Devices
SELECT 'Local' AS Type, COUNT(dbo.v_GS_PRINTER_DEVICE.DeviceID0) AS [Count of Devices]
FROM dbo.v_GS_PRINTER_DEVICE INNER JOIN
dbo.v_R_System ON dbo.v_GS_PRINTER_DEVICE.ResourceID = dbo.v_R_System.ResourceID
WHERE (dbo.v_R_System.Name0 LIKE '%PC%' OR
dbo.v_R_System.Name0 LIKE '%tb%' OR
dbo.v_R_System.Name0 LIKE '%lt%') AND (v_GS_PRINTER_DEVICE.PortName0 LIKE 'LPT%' OR
v_GS_PRINTER_DEVICE.PortName0 LIKE 'USB%' OR
v_GS_PRINTER_DEVICE.PortName0 LIKE 'DOT%' OR
v_GS_PRINTER_DEVICE.PortName0 LIKE 'ESD%')
UNION
SELECT TOP (100) PERCENT 'Network' as [Type], COUNT(dbo.v_GS_PRINTER_DEVICE.DeviceID0) AS [Count of Devices]
FROM dbo.v_GS_PRINTER_DEVICE INNER JOIN
dbo.v_R_System ON dbo.v_GS_PRINTER_DEVICE.ResourceID = dbo.v_R_System.ResourceID
WHERE (dbo.v_R_System.Name0 LIKE '%##PrintServerName##%')
MFDCount
Type - Bar Chart
Title - Count of MultiFunction Devices
Description -
Legend - Type
Y-Axis - Count of MFDs
SELECT 'MultiFunction Devices' AS Type, COUNT(v_GS_PRINTER_DEVICE.DriverName0) AS [Count of MFDs]
FROM v_GS_PRINTER_DEVICE INNER JOIN
v_R_System ON v_GS_PRINTER_DEVICE.ResourceID = v_R_System.ResourceID
WHERE (v_GS_PRINTER_DEVICE.DriverName0 LIKE 'Sharp%') AND (v_R_System.Name0 = '##PrintServerName##')
HWandSWInventoryLast7Days
Type - Data Grid
Title - Client Count and % with Hardware & Software Inventory in last 96 hrs. by Site
Header Text | Column |
Site Code | AssignedSite |
Total Clients | TotalActiveClients |
Type - Linear Gauge
Title - Client % with Hardware Inventory in last 96 Hours
Description -
Actual Value - HW Percentage
Operator - >=
Goal - 80
Scale Start - 0
Scale End - 100
Aggregation - Average
Type - Linear Gauge
Title - Client % with Hardware Inventory in last 96 Hours
Description -
Actual Value - HW Percentage
Operator - >=
Goal - 80
Scale Start – 0
Scale End - 100
Aggregation - Average
DECLARE @olddate datetime
,@NullVal datetime
SET @olddate = DATEADD(day,-7, GETUTCDATE())
SELECT sites.SMS_Assigned_Sites0 AS AssignedSite
,TotalSys.Total AS TotalActiveClients
,SuccSys.Succ AS HWSuccess
,SuccSW.Succ AS SWSuccess
,CONVERT(decimal(5,2),(SuccSys.Succ*100.00/TotalSys.Total)) AS 'HW Percentage'
,CONVERT(decimal(5,2),(SuccSW.Succ*100.00/TotalSys.Total)) AS 'SW Percentage'
FROM v_RA_system_smsassignedsites sites
INNER JOIN (
SELECT sit.SMS_Assigned_Sites0 AS AssSite
,COUNT(DISTINCT sis.Netbios_Name0) AS Succ
FROM v_RA_System_SMSAssignedSites sit
INNER JOIN v_R_System sis
ON sit.ResourceID = sis.ResourceID
INNER JOIN v_gs_workstation_status sts
ON sis.ResourceID = sts.ResourceID
AND sis.Client0 = 1
AND sis.Obsolete0 = 0
AND sis.Active0 = 1
AND sts.LastHWScan > @olddate
GROUP BY sit.sms_assigned_sites0
) SuccSys
ON sites.SMS_Assigned_Sites0 = SuccSys.AssSite
INNER JOIN (
SELECT sit.SMS_Assigned_Sites0 AS AssSite
,COUNT(DISTINCT sis.Netbios_Name0) AS Succ
FROM v_RA_System_SMSAssignedSites sit
INNER JOIN v_R_System sis
ON sit.ResourceID = sis.ResourceID
INNER JOIN v_GS_LastSoftwareScan sts
ON sis.ResourceID = sts.ResourceID
AND sis.Client0 = 1
AND sis.Obsolete0 = 0
AND sis.Active0 = 1
AND sts.LastScanDate > @olddate
GROUP BY sit.SMS_Assigned_Sites0
) SuccSW
ON SuccSW.AssSite = sites.SMS_Assigned_Sites0
INNER JOIN (
SELECT sit.SMS_Assigned_Sites0 AS AssSite
,COUNT(DISTINCT sis.Netbios_Name0) AS Total
FROM v_RA_System_SMSAssignedSites sit
INNER JOIN v_R_system sis
ON sit.ResourceID = sis.ResourceID
AND sis.Client0 = 1
AND sis.Obsolete0 = 0
AND sis.Active0 = 1
GROUP BY sit.SMS_Assigned_Sites0
) TotalSys
ON sites.SMS_Assigned_Sites0 = TotalSys.Asssite
GROUP BY sites.SMS_Assigned_Sites0
,TotalSys.Total
,SuccSys.Succ
,SuccSW.Succ
ORDER BY 4 DESC
ClientCommsInLast24Hrs Type - Data Grid
Title - Client Count for WSUS Scan, Heartbeat and MP Communication in last 24 hrs
Header Text | Column |
Site Code | SiteCode |
Total Clients | TotalClient |
Type - Bar Chart
Title - Client Count for WSUS Scan, Heartbeat and MP Communication in last 24 hrs
Description -
Legend - SiteCode
Y-Axis - TotalClient, ScanSuccess, Heartbeat, MPComunicationSuccess
DECLARE @olddate datetime
SET @olddate=DATEADD(hour,-24, GETUTCDATE())
SELECT tot.SiteCode
,tot.TotalClient
,ptc.ScanTotal [ScanSuccess]
,CONVERT(decimal(5,2),(ptc.ScanTotal*100.00/tot.TotalClient)) [ScanSuccessPerc]
,hrt.HBCount [Heartbeat]
,CONVERT(decimal(5,2),(hrt.HBCount*100.00/tot.TotalClient)) [HeartbeatPerc]
,mpc.MPComunicatonSuccess
,mpc.MPComunicatonFailure
,mpc.MPComunicatonTotal
,CONVERT(decimal(5,2),(mpc.MPComunicatonSuccess*100.00/tot.TotalClient)) MPComunicatonSuccessPerc
FROM (
SELECT sit.SMS_Assigned_Sites0 [SiteCode]
,COUNT(1) TotalClient
FROM v_R_System sis
INNER JOIN v_RA_System_SMSAssignedSites sit
ON sis.ResourceID = sit.ResourceID
AND sis.Client0 = 1
AND sis.Obsolete0 = 0
AND sis.Active0 = 1
GROUP BY sit.SMS_Assigned_Sites0
) tot
LEFT OUTER JOIN (
SELECT sit.SMS_Assigned_Sites0 [SiteCode]
,COUNT(1) [ScanTotal]
FROM v_updateScanStatus upp
INNER JOIN v_statenames stn
ON upp.LastScanState = stn.StateID
AND stn.TopicType = '501'
AND stn.StateName = 'Scan completed'
INNER JOIN v_RA_System_SMSAssignedSites sit
ON upp.ResourceID = sit.ResourceID
AND upp.LastScanPackageLocation LIKE 'http%'
AND upp.LastScanTime > @olddate
GROUP BY upp.LastScanState
,sit.SMS_Assigned_Sites0
) ptc
ON tot.SiteCode = ptc.SiteCode
LEFT OUTER JOIN (
SELECT sit.SMS_Assigned_Sites0 AS [SiteCode]
,COUNT (sis.name0) AS [HBCount]
FROM v_R_System sis
INNER JOIN (
SELECT a.ResourceID, a.AgentSite, b.AgentTime
FROM v_AgentDiscoveries a
INNER JOIN (
SELECT ResourceID, MAX(AgentTime) AS AgentTime
FROM v_AgentDiscoveries
WHERE AgentName LIKE '%Heartbeat%'
AND AgentTime > @olddate
GROUP BY ResourceID
) b
ON a.ResourceID = b.ResourceID
AND a.AgentTime = b.AgentTime
) hrt
ON sis.ResourceId = hrt.ResourceID
INNER JOIN v_RA_System_SMSAssignedSites sit
ON sis.resourceID = sit.ResourceID
AND sis.Client0 = 1
AND sis.Obsolete0 = 0
GROUP BY sit.SMS_Assigned_Sites0
) hrt
ON tot.SiteCode = hrt.SiteCode
LEFT OUTER JOIN (
SELECT sub.[Site] AS [SiteCode]
,SUM(CASE sub.HealthState WHEN 1 THEN sub.Cnt END) AS [MPComunicatonSuccess]
,SUM(CASE sub.HealthState WHEN 2 THEN sub.Cnt END) AS [MPComunicatonFailure]
,SUM(sub.Cnt) AS [MPComunicatonTotal]
FROM (
SELECT sit.SiteCode [Site]
,chs.HealthState
,COUNT(chs.HealthState) [Cnt]
FROM v_Site sit
INNER JOIN v_ClientHealthState chs
ON sit.SiteCode = chs.AssignedSiteCode
AND chs.HealthType = '1000'
AND chs.LastHealthReportDate > @olddate
AND sit.[Type] = 2
GROUP BY sit.SiteCode
,chs.HealthState
) sub
GROUP BY sub.[Site]
) mpc
ON tot.SiteCode = mpc.SiteCode
ClientDeploymentErrorMsg
Type - Data Grid
Title - .
Header Text | Column |
Last Message State ID | LastMessageStateID |
Last State Message Description | StateDescription |
Client Counts | ClientsCount |
Type - Pie Chart
Title - Client Deployment Error Messages for Last 7 days
Description -
Legend - LastMessageStateID
Y-Axis - ClientsCount
select Top 10 StateDescription,
LastMessageStateID, count(*) 'ClientsCount'
from v_ClientDeploymentState
where LastMessageStateID not in ('400','700','100','500')
and DeploymentBeginTime >DATEADD(day,-8, getdate())
group by StateDescription, LastMessageStateID
order by 3 desc
InactiveAndObsoleteClientCount
Type - Data Grid
Title - % of Obsolete & Inactive Client Count
Header Text | Column |
Site Code | AssignedSite |
Total Clients (including inactive & obsolete) | Total Count |
Type - Linear Gauge
Title - % of Inactive Client Count
Description -
Actual Value - % of Inactive Clients
Operator - <
Goal - 25
Scale Start – 0
Scale End - 100
Aggregation - Average
Type - Linear Gauge
Title - % of Obsolete Client Count
Description -
Actual Value - % of Obsolete Clients
Operator - <
Goal - 25
Scale Start - 0
Scale End - 100
Aggregation - Average
select sub.AssignedSite,
sum(sub.cnt ) 'Total Count',
SUM(CASE SUB.ACTIVE0 when 1 then SUB.cnt END) AS 'Total Active Clients',
SUM(CASE SUB.ACTIVE0 when 0 then SUB.cnt END) AS 'Total Inactive Clients',
SUM(CASE SUB.obsolete0 when 1 then SUB.cnt END) AS 'Total Obsolete Clients'
,ROUND((CAST(SUM(CASE SUB.ACTIVE0 when 0 then SUB.cnt END) AS float) / SUM(sub.Cnt)) * 100,2) AS '% of Inactive Client Count'
,ROUND((CAST(SUM(CASE SUB.obsolete0 when 1 then SUB.cnt END) AS float) / SUM(sub.Cnt)) * 100,2) AS '% of Obsolete Client Count'
FROM
(select sit.sms_assigned_sites0 AssignedSite,
sys.active0,sys.obsolete0 ,
COUNT(*) cnt
from v_R_System sys
join v_RA_System_SMSAssignedSites sit on sys.resourceID=sit.resourceID
and (sys.Active0 is not null and sys.Obsolete0 is not null)
group by sit.sms_assigned_sites0,sys.active0,sys.obsolete0
)sub
group by sub.AssignedSite
ClientHealthStateErrorMessagesforLast7Days
Type - Data Grid
Title - .
Header Text | Column |
Error Code | ErrorCode |
Error Symbolic Name | SymbolicName |
Type - Pie Chart
Title - Client Health State Error Messages for Last 7 days
Description -
Legend - ErrorCode
Y-Axis - ClientCount
select Top 10 CH.ErrorCode,
CU.Description,CU.[SymbolicName],
COUNT(*) as ClientClient
from v_ClientHealthState CH
left join SCCM_NELC.dbo.Custom_Message_descriptions CU on CH.ErrorCode=cu.errorcode
where healthstate != 1
and LastHealthReportDate >DATEADD(day,-8, getdate())
Group by CH.ErrorCode,CU.Description,CU.[SymbolicName]
order by 4 desc
WindowsUpdateScanErrorforLast7days
Type - Data Grid
Title - .
Header Text | Column |
Symbolic Name | SymbolicName |
Last Error Code | LastErrorCode |
Type - Pie Chart
Title - Windows Update Scan Error for Last 7 days
Description -
Legend - LastErrorCode
Y-Axis - ClientCount
select Top 10 LastErrorCode,
CU.Description,CU.[SymbolicName],
COUNT(*) as ClientCount
from v_updateScanStatus up
join v_r_system sys on sys.resourceid = up.resourceid
left join SCCM_NELC.dbo.Custom_Message_descriptions CU on up.LastErrorCode=cu.errorcode
where lastscantime >DATEADD(day,-8, getutcdate())
and lastscantime < getutcdate()
and LastErrorCode != 0
group by LastErrorCode,CU.Description,CU.[SymbolicName]
order by 4 desc
ClientErrorStatusMessagesforlast7days
Type - Data Grid
Title - .
Header Text | Column |
Client Component | Component |
Message ID | MessageID |
Type - Pie Chart
Title - Top 10 Client Error Status Messages for last 7 days
Description -
Legend - MessageID
Y-Axis - Count
SELECT top 10 MessageID
, sm.Severity
, COUNT(*) AS 'Count'
, MAX(Time) AS 'LastOccurred'
, Component
,cu.Description
FROM v_StatusMessage sm WITH (NOLOCK)
join SCCM_NELC.dbo.Custom_Message_descriptions CU on sm.messageid=cu.errorcode
WHERE ModuleName = 'SMS Client'
AND sm.Severity != 1073741824
AND Time > DATEADD(DAY, -8, GetDate())
GROUP BY MessageID, MachineName, Component, sm.Severity, cu.description
ORDER BY 3 desc
UnapprovedClientCountbySite
Type - Column Chart
Title - Unapproved Client Count by Site
Description -
Legend - SMS_Assigned_Sites0
Y-Axis - ClientCount
Select AssignedSite.SMS_Assigned_Sites0,
COUNT(*) as ClientCount
from ClientKeyData CD
join v_RA_System_SMSAssignedSites AssignedSite on AssignedSite.resourceID=CD.RecordID
where CD.ApprovalStatus <> 1
group by AssignedSite.SMS_Assigned_Sites0
order by 2 desc
Servers By Physical/Virtual Type
Type - Pie Chart
Title - Servers By Physical/Virtual Type
Description -
Legend - Machine Type
Y-Axis - ClientCount
SELECT TOP (100) PERCENT CASE Model0 WHEN 'VMware Virtual Platform' THEN 'Virtual Machine' ELSE 'Phyical Machine' END AS [Machine Type], COUNT(sys.Name0)
AS ClientCount
FROM dbo.Computer_System_DATA AS ass INNER JOIN
dbo.v_R_System AS sys ON ass.MachineID = sys.ResourceID INNER JOIN
dbo.v_FullCollectionMembership ON sys.ResourceID = dbo.v_FullCollectionMembership.ResourceID
WHERE (sys.Client0 = 1) AND (sys.Obsolete0 = 0) AND (sys.Active0 = 1) AND (dbo.v_FullCollectionMembership.CollectionID = 'SMS000DS')
GROUP BY CASE Model0 WHEN 'VMware Virtual Platform' THEN 'Virtual Machine' ELSE 'Phyical Machine' END
ORDER BY COUNT(sys.Name0) DESC
Operating System and Service Pack
Type - Data Grid
Title - .
Header Text | Column |
Operating System | Operating System |
Service Pack | Service Pack |
Count | Count |
Type - Pie Chart
Title - Operating System and Service Pack
Description -
Legend - Operating System
Y-Axis - Count
SELECT OPSYS.Caption0 as 'Operating System', OPSYS.CSDVersion0 as 'Service Pack', COUNT(*) AS 'Count'
FROM v_GS_OPERATING_SYSTEM OPSYS
inner join v_R_System sys on OPSYS.ResourceID=sys.ResourceID
GROUP BY OPSYS.Caption0, OPSYS.CSDVersion0
ORDER BY OPSYS.Caption0, OPSYS.CSDVersion0
Operations Manager SQL Snippets
Number of Alerts Per Day (28 Days)
Type - Bar Chart
Title - Number of Alerts
Description - Per Day (28 Days)
Legend - Date
Y-Axis - Alerts
USE OperationsManagerDW
SELECT CONVERT(VARCHAR(10), DBCreatedDateTime, 101) AS Date, COUNT(*) AS Alerts
FROM Alert.vAlert
WHERE (DBCreatedDateTime BETWEEN DATEADD(day, -27, GETDATE()) AND GETDATE())
GROUP BY CONVERT(VARCHAR(10), DBCreatedDateTime, 101)
ORDER BY Date DESC
Top 20 Alerts by Alert Count
Type - Pie Chart
Title - Top 20 Alerts by Alert Count
Description -
Legend - AlertStringName
Y-Axis - AlertCount
SELECT TOP 20 SUM(1) AS AlertCount, AlertStringName
FROM Alertview WITH (NOLOCK)
WHERE TimeRaised is not NULL
GROUP BY AlertStringName
ORDER BY AlertCount DESC
Top 20 Alerts by Repeat Count
Type - Pie Chart
Title - Top 20 Alerts
Description - by Repeat Count
Legend - AlertStringName
Y-Axis - RepeatCount
SELECT TOP 20 SUM(RepeatCount+1) AS RepeatCount, AlertStringName
FROM Alertview WITH (NOLOCK)
WHERE Timeraised is not NULL
GROUP BY AlertStringName
ORDER BY RepeatCount DESC
Servers Generating the Most Events
Type - Pie Chart
Title - Servers Generating the Most Events
Description -
Legend - ComputerName
Y-Axis - TotalEvents
SELECT top 20 LoggingComputer as ComputerName, COUNT(*) AS TotalEvents
FROM EventallView with (NOLOCK)
GROUP BY LoggingComputer
ORDER BY TotalEvents DESC
Number of Monitored Servers
Type - Circular Gauge
Title - Number of Monitored Servers
Description -
Actual Value - NumManagedComps
Operator - <
Goal - 200
Scale Start – 0
Scale End - 250
Aggregation - Min
SELECT COUNT(*) AS NumManagedComps FROM (
SELECT bme2.BaseManagedEntityID
FROM BaseManagedEntity bme WITH (NOLOCK)
INNER JOIN BaseManagedEntity bme2 WITH (NOLOCK) ON bme2.BaseManagedEntityID = bme.TopLevelHostEntityID
WHERE bme2.IsDeleted = 0
AND bme2.IsDeleted = 0
AND bme2.BaseManagedTypeID = (SELECT TOP 1 ManagedTypeID FROM ManagedType WHERE TypeName = 'microsoft.windows.computer')
GROUP BY bme2.BaseManagedEntityID
) AS Comps
Number of Active Alerts
Type - Circular Gauge
Title - Number of Active Alerts
Description -
Actual Value - ActiveAlerts
Operator - <
Goal - 20
Scale Start - 0
Scale End - 100
Aggregation - Min
SELECT COUNT(1) AS ActiveAlerts FROM Alert WHERE ResolutionState = '0'
Number of Alerts by Type
Type - Column Chart
Title - Number of Alerts by Type
Description -
Legend - State
Y-Axis - GroupCount
SELECT [State] = CASE ManagedEntityGenericView.HealthState
WHEN 1 THEN 'Healthy'
WHEN 2 THEN 'Warning'
WHEN 3 THEN 'Critical'
ELSE 'Unknown'
END
, COUNT(1) AS GroupCount
FROM ManagedEntityGenericView INNER JOIN
ManagedTypeView ON ManagedEntityGenericView.MonitoringClassId = ManagedTypeView.Id
WHERE (ManagedTypeView.Name LIKE 'Microsoft.Windows.Computer')
GROUP BY ManagedEntityGenericView.HealthState
ORDER BY GroupCount
Total Number of Alerts Closed by User
Type - Pie Chart
Title - Total Number of Alerts Closed by User
Description -
Legend - statesetbyuserid
Y-Axis - Number of Alerts
select statesetbyuserid, count(*) as 'Number of Alerts'
from Alert.vAlertResolutionState ars
where resolutionstate = '255'
group by statesetbyuserid
order by 'Number of Alerts' DESC
Installed Management Packs
Type - Data Grid
Title - Installed Management Packs
Header Text | Column |
Management Pack Name | MPFriendlyName |
Version | MPVersion |
SELECT MPName, MPFriendlyName, MPVersion, MPIsSealed
FROM ManagementPack WITH(NOLOCK)
ORDER BY MPName
