SCCM / SQL – More Inventory Queries

Here’s a few more queries I’ve found helpful in the past week or two.  These were used on Configuration Manager 1602 and 1606 with SQL Server 2014 Sp1.  I will be taking a break for a while, but I would appreciate any feedback you have (and don’t forget to click the rating please?)

Computers with SSD’s Installed

SELECT DISTINCT
  ResourceID,
  SystemName0 AS ComputerName,
  Model0 AS Model,
  DeviceID0 AS DeviceID,
  Index0 AS [Index],
  InterfaceType0 AS Interface,
  Manufacturer0 AS Manufacturer,
  MediaType0 AS MediaType,
  Partitions0 AS Partitions,
  Size0 AS Capacity
FROM 
  dbo.v_GS_DISK
WHERE 
  Model0 LIKE '%SSD%'

Installation Counts for Operating Systems

SELECT DISTINCT 
  Caption0 AS OSName, 
  COUNT(*) AS Installs
FROM 
  dbo.v_GS_OPERATING_SYSTEM
GROUP BY 
  Caption0
ORDER BY
  Installs DESC

Machine Counts by BIOS Description

SELECT 
  DISTINCT Description0 AS BIOS,
  COUNT(*) AS Machines
FROM 
  dbo.v_GS_PC_BIOS
GROUP BY
  Description0
ORDER BY
  Machines DESC

Machine Counts by Time Zone

SELECT DISTINCT 
  CurrentTimeZone0 AS TimeZone,
  COUNT(*) AS Machines
FROM 
  dbo.v_GS_COMPUTER_SYSTEM
GROUP BY
  CurrentTimeZone0
ORDER BY
  CurrentTimeZone0

Count of Machines Online vs. Offline, by Site Server

SELECT DISTINCT
  SiteSystemName,
  ServerID,
  OnlineClients,
  OfflineClients,
  TotalClients
FROM 
  dbo.v_BgbServerCurrent
  LEFT OUTER JOIN dbo.v_BoundarySiteSystems ON
    dbo.v_BoundarySiteSystems.BoundaryID = ServerID
ORDER BY 
  SiteSystemName

Cheers!