In telemetry we trust?

More than patches

Telemetry is one of those things that tends to divide a room. On one hand it’s productive and accurate feedback for your product of choice and on the other hand it’s big brother spying on what you’re doing in that product. I wanted to share a recent experience with you based on my recent upgrade of Microsoft System Center Configuration Manager (SCCM) to the recent 1710 release as it may make you change your mind.

The long awaited 1710 release was made available last week a little before midnight in the UK on 20/11/17 (or 11/20 in the US Smile). The next morning I fired up my lab which runs the current branch version of SCCM, ran the early update ring PowerShell script and proceeded to whizz through the wizard in a speedy not hastily fashion. This was lab after all so on with the day job and I’ll check…

View original post 689 more words


ConfigMgr : Hardware Inventory flow

Being Configuration Manager Administrator

SCCM ..(Hardware Inventory)

Hardware Inventory

Hardware inventory is a feature in itself and functions independent to the other features of SCCM, but it closely depends on a successful deployment.

SCCM Setup has to complete successfully. The Hardware inventory feature on the server side solely depends on setup and successful SQL install. All the tables from the default sms_def.mof should get correctly created in SQL with the right credentials.

Similarly, for the hardware inventory agent to function as per requirements on the advanced client the client setup should have completed successfully and the client should have access to the MP for the advanced client. On the Advanced client the Inventory agent’s functionality is dependent on the running of the SMS Agent Host service (ccmexec).

The entire hardware inventory feature is dependent on the sms_def.mof file present in the clifiles.srchinv folder on the SiteServer. If that file is deleted or made corrupt…

View original post 1,169 more words

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 :

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 >


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.

ConfigMgr : Query to find SCCM client versions (ConfigMgr 2012)


download : SQLQuery-find-agentsversions


select sys.Client_Version0, "CM Name"=
case sys.Client_Version0
when '4.00.6487.2000' then 'CM07 SP2 (4.00.6487.2000)'
when '4.00.6487.2188' then 'CM07 R3 (4.00.6487.2188)'
when '4.00.6487.2187' then 'CM07 R3 (4.00.6487.2187)'
when '4.00.6487.2157' then 'CM07 R3 (4.00.6487.2157)'
when '5.00.0000.0000' then 'CM12 RTM (5.00.0000.0000)'
when '5.00.7804.1000' then 'CM12 SP1 (5.00.7804.1000)'
when '5.00.7804.1202' then 'CM12 SP1 CU1 (5.00.7804.1202)'
when '5.00.7804.1300' then 'CM12 SP1 CU2 (5.00.7804.1300)'
when '5.00.7804.1400' then 'CM12 SP1 CU3 (5.00.7804.1400)'
when '5.00.7958.1000' then 'CM12 R2 (5.00.7958.1000)'
when '5.00.7958.1101' then 'CM12 R2 KB 2905002(5.00.7958.1101)'
when '5.00.7958.1203' then 'CM12 R2 CU1 (5.00.7958.1203)'
when '5.00.7958.1303' then 'CM12 R2 CU2 (5.00.7958.1303)'
when '5.00.7958.1401' then 'CM12 R2 CU3 (5.00.7958.1401)'
else 'Others(non-Clients)'
End,count(*) [Total]
from v_R_System sys
where sys.Name0 not like 'unknown' and
sys.Client_Version0 not like '' and sys.Client_Version0 not like '0.0%'
group by sys.Client_Version0
order by Client_Version0


ConfigMgr : OSD : How To Create a Bootable USB Windows 7 Build Disk



The following are required to build the Windows 7 USB Media:

  • Windows 7 Machine
  • USB Removable Drive


Step by Step: How to prepare the USB

The USB device must be prepared prior to generate the stand alone media.

  1. On a Windows 7 PC, Insert the USB device and launch the command line in administrator mode
  2. Enter: “Diskpart” from a command line
  3. In disk part, Enter: “List Disk” – to view the disk number
  4. Select the device with the command “Select Disk 1” and substitute the number 1 with the correct device ID on your system. Make sure you select the correct device as the next step WILL DESTROY ALL DATA on the device selected.
  5. Enter: “clean” in the diskpart command line and hit “enter”
  6. Enter “create partition primary” in the diskpart command line and hit “enter”
  7. Enter “select partition 1” in the diskpart command line and hit “enter”
  8. Enter “format quick fs=ntfs” in the diskpart command line and hit “enter”
  9. Enter “active” in the diskpart command line and hit “enter”
  10. Enter “assign” in the diskpart command line and hit “enter”
  11. Enter “exit” in the diskpart command line and hit “enter”


For easy reference please check the below screen shot


Now copy the content  of the ISO .

ConfigMgr : SQL Query to find Application and version by location


Download :  SQLQuery-Findmachines-withoutlook-locationspecific


SELECT distinct
v_GS_SoftwareFile a
JOIN v_R_System b ON a.ResourceID = b.ResourceID
JOIN v_RA_System_SystemOUName c ON a.ResourceID = c.ResourceID
WHERE FileName = 'outlook.exe' and AD_Site_Name0 like '%perth%'


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


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:

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)


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
Code = 0x80041017
Description = Invalid query
Facility = WMI

NOTE The table and field in SQL are:

Column – OperatingSystemSKU0

In WMI it is:



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”),
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 ) :

ConfigMgr Guide : Holistic Standardization and Fine Tuning


