Power BI : Active Directory v/s SCCM Boundaries : [FREE PBIX]

Purpose :

Active Directory Sites and System Center Configuration Manager Boundaries are hard to keep in sync – especially in an environment where there are regular changes and several team managing each technology separately.

This Power BI dashboard solution will help analyze and reduce down the gaps in SCCM. The dashboard will help SCCM administrators / architects to resolve issues where clients are not covered – this helps in Client Health / Reporting and Compliance.

The dashboard is based off the Powershell script posted on Technet by Scott Breen and can be downloaded from here : https://gallery.technet.microsoft.com/Validate-Boundary-Group-d85ed496

How to use this :

A. The script can be configured to run on schedule via Scheduled Tasks on a server or via System Center Orchestrator.

B. The output CSV can be placed on a network share on local folder.

C. Power BI will read this csv and perform the necessary DAX operations.

D. Power BI can further be used to publish on o365 on the Power BI portal. Power BI gateway can be configured so that the content is autoupdated on a schedule. Embedded Power BI options can be used to inject the dashboard into Sharepoint or any site.

Backend engine :

The output [download csv here] of the script needs some work to be done and looks like this >

output

The last column is most important in this csv output – the result ‘False’ indicates the machine is not covered by any SCCM Boundary. The other columns provide plenty of information – but its not really presentable or understandable from the get go.

The conversion of the csv data from crude data to human readable format is performed by Power BI and via DAX. The csv file before conversion is available here – BoundaryCheck-csv

The PowerBI dashboard is configured to pull information from C:\PowerBI\adsite-vs-boundaries\Boundarycheck.csv

Get it now !

 

Download the PBIX.

Advertisements

Power BI : ConfigMgr : Indepth Hardware Inventory Report – (Free Pbix, query and guide)

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.

Power BI : Microsoft Office Telemetry Dashboard

Microsoft Office’s Telemetry Dashboard is an essential add-on for giving your environment an Office Health Index – where you have it, licensing, upgrade, tracking issues, AppCompat etc. The insight telemetry provide is priceless and yet free.
Here the data sources are both the telemetry database, telemetry excel dashboard, and the SCCM database.
Installation + Configuration of Telemetry : https://technet.microsoft.com/en-us/library/jj863580.aspx?f=255&MSPPError=-2147217396
Configure and deploy the dashboard : https://technet.microsoft.com/en-us/library/jj853312.aspx?f=255&MSPPError=-2147217396
want the pbix file ? 
share this post on twitter or fb and comment with the link of ur shared post & your email in the comment window below.

Power BI : How to make quick awesome looking dashboard for SCCM

An awesome example of what power bi can do – and its all interactive and dynamic content – which sync’s with your sql data in real time. a little bit of sql and little bit of excel does the magic.

The dashboard above was created to show the remnants of Office 2010 in the environment.

what all do we have in here ?
A dynamically created map based on location pulled from SCCM
a scrolling ticker indicating increase or decrease in count as per trends
count of machines per location
trends indicated by a simple graph 
a dynamic water level indicators for data.
a dynamic pyramid based breakup
Query used to pull from SQL >
——————————————————————————————
SELECT       distinct vrs.AD_Site_Name0, vrs.Distinguished_Name0, vrs.Netbios_Name0, vrs.Resource_Domain_OR_Workgr0, vrs.User_Name0, sof.ProductName0, sof.ProductVersion0, sof.Publisher0
FROM            v_R_System AS vrs LEFT OUTER JOIN
                         v_GS_INSTALLED_SOFTWARE AS sof ON vrs.ResourceID = sof.ResourceID
WHERE        sof.ProductName0 like ‘Microsoft Office Standard 2010’ or  sof.ProductName0 like  ‘Microsoft Office Professional 2010’ or  sof.ProductName0 like  ‘Microsoft Office Professional Plus 2010’
——————————————————————————————
want the pbix file ? 
share this post on twitter or fb and comment with the link of ur shared post & your email in the comment window below.