Sign in
Microsoft.com
 
System Center Configuration Manager TechCenter
 
 
 
System Center Configuration Manager TechCenter > System Center Configuration Manager Forums > Configuration Manager Asset Intelligence > SCCM Maintenance Report
Ask a questionAsk a question
Search Forums:
 

AnswerSCCM Maintenance Report

  • Tuesday, October 06, 2009 10:14 AMCarlo D Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     
    Sign In to Vote
    0
    Sign In to Vote

    Hi Guys,
    We currently run this report to get info on
     - Site
     - Machine name
     - Model
     - Manufacturer
     - Serial Number

    SELECT     TOP 100 PERCENT
    dbo.v_R_System.AD_Site_Name0 AS [AD Site],
    dbo.v_R_System.Name0 AS [Machine Name],
    [Manufacturer] = CASE LEN(ISNULL(dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, '')) WHEN 0 THEN 'No_Data' ELSE dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 END,
                          [Model] = CASE LEN(ISNULL(dbo.v_GS_COMPUTER_SYSTEM.Model0, '')) WHEN 0 THEN 'No_Data' ELSE dbo.v_GS_COMPUTER_SYSTEM.Model0 END,
                          [Serial Number] = CASE LEN(ISNULL(dbo.v_GS_PC_BIOS.SerialNumber0, ''))
                          WHEN 0 THEN 'No_Data' ELSE dbo.v_GS_PC_BIOS.SerialNumber0 END 
    FROM         dbo.v_R_System LEFT OUTER JOIN
                          dbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_LOGICAL_DISK ON dbo.v_R_System.ResourceID = dbo.v_GS_LOGICAL_DISK.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_PROCESSOR ON dbo.v_R_System.ResourceID = dbo.v_GS_PROCESSOR.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_PC_BIOS ON dbo.v_R_System.ResourceID = dbo.v_GS_PC_BIOS.ResourceID LEFT OUTER JOIN
            dbo.v_GS_SYSTEM_ENCLOSURE ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID LEFT OUTER JOIN
            dbo.v_GS_DISK ON dbo.v_R_System.ResourceID = v_GS_DISK.ResourceID
    WHERE     (dbo.v_R_System.Client0 = 1)AND (ISNULL(dbo.v_GS_PROCESSOR.DeviceID0, 'CPU0') = 'CPU0') AND (ISNULL(dbo.v_GS_LOGICAL_DISK.DeviceID0, 'C:') = 'C:') AND (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 3 OR dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 4 OR dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 6 OR dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 15 OR dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 8 OR dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 10) AND (dbo.v_GS_DISK.InterfaceType0 = 'IDE')
    ORDER BY dbo.v_R_System.AD_Site_Name0 DESC, dbo.v_R_System.User_Name0

    My boss wants me to add the IP info also (maintenance reasons and for our helpdesk).
    I am currently downloading report builder to play around a familiarize myself. Can anyone help with the exact code, until I get my head around the report builder

    Regards
    Carlo

     

