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:
- LDAP query directly to Active Directory, PowersShell command:
Get-ADcomputer -filter * -properties name,operatingSystem | select Name,Operatingsystem
- WMIC query
- VBS script
- 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:
Go to “Report” category and create a new report:
Provide a name and description:
Site: specify the location you want the report will be saved
SQL Report Builder will open:
Right click on your “DataSetAdminID” and then “Dataset Properties“:
Click on “Query Designer….”:
And here you need to paste your “Operating System” query that we have created:
If you try to RUN the query it will fail:
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:
In soon I’ll upload another article about translation.
Press Ok:
Clear page and then go to “Insert” TAB and select on “Chart” > Chart Wizard:
Choose your dataset and then continue with next:
Column > Next:
Drag the “Caption0” filed to Series and in Values and then right click on “Caption0” undervalues and select “Count”:
Choose your style and then finish:
Expand the size of the page
Right-click on the chart and select “Show Data Labels“:
Result:
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!
