Jaa

SCCM 2012 Reporting: Get system serial number & create custom SSRS report

  • Artikkeli

 

Once again our expert Manoj Kumar sharing his valuable insight on SCCM 2012 Reporting.

In Configuration manager 2012 we do not have an out of the box report which shows the system serial number. Below is a very simple report which includes: System Name, User Name, OS, Service Pack, Manufacturer, Serial Number, Model & System Type.

This step by step guide will show you how to setup your custom report to show-up the system serial number.

Open SCCM console and open the Monitoring > Overview > Reporting> Right click the “Reports” node and click on Create Report.

clip_image002

 

Click on SQL- Based Report

zclip_image003

 

Click to Browse the category to save the report in 

clip_image005

 

Click OK and then Click Next till you reach  summary page and then close after the report is created.

When you finish the report the SQL Server Report Builder should now open.

clip_image006

 

If you already see data source connection, just hit Test Connection toverify it If not, then click Browse Enter in the credentials that have access to the SCCM database and click Next.

clip_image007

 

You’ll now hit the “Design a query”  Click on Edit as Text in the top left corner.

clip_image008

 

Paste the below query and hit red color Exclamation Mark just next to import

Select sys.Name0 Machine, CS.UserName0 'User Name',OS.Caption0 OS, OS.CSDVersion0 'Service Pack',CS.Manufacturer0 Manufacturer,CS.Model0 Model,BIOS.SerialNumber0 'Serial Number',RAM.Capacity0 Memory, cs.SystemType0

From v_R_System SYS

  left join v_GS_COMPUTER_SYSTEM CS on sys.Name0=cs.Name0

  left join v_GS_PC_BIOS BIOS on sys.ResourceID=bios.ResourceID

  left join v_GS_OPERATING_SYSTEM OS on sys.ResourceID=OS.ResourceID

  left join v_GS_PHYSICAL_MEMORY RAM on sys.ResourceID=ram.ResourceID

  --Where sys.Name0 = @variable

  order by sys.Name0

clip_image009

 

On the next screen, you’ll get the ability to lay out how you want your report to look. I’ve selected all the fields from available and drag it over ∑ values box

clip_image010

 

Click next on Choose the layout page

clip_image011

 

Click Next and then Finish. You should be back at the report builder screen. You can start modifying the report to look exactly how you’d like

clip_image012

 

You can always go back to the design by clicking Design if you need to make modifications or add extra information to this report like below.

On the design page if you want to put header click above the report and insert the Text Box and type the report name as you like

clip_image014clip_image016

 

If you want to bring the total number of machines on this page, insert new Text box just next to the report header. Right click inside the box click Expression

clip_image017

 

Paste this query

=Count(Fields!Machine.Value, "DataSet1")

After you adjusting all the fields the way you want and when all this is done, click on Run to view the report

clip_image018

 

Click on Save icon, you should be able to see the reports under “Site – General” you should now see your new report. This should  appear in the console as well as the web reports.

clip_image020

 

 

Happy Learning!

