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: