the-main-dashboard

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

  1. 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.SMBIOSBIOSVersion0

    ORDER BY sys.Name0

    ———————————————————————————————–

  2. 2. The Query Output – sample

query output

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.

clean-dashboard

5. Reconfigure the SQL Server

Home > Edit Queries.

step1

Query Editor Window > Home > Data Source Settings

step2

On Data Source Setting Window > Change Source

step3

Enter the Server and Database details – then Hit ‘ok

step4

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.the-main-dashboard
Go ahead put in your feedback / comments once you give this a try.

Advertisements

Join the conversation! 3 Comments

  1. Trying to understand this. I first need to install PowerBI to use this?

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

Category

Uncategorized

Tags

, , , , , , , , , , , , , , , , ,