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.
From the Report Builder, expand Datasets. Right click on the Dataset0 and select Dataset Properties
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.
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.
As you can see, there are many others information contain in this view but the original query didn’t query the additional information.
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],
Still on the same Dataset0 Properties, I also added “Lic.DateOfPurchase” after group by Lic.Name, Lic.Version, Lic.EffectiveQuantity
Click on the Fields tab, click Add and select Query Field
Enter DateOfPurchase on the left hand side, Date of Purchase on the right hand side.
On the Report Builder, right click on top of the table and select Insert Column > Right
Right click the Expression of the [Difference] and select Copy
Right click on the blank table and select Paste
Right click on the newly pasted Expression, and select Expression
Replace “Difference” with “Date of Purchase”, then click OK
Click on the tiny little blue headed icon and select “DateofPurchase”
Beautify the header of the table by dragging the boxes.
Save it to the folder where you store your custom reports. I don’t recommend you to replace the out of the box report.
Run the report you saved. I can view the Date of Purchase information in my report now.
The End…….
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