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

Advertisements

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.

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

agent-smith.png

download : SQLQuery-find-agentsversions

outputofquery.png


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

Windows-10-USB-drive-bootable

Prerequisites

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

dos.png

Now copy the content  of the ISO .

ConfigMgr : SQL Query to find Application and version by location

earth-gps

Download :  SQLQuery-Findmachines-withoutlook-locationspecific

outlook-per-location.png


SELECT distinct
b.Netbios_Name0,
b.User_Name0,
b.ad_site_name0,
a.FileName,
a.FileVersion,
a.FilePath
FROM
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%'
ORDER BY
b.Netbios_Name0


			

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/

ConfigMgr : Discovery Methods in System Center Configuration Manager 1706 CB

In Today’s tutorial, we learn about the discovery methods in System center configuration manager.

We have below discovery methods in System Center Configuration Manager.
– Active Directory Forest Discovery
– Active Directory Group Discovery
– Active Directory System Discovery
– Active Directory User Discovery
– Heartbeat Discovery
– Network Discovery

Active Directory Forest Discovery

By enabling Active Directory Forest Discovery, we can find resources from AD forests. When you configure the forests to discover AD sites and subnets, configuration manager can automatically create boundaries from this information.

Active Directory Group Discovery
By enabling Active Directory Group Discovery, we can discover the resources using AD group membership of computers and users.

Active Directory System Discovery
By enabling Active Directory System Discovery, we can find the computers in Active Directory Domain Services.

Active Directory User Discovery
By enabling Active Directory User Discovery, we can find the user accounts in AD domain Services.

Heartbeat Discovery
By configuring these settings, we can configure interval for configuration manager clients to periodically send a discovery data record to the site.

Network Discovery
By configuring these settings, we can configure settings and polling intervals to discover resources on the network like subnets, SNMP-enabled.

We can enable all these discovery methods by following below steps.
Login to Configuration manager console, Administration, expand hierarchy configuration and click on discovery methods, in results pane you will find all the discovery methods as below. To enable go to properties of each discovery methods.DiscoveryMethods
Thank you for reading ☺
Keep Learning ☺

ConfigMgr Guide : Holistic Standardization and Fine Tuning

marketing-analytics

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.

PROBLEM STATEMENT

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.

BEFORE


SCCM Backup Schedules – Current

finetuning1


SCCM Discovery – Current

finetuning2.png


SCCM Maintenance Tasks – Current

finetuning3.png


SCCM Collection Configuration – Current

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

finetuning4

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

finetuning5.png


RECOMMENDATION

  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.

TIMELINE

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]

OUTCOME

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

finetuning6.png

AFTER

SCCM Backup Schedules – Standardized

finetuning7.png


SCCM Discovery – Standardized

finetuning8.png


SCCM Maintenance Tasks – Standardized

finetuning9.png


SCCM Collection Configuration – Standardized

finetunin10.png


Activities Spread Out

finetunin11.png


POST REMEDIATION

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

TIMELINE

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

OUTCOME

Ease of configuration for the new SCCM environment and upgrades.

BEYOND

SCCM Infra for Future

finetunin12.png


SCCM Troubleshooting : Ironing out Patching for ConfigMgr 2007 (upto 95% compliance)

This guide will take you through troubleshooting guides, tips, tricks and tools to achieve 95%+ compliance in your environment and maximum coverage. The guide assumes the following environmental conditions:

 

  1. Microsoft System Center Configuration Manager 2007 R3
  2. WSUS 3.0 SP2 used as base for Software Updates
  3. Windows Client endpoints
  4. Three tier SCCM hierarchy

 

– Table of Contents –

  1. Server Side 
    • SCCM Side Clean-up 
    • Discovery Configuration 
    • Distribution Point / DP Group 
    • Site Info v/s WSUS Infra 
    • WSUS Sync up to date 
    • Top Down Checks 
    • Autonomous WSUS 
    • Windows Update Agent Version 
    • Windows Update Agent Repair 
  2. Client Side 
  3. Monitoring Tools

 

  •  Server Side
    •  SCCM Side Clean-up
      • Obsolete