Comments

  • Anonymous
    January 01, 2003
    thanks for sharing.
  • Anonymous
    January 01, 2003
    Great post. Thank you.
  • Anonymous
    January 01, 2003
    I am looking for a query to determine the current logged in user on a domain joined computer. I found a query powershell that tells me the last logged in user for a specific computer but does not tell me if the user is currently logged in to that computer. I am somewhat surprised that SCCM does not list that information with the client machine without having to run a separate query.

    Thank you.
  • Anonymous
    January 01, 2003
    whitch variable would have to add to list it for Collections?
  • Anonymous
    December 05, 2013
    The comment has been removed
  • Anonymous
    March 19, 2014
    Is it possible to add more fields to the query in the post? I also need the fields Mac Address and IP Address.
  • Anonymous
    April 24, 2014
    Thanks it works perfect for me!.
  • Anonymous
    May 29, 2014
    When following the instructions above, my report is full of duplicates. Any idea why? We're on SCCM 2012 R2
  • Anonymous
    June 17, 2014
    Great post but I am also having the same issue as Brachus. Duplicates.... We are also on SCCM 2012 R2. Anyone?????
  • Anonymous
    July 30, 2014
    Hi how can i include the bios verson on this report
  • Anonymous
    September 08, 2014
    SSRS Report that has dropdown menu for OS Version and displays asset information, OS install date and BIOS version.

    Select distinct SMS_R_System.ItemKey,
    SMS_R_System.Name0,SMS_R_System.User_Name0,SMS_R_System.Last_Logon_Timestamp0,
    SMS_G_System_OPERATING_SYSTEM.Caption00,
    SMS_G_System_OPERATING_SYSTEM.InstallDate00,
    SMS_G_System_SYSTEM_ENCLOSURE.Manufacturer00,
    SMS_G_System_COMPUTER_SYSTEM.Model00,
    SMS_G_System_SYSTEM_ENCLOSURE.SerialNumber00,
    SMS_G_System_SYSTEM_ENCLOSURE.SMBIOSAssetTag00,
    SMS_G_System_PC_BIOS.SMBIOSBIOSVersion00
    from vSMS_R_System AS SMS_R_System
    LEFT OUTER JOIN System_System_OU_Name_ARR AS __mSystem_System_OU_Name_ARR0 ON SMS_R_System.ItemKey = __mSystem_System_OU_Name_ARR0.ItemKey INNER JOIN System_Enclosure_DATA AS SMS_G_System_SYSTEM_ENCLOSURE ON SMS_G_System_SYSTEM_ENCLOSURE.MachineID = SMS_R_System.ItemKey
    INNER JOIN Operating_System_DATA AS SMS_G_System_OPERATING_SYSTEM ON SMS_G_System_OPERATING_SYSTEM.MachineID = SMS_R_System.ItemKey
    INNER JOIN PC_BIOS_DATA AS SMS_G_System_PC_BIOS ON SMS_G_System_PC_BIOS.MachineID = SMS_R_System.ItemKey INNER JOIN Computer_System_DATA AS SMS_G_System_COMPUTER_SYSTEM ON SMS_G_System_COMPUTER_SYSTEM.MachineID = SMS_R_System.ItemKey
    where __mSystem_System_OU_Name_ARR0.System_OU_Name0 = N'Domain.com/OU/COMPUTERS'
    and (SMS_G_System_OPERATING_SYSTEM.Caption00= @OSVersion)
  • Anonymous
    September 22, 2014
    Also get the dublicate computer names but not for all, any idea why?
  • Anonymous
    October 01, 2014
    Looks related to number of installed memory chips in the computer. Probably needs to summarize for total installed memory.
  • Anonymous
    October 31, 2014
    Thans alot for the support but at the system type i am geting systemtype0
  • Anonymous
    October 31, 2014
    Dear MR Felix when i am testing your ssrs report i am getting
    ItemKey+A1:L1 Name0 User_Name0 Last_Logon_Timestamp0 Caption00 InstallDate00 Manufacturer00 Model00 SerialNumber00 SMBIOSAssetTag00 SMBIOSBIOSVersion00
  • Anonymous
    November 14, 2014
    Where would I add the filter to only pull clients that have SCCM installed?
  • Anonymous
    November 19, 2014
    I think the Filter your looking for is V_gs_system client=1 but I'm not certain on that. Maybe someone else on this thread can correct it.
  • Anonymous
    November 28, 2014
    How do i summarize for total memory, i am getting duplicates aswell
  • Anonymous
    December 23, 2014
    if you have the answer to summarize the memory that interrest me! :)
  • Anonymous
    January 01, 2015
    The comment has been removed
  • Anonymous
    April 14, 2015
    I'm no SQL expert (this is the first time I've actually messed with SQL), but I've found a work around. If you remove the following information, you will no longer have duplicates:

    RAM.Capacity0 Memory
    left join v_GS_PHYSICAL_MEMORY RAM on sys.ResourceID=ram.ResourceID

    This might not be an ideal solution for everyone, if you are really wanting system memory on there, but it'll get rid of the duplicates.

    Removing RAM.Capacity0 Memory removes the Memory field, and removing the left join... removes the information that populates that field.
  • Anonymous
    April 14, 2015
    I take back what I just said. I got with one of our DBA's and had him take a look at this. Here is what we did to get it to work:

    Select sys.Name0 Machine,OS.Caption0 OS,CS.Manufacturer0 Manufacturer,CS.Model0 Model,BIOS.SerialNumber0 'Serial Number',RAM.Capacity0 Memory, cs.SystemType0
    From v_R_System SYS
    left join v_GS_COMPUTER_SYSTEM CS on sys.Name0=cs.Name0
    left join v_GS_PC_BIOS BIOS on sys.ResourceID=bios.ResourceID
    left join v_GS_OPERATING_SYSTEM OS on sys.ResourceID=OS.ResourceID
    left join ( Select SUM(Capacity0) Capacity0, ResourceID From v_GS_PHYSICAL_MEMORY Group By ResourceID) RAM on sys.ResourceID=ram.ResourceID
    --Where sys.Name0 = @variable
    order by sys.Name0

    That will remove the duplicates.
  • Anonymous
    June 23, 2015
    you should use "RAM.TotalPhysicalMemory0" from "V_GS_X86_PC_MEMORY as RAM"
  • Anonymous
    July 16, 2015
    The comment has been removed
  • Anonymous
    August 04, 2015
    thanks its worked