This is one guide that will take your SCCM skills and environment to a whole new level ! This would give you good insight into your infra and also let you understand how the enterprise class Microsoft Suite is behaving under the hood. The guide has also been implemented in several real world production environments and has brought the server systems availability & performance up by 5-10 %.

This guide will also get your environment standardized, organized – get you insights into health on a perspective no tool or query can. Additionally it can be used for expansion / consolidation / migration / upgrades and Configuration Drifts. The logic can also be transmuted to any application which would have heavy configuration and optimization requirements.


SCCM/ConfigMgr Infrastructure covers the entire globe, there are multiple servers catering each location and they have numerous SCCM installations and their configurations have drifted over the time.

The SCCM configurations are currently too varied and we are not receiving the optimum performance from the servers and also facing issues day to day.

4 areas that are currently drifting or problematic ::

Backup Schedules / Discovery / Maintenance Tasks / Collection (Refresh Schedules & Runtime)

  1. Overlapping actions : At present the SCCM backup will run – discovery also runs at same time along with some maintenance tasks.
  2. Too aggressive : Certain actions or schedules are too aggressive and hamper the server performance.
  3. Drift : Configurations have drifted over time and there is no monitoring or what is the benchmark – to notice the deviation from desired configuration.
  4. Unmanaged Items : There are settings that must have been created for a test – but have been forgotten – collection refresh schedules are high on them.
  5. Reporting : The issues with reporting, AppPortal or Integrated App Portals, weekend deployments are all culminating from poor SCCM scavenging process and overlapping aggressive activities.

Document all your findings in the Holistic Standardization and Fine Tuning.


SCCM Backup Schedules – Current


SCCM Discovery – Current


SCCM Maintenance Tasks – Current


SCCM Collection Configuration – Current

Use the Powershell scripts attached to find the expensive and lengthy collection refresh cycles.


From the above captures its obvious there is a configuration drift and non-standard modifications which have been made – maybe even for temporary periods – but get forgotten over time. One classic example is application/OS deployments – where ConfigMgr can be ‘tuned up’ to deliver and deploy faster – but this is not required in day to day operations.

Activities Overlap – Current



  1. Run Performance Counters to capture the current server performance – get a baseline of peak and off hours behavior.
  2. Stagger the activities on the server, have them run spaced out and have least overlaps – this will guarantee high server availability.
  3. Space out the backup schedules – discoveries and maintenance tasks.
  4. Have discoveries run on both Central and Primaries.
  5. Change source of deletion – disable on the primaries and only delete on the central = giving single source for deletion giving better reporting and rid of the duplicate deletion action which was leading to inconsistent data.
  6. Post changes run capture new set of Performance Counters to capture the new server performance – this would the delta of improvement and new baseline.


2-3 months, have four phased changes –

1.Backup [Configuration to be set in 1 go]

2.Discovery [2 Pilot – Rest Production]

3.Maintenance Tasks [Pilot on 2 Primaries and rest in Production]

4.Collections [Pilot on 2 Primaries and rest in Production]


The standardization sheet would be the template of reference for any deviation from desired configuration.



SCCM Backup Schedules – Standardized


SCCM Discovery – Standardized


SCCM Maintenance Tasks – Standardized


SCCM Collection Configuration – Standardized


Activities Spread Out



4 areas that get corrected ::

Backup Schedules / Discovery / Maintenance Tasks / Collection (Refresh Schedules & Runtime)

  1. Spaced out actions : Actions on the servers will be given enough room for its runtime.
  2. Less strain on server : This activity is aiming to lower the process/disk utilization and give a higher server availability and faster response.
  3. Baselines : Configurations have drifted over time and there is no monitoring or what is the benchmark – to notice the deviation from desired configuration
  4. Control : There are settings that must have been created for a test – but have been forgotten – collection refresh schedules are high on them.
  5. Better Reporting : The issues with reporting, App Portal, weekend deployments are all culminating from poor SCCM scavenging process and overlapping aggressive activities.

The configuration would be the baseline for any new server – this additionally would also assist in fine tuning the migrated SCCM 2012 environment


Future migration and baseline maintenance, quarterly re-run of the above checks.


Ease of configuration for the new SCCM environment and upgrades.


SCCM Infra for Future


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


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.

    sys.Name0 ‘Machine’,
    sys.AD_Site_Name0 ‘ADSite’,
    CS.UserName0 ‘User Name’,
    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’
    v_R_System SYS
    MAX(Creation_Date0) AS Creation_Date
    ) AS CleanSystem
    ON SYS.Name0 = CleanSystem.Name0 and sys.Creation_Date0 = CleanSystem.Creation_Date
    ON sys.ResourceID=cs.ResourceID
    ON sys.ResourceID=bios.ResourceID
    MAX(A.[InstallDate0]) AS [InstallDate0]
    ) AS X
    ON sys.ResourceID = X.ResourceID
    ON X.ResourceID=OS.ResourceID and X.InstallDate0 = OS.InstallDate0
    ON sys.ResourceID=ram.ResourceID
    ON sys.ResourceID = D.ResourceID and D.DriveType0 = 3
    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
    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’,”),
    WHEN U.TopConsoleUser0 = ‘-1’ OR U.TopConsoleUser0 IS NULL THEN ‘N/A’
    ELSE U.TopConsoleUser0
    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 ,

    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.


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