Need a Custom Inventory Report with Location Listed

Products

• Inventory Solution

Issue/Introduction

Where can I find a custom report with the details listed below:

• Internal Serial number
• Device ID
• Computer ID
• Hardware Manufacturer
• Model
• Network Mac Address
• Resource name
• Domain
• Primary account
• Logged on account
• Last logged on date
• Location
• Asset Status
• Operating system

Resolution

Providing the information below, there is are default reports present with all these specific details. A custom SQL report needs to be created to provide the information required, and SYMC Technical Support does not normally provide custom reporting information.  For this example we cloned the Computer Summary report and edited the SQL query to get the extra required fields.  This represented "best of effort".

Here is a sample query provided:

DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'{2E1F478A-4986-4223-9D1E-B5920A63AB41},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F},{F5930059-55B6-484F-8403-EE7F5449F2D5}'

SELECT DISTINCT i.Guid,  
i.[User] as [Primary Account],
 d.[Client Date] as [Last logged on date],
 d.[Last Logon User],
vl.Name as Location,  

CASE WHEN i.Domain = '' AND CHARINDEX('.', i.[Name]) <> 0 THEN SUBSTRING (i.[Name],0,CHARINDEX ('.',i.[Name])) ELSE i.[Name] END [Name],     
CASE WHEN i.Domain = '' AND CHARINDEX('.', i.[Name]) <> 0 THEN REPLACE (i.[Name],SUBSTRING (i.[Name],0,CHARINDEX ('.',i.[Name]))+ '.','') ELSE i.Domain END [Domain],    
sn1.Manufacturer [Manufacturer],    
sn1.[Model] [Computer Model],        
cpu.[Max Clock Speed (Mega-hertz)] [CPU Frequency],  
cpu.[Family] [CPU Type],      
sn1.[Total Physical Memory (Bytes)] / (1024 * 1024) [Memory (MB)],      
ISNULL (i.[OS Name],os.[Name]) [Operating System],      
d.[OS Version] [OS Version],              
CST.[Max Media Size (Kilobytes)] / (1024) [Disk Size(MB)],    
ld.[Free Space (Bytes)] [Free Space(MB)],    
sn1.[Identifying Number] [Serial Number]
FROM dbo.vComputer i 
left join ResourceAssociation ra on i.Guid = ra.ParentResourceGuid left join vLocation vl on ra.ChildResourceGuid = vl._ResourceGuid         
    JOIN dbo.ScopeMembership sm
        ON sm.[ResourceGuid] = i.Guid
        AND sm.[ScopeCollectionGuid] IN (SELECT * FROM dbo.fnGetTrusteeScopeCollections (@v1_TrusteeScope)) 
    LEFT JOIN dbo.Inv_AeX_AC_Identification d
        ON d._ResourceGuid = i.Guid
    JOIN dbo.CollectionMembership cm    
        ON cm.ResourceGuid = i.[Guid]    
    JOIN dbo.vCollection it    
        ON it.Guid = cm.CollectionGuid    
    LEFT JOIN dbo.vHWPhysicalMemory m1    
        ON  m1.[_ResourceGuid] = i.Guid       
    JOIN dbo.vHWComputerSystem sn1    
        ON  sn1.[_ResourceGuid] = i.Guid       
    LEFT JOIN dbo.Inv_HW_Processor cpu   
        ON cpu.[_ResourceGuid] = i.[Guid]
    LEFT JOIN  (SELECT SUM ([Free Space (Bytes)] / (1024 * 1024)) [Free Space (Bytes)], _ResourceGuid
        FROM dbo.vHWLogicalDisk WHERE [Logical Disk Type] = 3 GROUP BY _ResourceGuid) ld
        ON i.Guid = ld._ResourceGuid
    LEFT JOIN dbo.vOSOperatingSystem os
        ON os._ResourceGuid = i.Guid
    JOIN (SELECT SUM ([Max Media Size (Kilobytes)]) [Max Media Size (Kilobytes)],  
    _ResourceGuid FROM vHWStorage  
    WHERE [Media Type] = 29  
    GROUP BY _ResourceGuid)CST 
        ON CST._ResourceGuid = i.Guid
WHERE 1 = 1    
AND LOWER (i.[Name]) LIKE LOWER ('%')  
AND i.Domain LIKE '%'    
AND lower (it.[Guid]) LIKE lower ('2c1b4a56-4bc6-4e03-8589-fb53499b7e55') 

Note: Location field will populate only if it is already present. It can be seen on console under Manage>Assets>Manage Configuration Items>Computers and Peripherals>Computer.