Answers

  • Tuesday, October 06, 2009 4:04 PMKent Agerlund Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     Answer
    Sign In to Vote
    0
    Sign In to Vote
    Hi,

    You can use this one:

    SELECT     TOP (100) PERCENT dbo.v_R_System.AD_Site_Name0 AS [AD Site], dbo.v_R_System.Name0 AS [Machine Name],
                          CASE LEN(ISNULL(dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, ''))
                          WHEN 0 THEN 'No_Data' ELSE dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 END AS Manufacturer, CASE LEN(ISNULL(dbo.v_GS_COMPUTER_SYSTEM.Model0, ''))
                          WHEN 0 THEN 'No_Data' ELSE dbo.v_GS_COMPUTER_SYSTEM.Model0 END AS Model, CASE LEN(ISNULL(dbo.v_GS_PC_BIOS.SerialNumber0, ''))
                          WHEN 0 THEN 'No_Data' ELSE dbo.v_GS_PC_BIOS.SerialNumber0 END AS [Serial Number], dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0
    FROM         dbo.v_R_System INNER JOIN
                          dbo.v_GS_NETWORK_ADAPTER_CONFIGUR ON dbo.v_R_System.ResourceID = dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_LOGICAL_DISK ON dbo.v_R_System.ResourceID = dbo.v_GS_LOGICAL_DISK.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_PROCESSOR ON dbo.v_R_System.ResourceID = dbo.v_GS_PROCESSOR.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_PC_BIOS ON dbo.v_R_System.ResourceID = dbo.v_GS_PC_BIOS.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_SYSTEM_ENCLOSURE ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_DISK ON dbo.v_R_System.ResourceID = dbo.v_GS_DISK.ResourceID
    WHERE     (dbo.v_R_System.Client0 = 1) AND (ISNULL(dbo.v_GS_PROCESSOR.DeviceID0, 'CPU0') = 'CPU0') AND (ISNULL(dbo.v_GS_LOGICAL_DISK.DeviceID0, 'C:') = 'C:') AND
                          (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 3 OR
                          dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 4 OR
                          dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 6 OR
                          dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 15 OR
                          dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 8 OR
                          dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 10) AND (dbo.v_GS_DISK.InterfaceType0 = 'IDE') AND
                          (dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0 IS NOT NULL)
    ORDER BY [AD Site] DESC, dbo.v_R_System.User_Name0

    Kent Agerlund | http://scug.dk/members/Agerlund/default.aspx | The Danish community for System Center products
    • Marked As Answer byCarlo D Tuesday, November 17, 2009 1:00 PM
    •  
     

All Replies

  • Tuesday, October 06, 2009 4:04 PMKent Agerlund Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     Answer
    Sign In to Vote
    0
    Sign In to Vote
    Hi,

    You can use this one:

    SELECT     TOP (100) PERCENT dbo.v_R_System.AD_Site_Name0 AS [AD Site], dbo.v_R_System.Name0 AS [Machine Name],
                          CASE LEN(ISNULL(dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, ''))
                          WHEN 0 THEN 'No_Data' ELSE dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 END AS Manufacturer, CASE LEN(ISNULL(dbo.v_GS_COMPUTER_SYSTEM.Model0, ''))
                          WHEN 0 THEN 'No_Data' ELSE dbo.v_GS_COMPUTER_SYSTEM.Model0 END AS Model, CASE LEN(ISNULL(dbo.v_GS_PC_BIOS.SerialNumber0, ''))
                          WHEN 0 THEN 'No_Data' ELSE dbo.v_GS_PC_BIOS.SerialNumber0 END AS [Serial Number], dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0
    FROM         dbo.v_R_System INNER JOIN
                          dbo.v_GS_NETWORK_ADAPTER_CONFIGUR ON dbo.v_R_System.ResourceID = dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_LOGICAL_DISK ON dbo.v_R_System.ResourceID = dbo.v_GS_LOGICAL_DISK.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_PROCESSOR ON dbo.v_R_System.ResourceID = dbo.v_GS_PROCESSOR.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_PC_BIOS ON dbo.v_R_System.ResourceID = dbo.v_GS_PC_BIOS.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_SYSTEM_ENCLOSURE ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_DISK ON dbo.v_R_System.ResourceID = dbo.v_GS_DISK.ResourceID
    WHERE     (dbo.v_R_System.Client0 = 1) AND (ISNULL(dbo.v_GS_PROCESSOR.DeviceID0, 'CPU0') = 'CPU0') AND (ISNULL(dbo.v_GS_LOGICAL_DISK.DeviceID0, 'C:') = 'C:') AND
                          (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 3 OR
                          dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 4 OR
                          dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 6 OR
                          dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 15 OR
                          dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 8 OR
                          dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 10) AND (dbo.v_GS_DISK.InterfaceType0 = 'IDE') AND
                          (dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0 IS NOT NULL)
    ORDER BY [AD Site] DESC, dbo.v_R_System.User_Name0

    Kent Agerlund | http://scug.dk/members/Agerlund/default.aspx | The Danish community for System Center products
    • Marked As Answer byCarlo D Tuesday, November 17, 2009 1:00 PM
    •