SystemCenter

All about System Center Articles and Tutorials

Check if Specific Hotfix installed on a Collection – SCCM

Posted by on Aug 11, 2017 in SCCM, SQL

Check if Specific Hotfix installed on a Collection – SCCM

Recently we got a request from our Platform Team whether we can fetch any report from System Center Configuration Manager to check if specific Hotfixes installed on all the servers managed by them.

After further discussion, we got to know that they wanted to check all the systems that miss the WannaCry related hotfixes.

Let’s create the report using Microsoft SQL Server Report Builder. Before that, we need to leverage the Win32_QuickFixEngineering WMI class otherwise we will not get the data in our report.  This WMI class is specifically designed to reflect the presence of all system-wide updates deployed.  We also have an added advantage that this class specifically excludes any update supplied by Windows Update –  so the result set we get from this WMI class comes pre-filtered with just the supplemental data we want.  You can read more about the Win32_QuickFixEngineering class here.

Let’s start…

Enable Win32_QuickFixEngineering WMI class

1: Launch SCCM console and click on Administration tab.

2: Click on Client Settings, right click Default Client Settings and click Properties.

3: Click on Hardware Inventory and click on Set Classes button under Device Settings option.

4: Put check mark on Quick Fix Engineering (Win32_QuickFixEngineering) and click OK.

5: Click OK button to save the settings.

We have enabled the Win32_QuickFixEngineering class, let’s create our report.

Creating Report using Report Builder

Below is the query we are going to use to check if specific HotFix installed on a collection of servers:

select
vrs.Resourceid,
vrs.Name0,vrs.User_Name0,
qfe.HotFixID00,qfe.InstallDate00,
qfe.InstalledOn00,qfe.Caption00,
qfe.InstalledBy00
from QUICK_FIX_ENGINEERING_DATA qfe
join v_R_System vrs on vrs.ResourceID =qfe.MachineID
join v_FullCollectionMembership fcm on fcm.ResourceID=vrs.ResourceID
and fcm.CollectionID=’Provide Collection ID
where HotFixID00 = ‘Provide HotFix ID

After running the SQL query in SSMS, we are getting the desired results.

I have created a Test collection for this demo, and using same in our query. Anyway, I will be giving an option in our report to choose the Collection from available list.

Let’s create the report.

1: Open SQL Server Report Builder, right click Data Sources folder and click Add Data Source.

2: Select Use a connection embedded in my report option and click Build button.

3: Under Server name field, provide SQL Server / Cluster Name holding SCCM Database.

4: Select SCCM database name and click OK.

5: Click on Test Connection button and make sure connection is created successfully.

6: Click OK to close Data Source Properties window.

7: Right click Datasets and click Add Dataset option.

8: Select option Use a dataset embedded in my report.

9: Select the Data source which we created.

10: Select Text as Query type and past below SQL query here.

select
vrs.Resourceid,
vrs.Name0,vrs.User_Name0,
qfe.HotFixID00,qfe.InstallDate00,
qfe.InstalledOn00,qfe.Caption00,
qfe.InstalledBy00
from QUICK_FIX_ENGINEERING_DATA qfe
join v_R_System vrs on vrs.ResourceID =qfe.MachineID
join v_FullCollectionMembership fcm on fcm.ResourceID=vrs.ResourceID
and fcm.CollectionID= @CollID
where HotFixID00 = @HotFixID

The SQL query will fetch data from SCCM Database and will show it in our report.

11: Click OK to close the Dataset Properties.

Now, since we want to select the Collection from list, we need to create one more Dataset for it.

12: Right click Datasets and click Add Dataset option.

13: Select option Use a dataset embedded in my report.

14: Select the Data source which we created.

15: Select Text as Query type and past below SQL query here.

select Distinct CollectionID,Name from v_collection order by Name

16: Click OK to close the Dataset Properties.

Now we need the fields which should be displayed in our report.

17: Click on Insert tab, click Table and select Table Wizard option.

18: Select DataSet1 and click Next.

19: Select the fields which you want to show in your report and drag it to Values box.

After dragging the required fields under Values box, you may get few fields starting from Sum.

We don’t need these fields, so remove all such fields by clicking dropdown option against those fields and unchecking the Sum option.

20: Layout preview will appear, click Next to continue.

21: Click Finish button.

The Report Builder Interface will show you all the created Data Sources, Datasets, Parameters used and the Fields which we want to show in our report.

22: Modify the field names and expand the fields as per the requirement.

23: Expand Parameters folder and double click @CollID parameter.

24: Provide a suitable caption under Prompt field.

25: Click on Available Values tab and select Get values from a query option.

26: Select DataSet2 under Dataset field.

27: Select CollectionID under Value field and Name under Label field.

28: Click OK to continue.

29: Perform Step 24 for HotFixID parameter and click OK button.

30: Click on Save button and select the folder under which report will be stored.

31: Provide suitable name to the report and click Save button.

Report is created, now let’s run it through Report Builder itself.

32: Click on Run button.

33: Select the Collection ID from dropdown list and provide Hotfix ID.

24: Click on View Report Button.

That’s it, the report will show you the list of servers on which this specific Hotfix is installed.

You can check the report through SCCM console as well.

Hope it helps.

Submit a Comment

Your email address will not be published. Required fields are marked *