Obsolete clients are those that have been replaced by new ones. This usually happens during refresh OS deployments where the hardware stays the same and thus the hardware id is the same but the SMS GUID changes because the OS has been reloaded or the GUID is regenerated for another reason but the hardware remains the same.

Reasons – 
1. Hard disk swapping
2. Renaming machines
3. Reimage OS
4. Reinstalling SMS/SCCM agent on the machines without proper uninstall.

If machines is showing up as Obsolete – that is good to delete, manually or via scheduled task within SCCM

 

  • No Client
    • Identify machines where they show up as no client,
    • If Client = NO and Site Code = blank – this indicate machine is not belonging to a particular boundary and it needs to be defined.
  • Inactive
    • Inactive client s are those that have not been discovered recently by the heartbeat discovery. The definition of recently is defined in the delete task as a number of days. Please note that obsolete client s are also marked inactive.Reasons
      Offline machines
      2. Machines having DNS issue/No name resolution
      3. Machines are in inventory stock

 

Refer the collection created via #WQL1 , machines are good to clean-up if Inactive=YES,

  • Duplicate – these are variants of obsolete, or outcomes of causes as explained in obsolete.

 To find duplicate entries – create a collection with the following WQL query >

Reference : #WQL1

select R.ResourceID,R.ResourceType,R.Name,R.SMSUniqueIdentifier,
R.ResourceDomainORWorkgroup,R.Client from SMS_R_System as r full join 
SMS_R_System as s1 on s1.ResourceId = r.ResourceId   full join SMS_R_System 
as s2 on s2.Name = s1.Name   where s1.Name = s2.Name 
and s1.ResourceId != s2.ResourceId

Based on the results – machines which are good to be deleted if Obsolete = YES.

  •  Unapproved – These as long as site code and domain are correct – good to be approved
  • Unknown domain
    • Machines reporting in from unknown domain could mean
      • Machine disjoin from current domain and rejoined another
      • Machine from another domain received incorrect SCCM client installation/push
      • Machines from other domain are using incorrect installation switches
  •  Unknown hostname naming format
    • Most multi-national organizations follow standards to maintain machine naming based on region and country
    • APDDSERIALNUMBER = AP – APAC, DD – Desktop
    • NALLSERIALNUMBER = NA – North America, LL – Laptop
    • Any deviation from these naming standards means the machine should not be in SCCM.

 

  • Site to Site Communication

Verify SCCM site communication is healthy.

  • Create test collections/packages on Central Site and then view on the Primary Sites
  • Verify machines reporting in Primary are available on Central – dump ‘All Systems’ from both and do vlookup to find out GAP.
  • Run preinst syncchild SITECODE against any primary not in sync.

 

  •  Discovery Configuration
    • Confirm that the correct OU’s are being queried within Active Directory, any new additions or new OU’s need to be sync’ed with AD team.
    • Heartbeat discovery is enabled and configured for weekly [mild] – daily [aggressive/urgency]

 

  •  Distribution Point / DP Group
    • A good sanity check is to target a test package to all DP groups and in the review window analyse which distribution points did not get selected.
    • Verify the Deployment Packages are targeted to all active distribution points – this helps attain maximum coverage, so machines from every corner of the infrastructure get the necessary patches.
  •  Site Info v/s WSUS Infra

 

For keeping a tidy sync between WSUS and SCCM – depending on your SCCM SUP’s spread – you can compare WSUS downstream servers v/s the built-in report 75 of SCCM.

http://servername/SMSReporting_sitecode/Report.asp?ReportID=75

  •  WSUS Sync up to date

Check last sync between downstream and upstream partners – if there is a gap that needs to be rectified.

  •  Top Down Checks

Start the checks on the WSUS console from top to down – start from Central and then check on the primaries – make sure all servers are reporting in and have latest sync.

  •  Autonomous WSUS

Verify none of the WSUS downstream partners are showing up as Autonomous.

If they are fix with this method

