Zum Inhalt

SCCM – SQL Query for Computer Information (statistics)

I am developing an administrative webgui to give our admins access to different services to manage their users and computers in our new Active Directory environment. We came across that this is the best solution to automate so many things like creating home folder, creating profile folder, creating terminalserver profile folder, set ACL to folders, create and publish printers to the AD, set quota for different kind of folders, distribute software via SCCM by AD groups and so on. It is necessary that every admin can only see objects that belong to his department. After developing all these base functionality we came to the conclusion that the information that is stored in the SCCM database is really informative for the admins, so we created some selects for the database to get the information about computerobjects, softwarestatus, hardwareinformation, etc…

Additional we thought it would be a great idea to build some kind of dashboard to get an overview over our environment and whats going on there, so we played around with the SQL Adminstudio and got some good looking SQL queries, implemented these and display the information in some pie charts (highcharts.com).

Little explanation to get along with the SQL queries: In some queries you can see a statement like this:

SYSTEM_OU_NAME0='[YOUR OU AD.XXX.DE/ROOT/SUB/SUB]'

Every select to SYSTEM_OU_NAME0 is necessary in our environment to prevent admins from seeing users and computers of different departments. The string for this select should look like this: ad.contoso.com/contoso/department1 ( associated distinguishedname: ou=department1,ou=contoso,dc=ad,dc=contoso,dc=com).

SCCM.SQL – Get Software installation state

Installationstatus in a pie chart
Here we select the software installation status and group the events by laststate (which is an ID) and laststatename.

Here we select the software installation status information. We use this select statement to display details of the computer and its software state. Use the [stateid] field to select only successfull/failed… installed software

SCCM.SQL – Last computer activity

We select all computers by their last connection time to the sccm server and grouped them in four categories: active, last connection > 10 days, last connection > 30 days, last connection > 90 days.

SCCM.SQL – Computer grouped by operating system

sccm computer by operating system

This select statement returns the operating system and the number of computers grouped by the operating system (natch).

This statement below selects all computer objects with information to the operating system and its version.

SCCM.SQL – Computer grouped by hardware models

Sccm computer by model / type
This select returns the top 8 (most frequently) computer models (manufacturer) in the environment.

And the associated select for the detailview of the computermodels (manufacturer)

If you have any questions or if this little tutorial was helpfull, please leave me a comment!

Veröffentlicht inAllgemeinJQuerySCCM

Schreibe den ersten Kommentar

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.