This post will guide you through connecting Power BI Desktop to your SCCM Infra and pulling out awesome indepth hardware info based off a SQL Query.
And for advanced users – skip the rest and Download PBIX
-
The SQL Query – [Download SQL Query]
The Query brings in Machine Name, AD site, User Name, Top User, OS, Service Pack, Manufacturer, Model, Serial Number, BIOS Date, BIOS Version, Managed Date, Memory, Memory Slots, Type of Proc, Disk Size, PC Type.
———————————————————————————————–
SELECT DISTINCT
sys.Name0 ‘Machine’,
sys.AD_Site_Name0 ‘ADSite’,
CS.UserName0 ‘User Name’,
CASE
WHEN U.TopConsoleUser0 = ‘-1’ OR U.TopConsoleUser0 IS NULL THEN ‘N/A’
ELSE U.TopConsoleUser0
END as TopUser,
REPLACE (OS.Caption0, ‘Microsoft Windows’,’Win’) OS,
REPLACE (OS.CSDVersion0,’Service Pack’,’SP’) ‘Service Pack’,
CS.Manufacturer0 ‘Manufacturer’,
CS.Model0 Model,
BIOS.SerialNumber0 ‘Serial Number’,
BIOS.ReleaseDate0 as BIOSDate,
BIOS.SMBIOSBIOSVersion0 as BIOSVersion,
(SELECT CONVERT(DATE,sys.Creation_Date0)) ‘Managed Date’,
SUM(ISNULL(RAM.Capacity0,0)) ‘Memory (MB)’,
COUNT(RAM.ResourceID) ‘# Memory Slots’,
REPLACE (cs.SystemType0,’-based PC’,”) ‘Type’,
SUM(D.Size0) / 1024 AS ‘Disk Size GB’,
CASE SE.ChassisTypes0
when ‘1’ then ‘Other’
when ‘2’ then ‘Unknown’
when ‘3’ then ‘Desktop’
when ‘4’ then ‘Low Profile Desktop’
when ‘5’ then ‘Pizza Box’
when ‘6’ then ‘Mini Tower’
when ‘7’ then ‘Tower’
when ‘8’ then ‘Portable’
when ‘9’ then ‘Laptop’
when ’10’ then ‘Notebook’
when ’11’ then ‘Hand Held’
when ’12’ then ‘Docking Station’
when ’13’ then ‘All in One’
when ’14’ then ‘Sub Notebook’
when ’15’ then ‘Space-Saving’
when ’16’ then ‘Lunch Box’
when ’17’ then ‘Main System Chassis’
when ’18’ then ‘Expansion Chassis’
when ’19’ then ‘SubChassis’
when ’20’ then ‘Bus Expansion Chassis’
when ’21’ then ‘Peripheral Chassis’
when ’22’ then ‘Storage Chassis’
when ’23’ then ‘Rack Mount Chassis’
when ’24’ then ‘Sealed-Case PC’
else ‘Undefinded’
END AS ‘PC Type’
FROM
v_R_System SYS
INNER JOIN (
SELECT
Name0,
MAX(Creation_Date0) AS Creation_Date
FROM
dbo.v_R_System
GROUP BY
Name0
) AS CleanSystem
ON SYS.Name0 = CleanSystem.Name0 and sys.Creation_Date0 = CleanSystem.Creation_Date
LEFT JOIN v_GS_COMPUTER_SYSTEM CS
ON sys.ResourceID=cs.ResourceID
LEFT JOIN v_GS_PC_BIOS BIOS
ON sys.ResourceID=bios.ResourceID
LEFT JOIN (
SELECT
A.ResourceID,
MAX(A.[InstallDate0]) AS [InstallDate0]
FROM
v_GS_OPERATING_SYSTEM A
GROUP BY
A.ResourceID
) AS X
ON sys.ResourceID = X.ResourceID
INNER JOIN v_GS_OPERATING_SYSTEM OS
ON X.ResourceID=OS.ResourceID and X.InstallDate0 = OS.InstallDate0
LEFT JOIN v_GS_PHYSICAL_MEMORY RAM
ON sys.ResourceID=ram.ResourceID
LEFT OUTER join dbo.v_GS_LOGICAL_DISK D
ON sys.ResourceID = D.ResourceID and D.DriveType0 = 3
LEFT outer join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP U
ON SYS.ResourceID = U.ResourceID
LEFT JOIN dbo.v_GS_SYSTEM_ENCLOSURE SE on SYS.ResourceID = SE.ResourceID
LEFT JOIN dbo.v_GS_ENCRYPTABLE_VOLUME En on SYS.ResourceID = En.ResourceID
GROUP BY
sys.Creation_Date0, sys.Name0,
sys.AD_Site_Name0 , CS.UserName0 ,REPLACE (OS.Caption0, ‘Microsoft Windows’,’Win’) , REPLACE (OS.CSDVersion0,’Service Pack’,’SP’) ,
CS.Manufacturer0 , CS.Model0 ,BIOS.SerialNumber0 , REPLACE (cs.SystemType0,’-based PC’,”),
CASE
WHEN U.TopConsoleUser0 = ‘-1’ OR U.TopConsoleUser0 IS NULL THEN ‘N/A’
ELSE U.TopConsoleUser0
END,
CASE SE.ChassisTypes0
when ‘1’ then ‘Other’
when ‘2’ then ‘Unknown’
when ‘3’ then ‘Desktop’
when ‘4’ then ‘Low Profile Desktop’
when ‘5’ then ‘Pizza Box’
when ‘6’ then ‘Mini Tower’
when ‘7’ then ‘Tower’
when ‘8’ then ‘Portable’
when ‘9’ then ‘Laptop’
when ’10’ then ‘Notebook’
when ’11’ then ‘Hand Held’
when ’12’ then ‘Docking Station’
when ’13’ then ‘All in One’
when ’14’ then ‘Sub Notebook’
when ’15’ then ‘Space-Saving’
when ’16’ then ‘Lunch Box’
when ’17’ then ‘Main System Chassis’
when ’18’ then ‘Expansion Chassis’
when ’19’ then ‘SubChassis’
when ’20’ then ‘Bus Expansion Chassis’
when ’21’ then ‘Peripheral Chassis’
when ’22’ then ‘Storage Chassis’
when ’23’ then ‘Rack Mount Chassis’
when ’24’ then ‘Sealed-Case PC’
else ‘Undefinded’
END ,BIOS.ReleaseDate0 ,
BIOS.SMBIOSBIOSVersion0ORDER BY sys.Name0
———————————————————————————————–
-
2. The Query Output – sample
This should give an idea of what all data can be played with for reference.
3. Download the PBIX file.
4. Dashboard when first launched.
Dont panic – this is just the clean slate view with no data.
5. Reconfigure the SQL Server
Home > Edit Queries.
Query Editor Window > Home > Data Source Settings
On Data Source Setting Window > Change Source
Enter the Server and Database details – then Hit ‘ok‘
Voila ! you are done ! You dynamic Power BI Dashboard is available. Use the Data Slicers on top or interactive donut pie in the center to filter and analyze – then export the end results to csv.
Go ahead put in your feedback / comments once you give this a try.
Trying to understand this. I first need to install PowerBI to use this?
LikeLike
Yes
LikeLike
updated the post with the power bi download link/step.
LikeLike
I have similar dashboards working now. However, by profession I am an SCCM admin not a DBA admin. I’m trying to determine if there are any potential issues doing direct queries into the hardware inventory with regards to SQL locks. I understand that Hardware Inventory may always be writing data, should I be concerned with database corruption if, say, my report refreshes during a write?
LikeLike
SQL’s select statements are fairly safe and haven’t faced issues so far in 10 years or so with SCCM and SQL Queries.
LikeLike