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

ConfigMgr : Creating queries, collections and reports for Window Server 2008 Core in System Center Configuration Manager 2007

17_desktop.jpg

System Center Configuration Manager 2007 SP2 allows client installation on Windows Server 2008 Core systems* but it does not differentiate between Windows Server 2008 and Windows Server 2008 Core machines in either queries, collections or reports.

* Configuration Manager 2007 SP2 Supported Configurations: http://technet.microsoft.com/en-us/library/ee344146.aspx

To determine which SKU a particular machine is running, you can use the following command:

wmic OS get OperatingSystemSKU

SKUs for Windows Server 2008:

  • 12 = Windows Server 2008 Datacenter Edition (core)
  • 13 = Windows Server 2008 Standard Edition (core)
  • 14 = Windows Server 2008 Enterprise Edition (core)
  • 40 = Windows Server 2008 Standard Edition without Hyper-V (core)

Reference: http://msdn.microsoft.com/en-us/library/aa394239(VS.85).aspx

This field is only present on Windows Vista / Server 2008 upwards.  Running the command above on a Windows XP or Windows Server 2003 computer will result in the following error:

Node – Machine1
ERROR:
Code = 0x80041017
Description = Invalid query
Facility = WMI

NOTE The table and field in SQL are:

Table – v_HS_OPERATING_SYSTEM
Column – OperatingSystemSKU0

In WMI it is:

Class – SMS_G_System_OPERATING_SYSTEM

And

Property – OperatingSystemSKU

The first step is to modify the sms_def.mof by adding a section in Hardware Inventory to collect the version information from the clients within their WMI repository.  Modify the mof file at \<SCCM Server Install Folder>\inboxes\clifiles.scr\hinv\ sms_def.mof to include the following:

[ SMS_Report     (TRUE),
SMS_Group_Name (“Operating System”),
SMS_Class_ID   (“MICROSOFT|OPERATING_SYSTEM|1.0”) ]
add >
[SMS_Report (TRUE)     ]
uint32     OperatingSystemSKU;

Once complete, you can do the following to speed up the process of the client reporting this information back to Configuration Manager.

– On the ConfigMgr console: Modify the hardware inventory agent to run at quicker interval.

– On the client: Trigger a ‘hardware inventory cycle’ on the client via the Configuration Manager applet in Control Panel

To verify that the collection took place, open the ConfigMgr admin console and navigate to Computer Management > Collections and right click on the client machine.  Click Start and then Resource Explorer > Hardware and click on Operating System.  A new column named  ‘OperatingSystemSKU’ should appear.

Creating a WQL Query

Once the OS information is being collected, you can use the query below to search for all Windows Server 2008 Core or Windows Server 2008 R2 Core systems only. To query for just Windows Server 2008 you can change %Server 6.% to %Server 6.0% or for Windows Server 2008 R2 you can change it to %Server 6.1%.

select SMS_R_System.Name, SMS_R_System.SMSAssignedSites, SMS_R_System.IPAddresses, SMS_R_System.IPSubnets, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceId, SMS_R_System.NetbiosName from  SMS_R_System join  SMS_G_System_OPERATING_SYSTEM on SMS_R_System.ResourceID=SMS_G_System_OPERATING_SYSTEM.ResourceID where SMS_R_System.OperatingSystemNameandVersion like “%Server 6.%”
and SMS_G_System_OPERATING_SYSTEM.OperatingSystemSKU IN (12,13,14,40)

 

Creating a Collection

A new Collection can be created by importing the WQL Query Statement we used above.

 

Creating a Report

The SQL statement below will search for all unique Windows Server 2008 or Windows Server 2008 R2 machines:

select distinct v_R_System.Name0 as ‘Machine Name’, v_HS_OPERATING_SYSTEM.caption0 as ‘OS Type’, CSDVersion0 as ‘Service Pack Level’, case v_HS_OPERATING_SYSTEM.OperatingSystemSKU0 when 12 then ‘Yes’ when 13 then ‘Yes’ when 14 then ‘Yes’ when 40 then ‘Yes’ else ‘No’ end as ‘Windows Core’ from v_R_System join v_HS_OPERATING_SYSTEM on v_R_System.resourceID = v_HS_OPERATING_SYSTEM.resourceID where v_HS_OPERATING_SYSTEM.OperatingSystemSKU0 is not null

The SQL statement above  will exclude all machines that have NULL in the SKU field and display a column indicating if the machine is Core or not.

 

source ( my own post ) :

https://blogs.technet.microsoft.com/configurationmgr/2012/03/26/creating-queries-collections-and-reports-for-window-server-2008-core-in-system-center-configuration-manager-2007/