Tuesday 12 April 2011

System Center Dashboards - SCCM & SCOM SQL Snippets

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

6 comments:

John Quirk said...

Love it. I'd almost forgotten the dashboard existed!!!

JQ

Wes said...

Thanks so much!

Steve Beaumont said...

You're welcome :)

Unknown said...

Tx for a good post. I see you refer to a table "Custom_Message_descriptions" in some. Can you please supply a definition/content for this table?

James E Lutz said...

You’ve written nice post, I am gonna bookmark this page, thanks for info. I actually appreciate your own position and I will be sure to come back here.
Office Furniture

Anonymous said...

Do I need to create a new dataset for each Chart?