Client Management Suite

Hi All,

I'm trying to get some information out of Altiris which has been asked of me, A report is required listing all people from a [particular AD group with regard to Memory configuration on their PCS.

There is an out of box report called Memory configuration by computer which shows Computer name, total memory, total Slots used slots, free slots and Max available memory. This is all good but I'd like it to show the Primary username also so I can cross reference people from a certain AD group.

Does anyone know how to edit this report? the SQL looks like this

DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'

SELECT DISTINCT 
      i.[Guid],
   i.[Name] [Computer Name],  
      i.[Domain],
      o1.[Total Physical Memory (Bytes)] / (1024 * 1024) as [Total Memory (MB)],
      m.[Memory Devices] as [Total Slots],
     temp.[Used Slots][Used Slots],
      m.[Memory Devices]-temp.[Used Slots] [Free Slots],
      m.[Max Capacity (Kilobytes)] / (1024) as [Max Available memory (MB)] 
FROM dbo.vComputer i    
 JOIN dbo.ScopeMembership sm
  ON sm.[ResourceGuid] = i.Guid
  AND sm.[ScopeCollectionGuid] IN (SELECT * FROM dbo.fnGetTrusteeScopeCollections (@v1_TrusteeScope))
 JOIN dbo.vHWComputerSystem o1 
  ON  o1.[_ResourceGuid] = i.Guid
 JOIN dbo.vOSOperatingSystem cop
  ON cop.[_ResourceGuid]=o1.[_ResourceGuid]
    JOIN dbo.vHWPhysicalMemory a
       on a._ResourceGuid=o1._ResourceGuid
  JOIN (SELECT SUM ([Memory Devices]) [Memory Devices], _ResourceGuid, SUM ([Max Capacity (Kilobytes)]) [Max Capacity (Kilobytes)]
   FROM dbo.vHWPhysicalMemoryArray
   GROUP BY _ResourceGuid) m  
  ON m.[_ResourceGuid] = a._ResourceGuid  
 JOIN (SELECT COUNT (*) [Used Slots],_ResourceGuid FROM vHWPhysicalMemory GROUP BY _ResourceGuid) temp
  ON temp._ResourceGuid = i.Guid
WHERE LOWER (i.[Name]) LIKE LOWER ('%ComputerName%') 
AND (('%Minimum Memory%' = '%')
  OR ('%Minimum Memory%' <> '%' AND o1.[Total Physical Memory (Bytes)] / (1024 * 1024) >= REPLACE ('%Minimum Memory%','%','')))
AND (('%Maximum Memory%' = '%')
  OR ('%Maximum Memory%' <> '%' AND o1.[Total Physical Memory (Bytes)] / (1024 * 1024) <= REPLACE ('%Maximum Memory%','%','')))
--AND cop.[Max Process Memory Size (Kilobytes)] >= '%Minimum Memory%'
--AND cop.[Max Process Memory Size (Kilobytes)] <= '%Maximum Memory%'
  --and (m.[Memory Devices]-count(distinct a._ResourceGuid))=1
AND (('%Number Free Slots%' = '%')
  OR ('%Number Free Slots%' <> '%' AND m.[Memory Devices]-temp.[Used Slots] >= REPLACE ('%Number Free Slots%','%','')))
AND (m.[Memory Devices]-temp.[Used Slots])>-1    
 

Grateful for any help or ideas with this.

Thanks

Shaun

Best to my knowledge,

You will need to clone this report (as default reports cannot be edited). In the cloned report you will need to edit query comparing GUIDs of computers in vcomputer table with user name from Inv_Aex_EU_Contact_Detail table. You may add below in your Query which would look something like this:

Join Inv_AeX_EU_Contact_Detail cntct on i.guid = cntct._resourceguid

-- This would give you Primary User Name, select [Exchange Display Name] column from Inv_AeX_EU_Contact_Detail table

Join Inv_Aex_AC_Identification ac on i.guid=ac._resourceguid

 -- This would give you Last Logged On User Name, select [Last Logon User] column from Inv_Aex_AC_Identification table

Hope this helps....