Monday, December 9, 2013

SCCM AI General License Reconciliation Report Part 2

Continue from the previous post, I’ll show how to modify the out of the box report (License 15A - General license reconciliation report) to show more hidden information.

Navigate to \Monitoring\Overview\Reporting\Reports\Asset Intelligence, right click on the report License 15A - General license reconciliation report and select Edit.
image

From the Report Builder, expand Datasets. Right click on the Dataset0 and select Dataset Properties
image

From the Dataset Properties Query, we can see that the query is from the AI_NON_MS_LICENSE SQL view. Besides, the original query only query Lic.Name, Lic.Version, Lic.EffectiveQuantity from the AI_NON_MS_LICENSE SQL view.
image

Now launch the Microsoft SQL Management Studio. Navigate to the SCCM database CM_XXX\Views and find dbo.v_AI_NON_MS_LICENSE. Right Click on the dbo.v_AI_NON_MS_LICENSE and click on Select Top 1000 Views.
image

As you can see, there are many others information contain in this view but the original query didn’t query the additional information.
image

I wanted to show Date of Purchase in the report.
Back to the Report Builder, Dataset0 Properties, I added “Lic.DateOfPurchase as [Date of Purchase]” after [Difference],
image

Still on the same Dataset0 Properties, I also added “Lic.DateOfPurchase” after group by Lic.Name, Lic.Version, Lic.EffectiveQuantity
image 

Click on the Fields tab, click Add and select Query Field
image

Enter DateOfPurchase on the left hand side, Date of Purchase on the right hand side.
image

On the Report Builder, right click on top of the table and select Insert Column > Right
 image 

Right click the Expression of the [Difference] and select Copy
image

Right click on the blank table and select Paste
image

Right click on the newly pasted Expression, and select Expression
image

Replace “Difference” with “Date of Purchase”, then click OK
image

Click on the tiny little blue headed icon and select “DateofPurchase”
image

Beautify the header of the table by dragging the boxes.
image

Save it to the folder where you store your custom reports. I don’t recommend you to replace the out of the box report.
image

Run the report you saved. I can view the Date of Purchase information in my report now.
image

The End…….

1 comment:

  1. A very interesting post & the information about the tools is beneficial. The words of wisdom with the example of a tree and tool is really very nice, I enjoyed reading the post. Thank you for the post n keep more coming in.

    ReplyDelete