What's new
Forums on Intune, SCCM, and Windows 11

Welcome to the forums. Register a free account today to become a member! Once signed in, you'll be able to participate on this site by adding your topics and posts, as well as connect with other members through your own private inbox!

PENDING SCCM - Custom Report Help

K

ktntech

Member
Messages
5
Reaction score
0
Points
1
Hello,

Would anyone be able to advise how I could create a custom report in SCCM to pull the following information

Machine Name
Make
Model
O/S & Version (21H2)

I'm new to trying to create custom reports so any help would be greatly appreciate.

Thank you in advance for your time and help

Kurt
 
Sort by date Sort by votes
I have something that can help you, here's the SQL script:
SELECT distinct
CS.name0 as "Computer Name",
CS.domain0 as "Domain",
CS.UserName0 as "User",
BIOS.SerialNumber0 as "Bios serial",
SE.SerialNumber0 as "Service Tag",
CS.Manufacturer0 as "Manufacturer",
CS.Model0 as "model",
OS.Caption0 as "OS",
RAA.SMS_Assigned_Sites0 as "Site",
RAM.TotalPhysicalMemory0 as "Total Memory",
SUM(ISNULL(LDisk.Size0,'0')) as "Hardrive Size",
SUM(ISNULL(LDisk.FreeSpace0,'0')) AS "Free Space",
CPU.Name0 as "CPU Model"
FROM
v_GS_COMPUTER_SYSTEM CS right join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID
right join v_GS_SYSTEM SYS on SYS.ResourceID = CS.ResourceID
right join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID
right join v_RA_System_SMSAssignedSites RAA on RAA.ResourceID = CS.ResourceID
right join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID
right join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID
right join v_GS_Processor CPU on CPU.ResourceID = CS.ResourceID
right join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID
WHERE
LDisk.DriveType0 =3
GROUP BY
CS.Name0,
CS.domain0,
CS.Username0,
BIOS.SerialNumber0,
SE.SerialNumber0,
CS.Manufacturer0,
CS.Model0,
OS.Caption0,
RAA.SMS_Assigned_Sites0,
RAM.TotalPhysicalMemory0,
CPU.Name0

You'll have to have get report builder installed on your site server and just create a new report using the script above.
 
Upvote 0 Downvote
ktntech said:
Hello,

Would anyone be able to advise how I could create a custom report in SCCM to pull the following information

Machine Name
Make
Model
O/S & Version (21H2)

I'm new to trying to create custom reports so any help would be greatly appreciate.

Thank you in advance for your time and help

Kurt
Click to expand...
What do you have for a query now? What is not working with it?
 
Upvote 0 Downvote
Hello,

I don't have anything at the moment, this is very much my first use of creating a custom SCCM report.

I've installed report builder and created a new report. I've then attempted to add a new data set but unsure which source to select.

Apologies, very new to this.
 
Upvote 0 Downvote
EdGonz said:
I have something that can help you, here's the SQL script:
SELECT distinct
CS.name0 as "Computer Name",
CS.domain0 as "Domain",
CS.UserName0 as "User",
BIOS.SerialNumber0 as "Bios serial",
SE.SerialNumber0 as "Service Tag",
CS.Manufacturer0 as "Manufacturer",
CS.Model0 as "model",
OS.Caption0 as "OS",
RAA.SMS_Assigned_Sites0 as "Site",
RAM.TotalPhysicalMemory0 as "Total Memory",
SUM(ISNULL(LDisk.Size0,'0')) as "Hardrive Size",
SUM(ISNULL(LDisk.FreeSpace0,'0')) AS "Free Space",
CPU.Name0 as "CPU Model"
FROM
v_GS_COMPUTER_SYSTEM CS right join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID
right join v_GS_SYSTEM SYS on SYS.ResourceID = CS.ResourceID
right join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID
right join v_RA_System_SMSAssignedSites RAA on RAA.ResourceID = CS.ResourceID
right join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID
right join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID
right join v_GS_Processor CPU on CPU.ResourceID = CS.ResourceID
right join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID
WHERE
LDisk.DriveType0 =3
GROUP BY
CS.Name0,
CS.domain0,
CS.Username0,
BIOS.SerialNumber0,
SE.SerialNumber0,
CS.Manufacturer0,
CS.Model0,
OS.Caption0,
RAA.SMS_Assigned_Sites0,
RAM.TotalPhysicalMemory0,
CPU.Name0

You'll have to have get report builder installed on your site server and just create a new report using the script above.
Click to expand...
Hi,

After a little playing around I managed to get the data source specified and ran your SCCM query. Can I ask would you know of a way to add the O/S version too so the report would display - Microsoft Windows 10 Enterprise and also the version such as 21H2 for example?

Thank you both for your help. I really appreciate your time
 
Upvote 0 Downvote
Garth said:
What do you have for a query now? What is not working with it?
Click to expand...
Hello,

Sorry, I used the SQL script provided EdGonz. I've managed to get this working but I also need to have the O/S version such as 21H2. Would you know of an addition I can add to the SQL statement above to also retrieve this information?

Thank you for your help
 
Upvote 0 Downvote
ktntech said:
Hello,

Sorry, I used the SQL script provided EdGonz. I've managed to get this working but I also need to have the O/S version such as 21H2. Would you know of an addition I can add to the SQL statement above to also retrieve this information?

Thank you for your help
Click to expand...
To help point you within the right direction. Where exactly within ConfigMgr do you see that 21H2 listed within ConfigMgr console? This will point you towards the right sql view/colum to use to add to this query.
 
Upvote 0 Downvote
I've managed to get the information that I want but by using two separate reports.

This is a combination of the query above and then running the SCCM report Microsoft Windows Servicing details for a specific collection report. The included report has a field titled OS Release name which is the extra information I required.

I checked the report to see if there was an SQL query I could use as reference to update the SQL statement above but it seems to be written differently with the information coming from inside the report rather than an SQL query.

Would you be know of a way I can include OS Release name information in the SQL query above so that I don't need to run two separate reports?

Again, thank you so much for your help and any pointers. I really appreciate it.
 
Upvote 0 Downvote
ktntech said:
I've managed to get the information that I want but by using two separate reports.

This is a combination of the query above and then running the SCCM report Microsoft Windows Servicing details for a specific collection report. The included report has a field titled OS Release name which is the extra information I required.

I checked the report to see if there was an SQL query I could use as reference to update the SQL statement above but it seems to be written differently with the information coming from inside the report rather than an SQL query.

Would you be know of a way I can include OS Release name information in the SQL query above so that I don't need to run two separate reports?

Again, thank you so much for your help and any pointers. I really appreciate it.
Click to expand...
That report use a function to get the OS release names. What you are asking for can be done but is not super simple. e.g. it would take more than a hour to create and test to ensure that everything is right.

You can try digging into the Official docs to see what else you can find. https://learn.microsoft.com/en-us/m...lviews/sql-server-views-configuration-manager
 
Upvote 0 Downvote
You must log in or register to reply here.

Similar threads

  • PENDING Missing Categories Management Ribbon after 2509 Update
  • PENDING How to Activate Windows 11 with Intune, Local Active Directory, and SCCM
  • PENDING Clients not reporting in to WSUS Server
  • PENDING SCCM Capture media error 0x80070091
  • SOLVED Fresh SCCM Server 2022 Install – Reuse Same Site Code Without Reinstalling All Clients?

    • Forum statistics

    Threads
    7,143
    Messages
    27,891
    Members
    18,188
    Latest member
    samforest

    Latest posts

    Trending content
    Back
    Top