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).

smsprov.log1

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

.

smsprov.log2

Create a new Sccm SQL-Based Report, define the name and path and the Report Builder will automatically open.

smsprov.log3

Select to Create a new Table or Matrix, and for the Query Design window, click simply on Edit as Text and copy your query.

smsprov.log4

Select all fields and add them to the Values group.

smsprov.log5

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

smsprov.log6

We then define a new parameter

smsprov.log8

which values are based on the Second Dataset

smsprov.log7

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

=Parameters!ReportParameter1.Label

Finally I made the url clickable via the following change

smsprov.log9

Now we have a report that we can export, print and provide as needed.

 

Advertisements