Check site to site replication
Sitectrl - parent settings
Sitectrl file exchange
Pointing to self for updates
Upstream server checkbox
ConfigMgr side configuration flip and flipback

 

  •  Windows Update Agent Version

Windows Update Agent version differences in the environment can cause issues.

Machines with older versions of WUA will not be able to sync latest updates or different products.

Use following SQL Statement to make a custom report >

SELECT v_R_System.Netbios_Name0, v_GS_WINDOWSUPDATEAGENTVERSIO.Version0
FROM v_GS_WINDOWSUPDATEAGENTVERSIO
INNER JOIN v_R_System ON v_GS_WINDOWSUPDATEAGENTVERSIO.ResourceID = 
v_R_System.ResourceID
WHERE v_GS_WINDOWSUPDATEAGENTVERSIO.Version0 <> 'ISNULL'
ORDER BY v_GS_WINDOWSUPDATEAGENTVERSIO.Version0

The result would indicate disparities between the WUA versions in the environment and these can be targeted.

Push the latest WUA to these machines.

 

  1. Make sure kb2734608 is installed on all WSUS servers
  2. Push latest WUA – https://support.microsoft.com/kb/949104?wa=wsignin1.0
  •  Windows Update Agent Repair

Use the WUA from https://support.microsoft.com/kb/949104?wa=wsignin1.0 to repair broken Windows Update Agents on machines – with unique scan errors etc and also helps machines which are unable to update the WUA.

  • WOL – Wake on Lan

Utilize SCCM’s most underrated/underutilized feature – Wake On Lan. This benefits in patching – so that machines can be woken up and then patched and they get auto rebooted as per requirement. This benefits in reaching out to machines in off hours – times when users are not present – great to combine with ‘Maintenance Windows’. This way you will get to set a time for when these machines will receive the patches (offhours) + ability to wake them up for the activity. Reduces load on the network and off hours maintenance is always appreciated.

  • State Message Drop

Evaluations from machines sometimes are not reported in correctly, machines at times have the patches installed but same does not reflect in the compliance reports. This is caused due to drop in state messages from client to server, or from server to server and at times shows up as Enforcement State Unknown.

There is a method to trigger the full state message to be sent from client to server >

 

Option Explicit
On Error Resume Next
Call RefreshServerComplianceState
' WScript.Echo "Finished"
Sub RefreshServerComplianceState()
dim newCCMUpdatesStore
' Create the COM object.
set newCCMUpdatesStore = CreateObject ("Microsoft.CCM.UpdatesStore")
' Refresh the srvr compliance state by running the RefreshServerComplianceState method.
newCCMUpdatesStore.RefreshServerComplianceState
' Output success message.
'wscript.echo "Ran RefreshServerComplianceState."
End Sub

This script can be found on the web in various places and simply makes use of the SCCM SDK to cause the client to resend it’s data to the MP.  It’s a convenient way to force some state messages up so we don’t have to wait on components to insert them.

Typically, a vb script is executed simply by using cscript as show below.  But note that this fails.  I show this to illustrate the problem you might see if trying to run the script yourself.  SCCM is a 32 bit application but, in this case, running on a 64 bit server.  The default version of cscript is the 64 bit version – and generally this works fine with any vbscript.  In this case, however, the call being made requires the 32 bit version of cscript which you must run out of the syswow64 folder as shown in the second example.

cmd1.png

At this point we have to wait for the next state message polling cycle which will cause all state messages to be sent back up.

Our polling cycle has now fired and if we look at the statemessage.log we see that the state information has been pulled, formatted (into XML) and sent to the MP.

 

