SystemCenter

All about System Center Articles and Tutorials

Build SCCM Report to check Installed Hotfixes on a System

Posted by on Jul 11, 2017 in SCCM, SQL

Build SCCM Report to check Installed Hotfixes on a System

Recently after couple of Ransomware attacks, we have got a requirement from our Server Operations team whether they can get a report wherein they can get a list of installed Hotfixes on a system.

There are sites which provides the PowerShell scripts to check this information, however they wanted us to author a report in SCCM through which they can get list of installed Hotfixes on a system.

So, let’s start…

Below is the SQL query which can fetch list of installed Hotfixes on a system managed by SCCM:

select
vrs.ResourceID,
vrs.Name0,
qfe.HotFixID00,
qfe.InstalledOn00,
qfe.Caption00,
qfe.InstalledBy00
from QUICK_FIX_ENGINEERING_DATA qfe
join v_R_System vrs on vrs.ResourceID = qfe.MachineID
where vrs.Name0 = ‘PROVIDE SYSTEM NAME HERE

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

Let’s create the report through SCCM console.

1: Open SCCM console and click Monitoring tab.

2: Expand Reporting folder, click Reports and click on Create Report option.

3: Provide a suitable name and description to the report.

4: Click on Browse button and select the folder under which report will be stored.

5: Click Next to continue.

6: Review the Summary and click Next.

7: Click on Close button to exit the Wizard and open Report Builder.

8: Right click Data Sources folder and click Add Data Source option.

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

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

11: Select SCCM database name and click OK.

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

13: Click OK to close Data Source Properties window.

14: Right click Datasets and click Add Dataset option.

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

16: Select the Data source which we created.

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

select
vrs.ResourceID,
vrs.Name0,
qfe.HotFixID00,
qfe.InstalledOn00,
qfe.Caption00,
qfe.InstalledBy00
from QUICK_FIX_ENGINEERING_DATA qfe
join v_R_System vrs on vrs.ResourceID = qfe.MachineID
where vrs.Name0 = @srvname

The SQL query is the core of this report, it will fetch the data from SCCM Database and present the same in our report.

18: Click OK to close the Dataset Properties.

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

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

19: Select the Dataset which we created and click Next.

20: 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.

21: Click Next to continue.

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

23: 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.

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

25: Expand Parameters folder and double click @srvname parameter.

26: Provide a suitable caption under Prompt field and click OK.

Save the report. You can test the report here by clicking Run button however we will test it through SCCM console.

28: Open SCCM console, click Monitoring tab and expand Reporting folder.

29: Expand Reports and open the folder under which we did save the report.

30: Double click the report which we created.

31: Provide system name under Provide System Name field and click View Report option.

It will list all the Hotfixes installed on the system. The list can be exported in different formats like .CSV or Excel.

That’s it.

Hope this helps.

Submit a Comment

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