While Sccm provides a lot of standard reports, sometimes they do not give you the information you want.
Let’s take a simple example.
If I go to Software Library / Software Updates / Software Update Groups, I can select one of the groups and show its members to display the list of updates that are part of the group. Now let’s try to provide this list to another person… it should be simple, right ?
But wait, I can’t export this view, cannot print it… and there does not seem to be a report for this in the default report list.. ?
Let’s build one by ourselves… !
Step 1: Find what tables / views are being used by the console
If you are proficient in SQL, you could use SQL Server Management Studio to query the tables and views until you find the information you are looking for.
An -easier – alternative is to look at the code the Sccm console is using to provide you with the results you are looking for. This can be done by looking at the sccm logs in Smsprov.log. For each operation in the console, you will get some SQL queries being run in the background, and those will appear in this log. (note that this log is to be found on the server itself, you will not find it on a computer where the console is installed).
Now open Sql Server Management Studio and validate that the information returned is what you are looking for.
For my part I choosed to reduce the number of colums returned so as i can use them to create a report.
My Sql query looks like this :
select all upd.ArticleID,upd.BulletinID,upd.DatePosted,upd.DateRevised,upd.IsExpired,upd.IsSuperseded,upd.Description,upd.DisplayName,upd.CIInformativeURL from vSMS_CIRelation as cr,fn_ListUpdateCIs(1033) as upd where ((cr.FromCIID = 16832645 AND cr.RelationType = 1) AND upd.CI_ID = cr.ToCIID)
order by upd.DatePosted
Step 2: Let’s Create a Report based on our Query
Create a new Sccm SQL-Based Report, define the name and path and the Report Builder will automatically open.
Select to Create a new Table or Matrix, and for the Query Design window, click simply on Edit as Text and copy your query.
Select all fields and add them to the Values group.
There you are, resize the colums to get a report that looks a bit nicer.
Step 3: Make the report more user friendly
While report provides us with the updates present in a software update group, this software update group is fixed by our query. Let’s make it work for all Software Update groups…
In order to do this we need to get a list of Software update groups and their corresponding ID.
Here is a query that does just this:
select SMS_AuthorizationList.CI_ID,SMS_AuthorizationList.DisplayName from fn_ListAuthListCIs(1033) AS SMS_AuthorizationList
In order to be able to use this query we need to create a second dataset based on this query
We then define a new parameter
which values are based on the Second Dataset
We can now change the query of our first dataset to use the parameter:
select all upd.ArticleID,upd.BulletinID,upd.DatePosted,upd.DateRevised,upd.IsExpired,upd.IsSuperseded,upd.Description,upd.DisplayName,upd.CIInformativeURL from vSMS_CIRelation as cr,fn_ListUpdateCIs(1033) as upd where ((cr.FromCIID = @ReportParameter1 AND cr.RelationType = 1) AND upd.CI_ID = cr.ToCIID) Order By upd.ArticleID
We replace our fixed CI_ID with the parameter.
Now when we launch the report, we can choose which Software Update Report we want to target.
You can automatically update the report title by using a TextBox using the expression
Finally I made the url clickable via the following change
Now we have a report that we can export, print and provide as needed.