Report OS counts SCCM

Success

Hello Everyone,

I am going to show you a necessary report for any organization, before that, how can you know how many Windows 10, Windows 7 or Windows XP computer you have in your organization? How can you start or planned to upgrade project without knowing this super important information?

Several ways:

  1. LDAP query directly to Active Directory, PowersShell command:
 Get-ADcomputer -filter * -properties name,operatingSystem | select Name,Operatingsystem
  1. WMIC query
  2. VBS script
  3. SCCM Query

I am going to use on section 4, it is not going to be regular query it is going to be with the chart that we will create in SQL report builder.

Let’s start.

The first step is to create in SCCM query which will provide all operating System captions: Create a new query and put this statement:

select SMS_G_System_OPERATING_SYSTEM.Caption from  SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId

The result:

SCCM QUERY

Go to “Report” category and create a new report:

Create a new report SCCM

Provide a name and description:

Site: specify the location you want the report will be saved

New SCCM REPORT

SQL Report Builder will open:

SQL REPORT BUILDER

Right click on your “DataSetAdminID” and then “Dataset Properties“:

New_DATASET_SCCM

Click on “Query Designer….”:

Query Designer

And here you need to paste your “Operating System” query that we have created:

SCCM REPORT

If you try to RUN the query it will fail:

OperatingSystemReportSCCM

The reason it fails, it’s because that SCCM query using on WQL queries, and SQL using SQL queries, we have to translate the query to SQL query language.

We can search any string in SQL and change it, loot at the figure:

SCCM_Reporting

In soon I’ll upload another article about translation.

Press Ok:

SCCM

Clear page and then go to “Insert” TAB and select on “Chart” > Chart Wizard:

Chart SCCM

Choose your dataset and then continue with next:

chartSCCM

 

Column > Next:

Colunm SCCM

Drag the “Caption0” filed to Series and in Values and then right click on “Caption0” undervalues and select “Count”:

count

 

Choose your style and then finish:

Style

Expand the size of the page

SCCM QUERIES

 

Right-click on the chart and select “Show Data Labels“:

SCCM2012

 

COUNT

Run Run

Result:

Success

That’s it,

Now you can create “Subscriptions” for this report and you will get this report every X you will set to your email.

I hope this article indeed stratify you and you will implement this report on your organization.

Waiting for your comment!

5 Comments
  1. giwn.re 2 years ago
    Reply

    To be honest, this is the first time I am visiting in your blog and really surprised!

    Thanks!

  2. Oleg 2 years ago
    Reply

    Hi, how would you add information about x64 and x86 OS versions?

    • Meir Peleg 2 years ago
      Reply

      Hi Oleg, I haven’t added it to my report but it not hard to add as well, once you have the value you just need translate it from WQL to SQL and then you can build a new report, my report is not presenting 86×64 so in your case I would create a new report under the current report.

      • Oleg 2 years ago
        Reply

        Thank you, will try it later. All the best!

Leave a Comment

Your email address will not be published.

In the news
Load More