<![LOG[StateMessage body: <?xml version="1.0" encoding="UTF-16"?> 
 <Report><ReportHeader><Identification><Machine><ClientInstalled>1</ClientInstalled><ClientType>1</ClientType><ClientID>GUID:F1B03089-9EA1-4A64-83DC-5F8E77BDDEB4</ClientID><ClientVersion>4.00.6487.2000</ClientVersion><NetBIOSName>STEVERAC64-2</NetBIOSName><CodePage>437</CodePage><SystemDefaultLCID>1033</SystemDefaultLCID></Machine></Identification><ReportDetails><ReportContent>State Message Data</ReportContent><ReportType>Full</ReportType><Date>20110107184030.662000+000</Date><Version>1.0</Version><Format>1.0</Format></ReportDetails></ReportHeader><ReportBody><StateMessage MessageTime="20110107183046.153000+000" SerialNumber="1431"><Topic ID="21e49ac6-a273-4a61-9794-eb675bc743e5" Type="500" IDType="3"/><State ID="2" Criticality="0"/><UserParameters Flags="0" Count="1"><Param>102</Param></UserParameters></StateMessageserParameters></StateMessage></ReportBody></Report> 
 ]LOG<![LOG[CStateMsgManager::GetSignEncyptMode]LOG]!><time="12:40:31.037+360" date="01-07-2011" component="StateMessage" context="" type="1" thread="3592" file="statemsg.cpp:1820"> 
 <![LOG[Successfully forwarded State Messages to the MP]LOG]!><time="12:40:31.099+360" date="01-07-2011" component="StateMessage" context="" type="1" thread="3592" file="statemsg.cpp:1527">

 

 

I’ve truncated the XML due to size – leaving only a single state message – we will come back to the XML shortly as it really isn’t very friendly in this format.

Note that although the statemessage.log records that the messages have been dispatched to the MP, the statemessage system doesn’t actually do the movement of data to the MP – CCMExec does that as we see from the small snip below.  There is actually a bit more that goes on behind the scenes at this point but it’s sufficient to know that CCMExec sends data to the MP – in this case the MP_Relay component.

<![LOG[OutgoingMessage(Queue='mp_mp_relayendpoint', 
ID={A9E7A07D-223D-4F5D-93D5-15AF5B72E05C}): Delivered successfully to host 
'STEVERACPRI.STARTREKNG.COM'.]LOG]!>

Switching to the MP now, when data arrives for processing at MP_Relay (again, a bit more behind the scenes stuff happens but not worth diving into here since most folks will never come across a need to dig in) we see the data processed and translated to .SMX file format and placed in auth\statesys.box\incoming.

Inv-Relay Task: Processing message body    
 Relay: FileType= SMX    
 Relay: Outbox dir: C:\Program Files (x86)\Microsoft Configuration Manager\inboxes\auth\statesys.box\incoming    
 Relay: Received 0 attachments    
 Relay: 0 of 0 attachments succesfully processed    
 Inv-Relay: Task completed successfully   

If we take a look at the auth\statesys.box\incoming directory – and if we are fast enough – we will see the .SMX files there and being processed.  Typically you won’t see much here – if you do it’s worth investigating what messages are here and not being processed.  If we find a .SMX and crack it open with notepad we can see the detail but the formatting leaves much to be desired.

smx.png
A little trick here.  If you rename the .SMX and simply add a .XML extension – so <filename>.SMX.XML and double click it you get a much nicer formatting of the data through IE.  I’ve highlighted the info in the file that is key – we can easily see the machine details and also state message details we have become familiar with – and also the unique state message ID.

Note:  If you are going to rename these files then you should copy them out first so you aren’t impacting the statesys.box folder itself.

xml.png

From here all that remains is to process these state messages into the database.  You can see these messages processed in the statesys.log – something similar to the following.

 

Found new state messages to process, starting processing thread    
 Thread "State Message Processing Thread #0" id:5076 started    
 CMessageProcessor - Detected parent site 'CEN'    
 CMessageProcessor - Processing file: mdlbp169.SMW    
 CMessageProcessor - Processed 1 records with 0 invalid records.    
 CMessageProcessor - Successfully replicated file "mdlbp169.SMW" to parent site CEN.    
 CMessageProcessor - Processed 1 message files in this batch, with 0 bad files.    
 Thread "State Message Processing Thread #0" id:5076 terminated normally

 

The DB processing component often can be seen by enabling SQL tracing.  That doesn’t help much here so we turn to SQL profiler – which does give us a hint at what is happening but still doesn’t’ completely pull back the covers.  Suffice it to say that there are a number of SQL stored procedures responsible for processing state messages – and also a number of tables in the database that store state message data.  The stored procedures that do the state message processing generally start with the name spProcess – there are a number of them.  Those interested should be able to dig in further from here to see the tables involved, etc.

OK, with processing into the DB complete we have finished the state message process flow – well, almost.  The last thing to discuss is state message resyncs.

I have already alluded to the fact that the site server keeps track of state messages as they come in – so that if any state messages are missing they can be flagged and a resync requested from time to time.  State messages and their data are important so we definitely don’t want to miss any.

As state messages come in the unique ID is read and stored in the database.  As processing continues this data is constantly updated and if a gap is detected we flag it and store any missing state messages in the SR_MissingMessageRanges table.  One would hope that this table will be empty but in production we may see data in the table here and there.  This table is used to help keep track of state messages needing resync.

Any needed resyncs are evaluated hourly – but that does not mean that systems will be running a resync hourly.  The site control file specifies a few settings of interest (with the site control file always apply the caution – look, don’t touch!   )

 

BEGIN_COMPONENT 
   <SMS_STATE_SYSTEM> 
   <6> 
   <STEVERACPRI> 
    PROPERTY <Loader Threads><><><4> 
    PROPERTY <Inbox Polling Interval><><><900> 
    PROPERTY <Loader Chunk Size><><><256> 
    PROPERTY <Max Chunks Fetched><><><100> 
    PROPERTY <Resync Check Interval><><><60> 
    PROPERTY <Min Missing Message Age><><><2880> 
    PROPERTY <Heartbeat Msg Interval><><><15> 
 END_COMPONENT
PROPERTY <Resync Merge Interval In Hours><><><72>

 

Resync Check Interval is set to 60 minutes – this is the schedule at which we check for any systems needing state message resyncs.

Min Missing Message Age is set to 2880 – this is the amount of time a message has to be missing before a resync is requested.

Resync Merge Interval in Hours is set to 72 – this is the amount of time between resyncs of a client.  This interval helps ensure that clients won’t resync over and over again.  You should not see a client resync more frequently that the interval set here.

 

  • WSUS automated cleanup via powershell

 https://gallery.technet.microsoft.com/scriptcenter/WSUS-Clean-Powershell-102f8fc6

 

This little script will perform a clean up of declined and superceded updates in the WSUS database.  It uses the .Net class “Microsoft.UpdateServices.Administration” assembly, which should be loaded on your WSUS server.

Note that this script will stop the WSUS service twice, but these services will be restarted correctly by the script, and no user action is needed.

# Performs a cleanup of WSUS.
# Outputs the results to a text file.
$outFilePath = '.\wsusClean.txt'
[reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration") | out-null
$wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer();
$cleanupScope = new-object Microsoft.UpdateServices.Administration.CleanupScope;
$cleanupScope.DeclineSupersededUpdates = $true      
$cleanupScope.DeclineExpiredUpdates         = $true
$cleanupScope.CleanupObsoleteUpdates     = $true
$cleanupScope.CompressUpdates                  = $true
#$cleanupScope.CleanupObsoleteComputers = $true
$cleanupScope.CleanupUnneededContentFiles = $true
$cleanupManager = $wsus.GetCleanupManager();
$cleanupManager.PerformCleanup($cleanupScope) | Out-File -FilePath $outFilePath

 

  • Enforcement State Unknown
    • Analyse the repeat offenders from past 4 to 5 months.

Report Name | States 1 – Enforcement states for a deployment

Assuming that each month has a unique deployment, within the report – it will

 

  •  Client Side
    • Tools

Client Actions Tool | https://sccmcat.codeplex.com/

Description
SCCM Client Actions Tool or SCCM CAT is a practical and simple HTA application for performing most common day-to-day administrative tasks on System Center Configuration Manager 2007 clients. The tool allows running actions remotely on one or more computers. A list of computers can be provided either from a file (XLS, XLSX, CSV, TXT), loaded from SCCM collection or simply entered as a text into a text area. The main goal for creating this tool was to have something simple that doesn’t have any prerequisites and doesn’t require installation. Just pick it up and run. It’s an alternative to SCCM console and right-click tools because it’s not always possible to install SCCM console everywhere and manage clients in closed environments. The features are as follows:

  • Initiate most common SCCM client schedule actions.
  • Initiate various actions to manipulate SCCM client agent. Install/uninstall agent, change GUID, assign site code, change cache size, etc.
  • Initiate SCCM client health checks and fixes. Allows running checks with and without fixes as well as full health check.
  • Initiate various administrative actions on workstations. Copy a file to remote computers, refresh policies, reset security settings, wake on LAN, etc.
  • Query for different values from remote computers. Query for wide range of information such as current management point, available advertisements, logged-on user, WSUS server, WUA version, patch status, system uptime, reboot pending state, etc.
  • Switch between integrated authentication and alternate credentials. Supports using multiple credentials. When logged on username has not enough rights it’s possible to specify alternate credentials by clicking on a link in top-right corner. Windows XP requires that cmdkey.exe is available in HTA folder.
  • Check for newer versions of the tool on startup.
  • Automatically install SCCM client during health check in case version is too old or agent does not work. Optional feature that can be enabled in configuration file.
  • Save list of offline computers for later use. Optional feature that can be enabled in configuration file.
  • Easily configure client installation properties. Ccmsetup.exe command line can be created dynamically by using GUI controls.
  • Use TXT, CSV, XLS or XLSX files as the data source. Files with TXT extension must have computer names on each line. Excel worksheets are read from column A starting from second row and it’s possible to write results back to worksheet. Using exported CSV from SCCM console is also supported.
  • Populate computer list from SCCM collection. Allows loading all collection members into a list.
  • Manually enter computer names into a textbox. Allows manually entering one or more computers in a text box for quick actions.
  • Supports both 32-bit and 64-bit OS on clients.
  • Supports Windows XP SP2 and newer operating systems on clients.
  • Displays real-time progress. Works when running HTA on Windows 7 or Windows Server 2008. Useful when there are thousands of computers and it would be nice to know how much is done. HTA window may not update as smoothly in Windows XP and Windows Server 2003, but it works.
  • Log is created in a text area and in a file. Lastlog.log is written to HTA folder. By default the log is using Trace32 log formatting. Log can be opened directly in application. It’s also possible to keep log history.
  • Uses configuration file to store default settings.

 

 

 

 

IT Challenges

IT administrators and IT support staff need easier access to key information about software and operating system deployments, client health, and compliance with regulations.  They must ensure that their systems and software meet the configuration requirements established for the organization.  And they need the ability to track this information without having access to a System Center Configuration Manager console.

Solution

The Microsoft System Center Configuration Manager 2007 Dashboard lets customers track application and operating system deployments, security updates, the health status of computers, and IT compliance with key regulations—with an easy to use, customizable Web interface.  Because the Dashboard is built on Windows® SharePoint® Services, IT staff can access information without using the Configuration Manager console. The Dashboard is a free Solution Accelerator, and fully supported by Microsoft.

Key Benefits

Benefits of the dashboard include:

  • Actionable information out of the box. The dashboard comes with valuable, built-in datasets that IT managers can access without using the Configuration Manager console.
  • Centralized, near-real-time access to key information. The graphical dashboard lets customers view any Configuration Manager data set in near-real time—without leaving their desk.
  • Easy to build and configure.The dashboard’s wizard-based tools let customers easily create new dashboards in minutes.
  • Easy to customize. The dashboard can easily be customized to meet the needs of different departments and other groups. Any data set in the Configuration Manager database can be presented on the dashboard, in chart, gauge, and table formats.
  • Flexible & interactive. Users can easily filter data and create ad hoc, custom views. Filters allow users to quickly drill down from high-level to more specific data.

 

Download the re-eval script and wsus cleanup powershell script

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.