Sign in
Microsoft.com
 
System Center Configuration Manager TechCenter
 
 
 
System Center Configuration Manager TechCenter > System Center Configuration Manager Forums > Configuration Manager Inventory > Create Custom-built Report
Ask a questionAsk a question
Search Forums:
 

Proposed AnswerCreate Custom-built Report

  • Monday, October 19, 2009 7:16 AMdensei27 Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     
    Sign In to Vote
    0
    Sign In to Vote
    Dear All

         Our Customer want to create baseline report for inventory their hardware, they want to when they run report like they want to know now in their office how manay client that have CPU lower 2.0 GHz , RAM lower 1 GB , HDD size lower 30 GHZ, but there want to resoult is on same one report 

       please tell me how to create and what is SQL sommand that I will put on it 
     

All Replies

  • Monday, October 19, 2009 8:15 AMKent Agerlund Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     Proposed Answer
    Sign In to Vote
    0
    Sign In to Vote
    Hi,

    I took a standard report and modified it a bit. The values are written in bold and thus easily for you to change.

    SELECT DISTINCT
                          TOP (100) PERCENT dbo.v_R_System_Valid.ResourceID, dbo.v_R_System_Valid.Netbios_Name0 AS [Computer Name],
                          dbo.v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup], dbo.v_Site.SiteName AS [SMS Site Name],
                          CASE WHEN (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 IS NULL OR
                          v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = '-1')
                          THEN 'Unknown' ELSE v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 END AS [Top Console User],
                          dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level],
                          dbo.v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [Serial Number], dbo.v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 AS [Asset Tag],
                          dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Manufacturer, dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model,
                          dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)], dbo.v_GS_PROCESSOR.NormSpeed0 AS [Processor (GHz)],
                              (SELECT     SUM(dbo.v_GS_LOGICAL_DISK.Size0) AS Expr1
                                FROM          dbo.v_GS_LOGICAL_DISK INNER JOIN
                                                       dbo.v_FullCollectionMembership ON dbo.v_FullCollectionMembership.ResourceID = dbo.v_GS_LOGICAL_DISK.ResourceID
                                WHERE      (dbo.v_GS_LOGICAL_DISK.ResourceID = dbo.v_R_System_Valid.ResourceID)) AS [Disk Space (MB)],
                              (SELECT     SUM(v_GS_LOGICAL_DISK_2.FreeSpace0) AS Expr1
                                FROM          dbo.v_GS_LOGICAL_DISK AS v_GS_LOGICAL_DISK_2 INNER JOIN
                                                       dbo.v_FullCollectionMembership AS v_FullCollectionMembership_2 ON
                                                       v_FullCollectionMembership_2.ResourceID = v_GS_LOGICAL_DISK_2.ResourceID
                                WHERE      (v_GS_LOGICAL_DISK_2.ResourceID = dbo.v_R_System_Valid.ResourceID)) AS [Free Disk Space (MB)]
    FROM         dbo.v_R_System_Valid INNER JOIN
                          dbo.v_GS_OPERATING_SYSTEM ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.v_R_System_Valid.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_SYSTEM_ENCLOSURE_UNIQUE ON dbo.v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = dbo.v_R_System_Valid.ResourceID INNER JOIN
                          dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System_Valid.ResourceID INNER JOIN
                          dbo.v_GS_X86_PC_MEMORY ON dbo.v_GS_X86_PC_MEMORY.ResourceID = dbo.v_R_System_Valid.ResourceID INNER JOIN
                          dbo.v_GS_PROCESSOR ON dbo.v_GS_PROCESSOR.ResourceID = dbo.v_R_System_Valid.ResourceID INNER JOIN
                          dbo.v_FullCollectionMembership AS v_FullCollectionMembership_1 ON
                          v_FullCollectionMembership_1.ResourceID = dbo.v_R_System_Valid.ResourceID LEFT OUTER JOIN
                          dbo.v_Site ON v_FullCollectionMembership_1.SiteCode = dbo.v_Site.SiteCode INNER JOIN
                          dbo.v_GS_LOGICAL_DISK AS v_GS_LOGICAL_DISK_1 ON v_GS_LOGICAL_DISK_1.ResourceID = dbo.v_R_System_Valid.ResourceID AND
                          v_GS_LOGICAL_DISK_1.DeviceID0 = SUBSTRING(dbo.v_GS_OPERATING_SYSTEM.WindowsDirectory0, 1, 2) LEFT OUTER JOIN
                          dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP ON
                          dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = dbo.v_R_System_Valid.ResourceID
    WHERE     ((SELECT     SUM(v_GS_LOGICAL_DISK_3.Size0) AS Expr1
                             FROM         dbo.v_GS_LOGICAL_DISK AS v_GS_LOGICAL_DISK_3 INNER JOIN
                                                   dbo.v_FullCollectionMembership AS v_FullCollectionMembership_3 ON
                                                   v_FullCollectionMembership_3.ResourceID = v_GS_LOGICAL_DISK_3.ResourceID
                             WHERE     (v_GS_LOGICAL_DISK_3.ResourceID = dbo.v_R_System_Valid.ResourceID)) < 30000) AND (dbo.v_GS_PROCESSOR.NormSpeed0 < 2000) AND
                          (dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 < 10240)
    ORDER BY [Computer Name]
    Kent Agerlund | http://scug.dk/members/Agerlund/default.aspx | The Danish community for System Center products