Tag : sccm reports

How to Create a SCCM Application Catalog Usage Report?

It is an interesting experience to write SQL query about SCCM Application Catalog Usage Report, as we need to use a function query: (fn_ListLatestApplicationCIs(1033)

from this function, we can get the data of Manufacturer, DisplayName, SoftwareVersion, CI_UniqueID, ModelID, SDMPackageVersion, DateCreated, LastModifiedBy from our SCCM.

 

Anyway, here is my SQL Query:

select app.RequestID as ‘ReqID’,
(select top 1 ResourceID from v_r_user where Unique_User_Name0 = umr.UniqueUserName) as ‘UserResourceID’,
(select DisplayName from fn_ListApplicationCIs(1033) where ModelName = app.AppID AND IsLatest = 1) as ‘Requested Software’,
umr.UniqueUserName as “User”,
(select Name0 from v_R_System where umr.MachineResourceID = v_R_System.ResourceID) as ‘Machine Name’,
app.CreationTime as ‘RequestDate’
from UserAppModelSoftwareRequest app
join UserMachineRelation umr on app.RelationshipResourceID = umr.RelationshipResourceID

 

Here are some references:

SCCM Application Statistics :

http://www.nickalmiron.com/?p=10#comment-111

“Functions” in SCCM SQL Reporting (fn_ListLatestApplicationCIs(1033) ) :

https://social.technet.microsoft.com/Forums/en-US/70a95f2b-21d7-4509-9b54-1e9040e99ee9/reporting-on-applications-in-configmgr-2012?forum=configmanagerapps

 


How to Create a Custom SCCM Report (Not Query)

I am sure you already tried building for some queries in SCCM, it is very easy and convenient, however if you want something looking nice and can export easily or subscribe, SCCM Reporting is the best way to do it.

It sounds very complicated, but if you try one step at a time. It is not as hard as you thought.

1.Open Microsoft SQL Server Management Studio

1.1 If you want to use the easy graphic way of building a SQL statement:

Right click “Views” on your SCCM Database, and you should able to see a window to “Add Table”

Here in the example, I chose “UserAppModelSoftwareRequest”, “UserMachineRelation”, and “v_R_System”

Now we need to tell SQL Server Management Studio what are the relationships of these three tables:

You can just drag the linked key from one table to another. For example, between “UserAppModelSoftwareRequest” and “UserMachineRelatoin” these two tables, they have “RelationshipResourceID” in common, so I dragged from one to the other. and you can see the “link” SQL Studio created for you.

After that, click the Columns you want to show. If you want an alias for the column name, just type in the “Alias” column.

and once you finished them all, click “Execute SQL” , or just Ctrl+R.

You will see the result from the bottom area.  Take a look the raw SQL statement area, those are the codes we need later.

 

1.2 If you want to use the old fashion way to write SQL statement on your own:

1.3 Once you finish your SQL statements and confirm it is working well. We copy the code we need.

 

2. Create a Report in SCCM

Now we need to give a name and tell SCCM which folder to save this report:

Once we finish that, it will automatically lunching Microsoft SQL Server Report Builder:

We need to create a table:

Use ” Table Wizard”:

“Create a dataset”

Test connection to see if you have enough rights to connect to your SCCM Database

Here I just use my current logged on user:

Once you finish testing the connection, now we “Edit as Text”:

Now paste the SQL statements we created before, and do a test run by clicking the red “!”:

If everything looks right, you save the report in SQL Server Report Builder, and now go back to check the result 🙂

 

References:

How to create a SCCM report: