How to include memory in a specific Report.

[polonio210]

Hello Everyone,

I want to build a query to return memory of all computers in a existent query.

How can i do this?

 

[GarthMJ]

Have you looked at the built-in reports for this?

[polonio210]

Hi GarthMJ,

 

I looked, but i need to join both reports, my hw inventory with the memory of each computer and i dont know how.

 

My report is..

 

SELECT Distinct

RSystem.Netbios_Name0 [Hostname],

CASE

WHEN RSystem.Client_Type0 = 1 THEN 'Sim'

ELSE 'Não'

END AS [Client SCCM],

ISNULL(

CASE

WHEN RSystem.Client_Version0 = '4.00.6487.2000' THEN 'SCCM 2007 SP2 RTM'

WHEN RSystem.Client_Version0 = '5.00.7711.0000' THEN 'ConfigMgr 2012 RTM'

WHEN RSystem.Client_Version0 = '5.00.7804.1000' THEN 'ConfigMgr 2012 SP1'

WHEN RSystem.Client_Version0 = '5.00.7804.1202' THEN 'ConfigMgr 2012 SP1 CU1'

WHEN RSystem.Client_Version0 = '5.00.7804.1300' THEN 'ConfigMgr 2012 SP1 CU2'

WHEN RSystem.Client_Version0 = '5.00.7804.1400' THEN 'ConfigMgr 2012 SP1 CU3'

WHEN RSystem.Client_Version0 = '5.00.7804.1500' THEN 'ConfigMgr 2012 SP1 CU4'

WHEN RSystem.Client_Version0 = '5.00.7804.1600' THEN 'ConfigMgr 2012 SP1 CU5'

WHEN RSystem.Client_Version0 = '5.00.7958.1000' THEN 'ConfigMgr 2012 R2 RTM'

WHEN RSystem.Client_Version0 = '5.00.7958.1101' THEN 'ConfigMgr 2012 Post R2 Hotfix'

WHEN RSystem.Client_Version0 = '5.00.7958.1203' THEN 'ConfigMgr 2012 R2 CU1'

WHEN RSystem.Client_Version0 = '5.00.7958.1303' THEN 'ConfigMgr 2012 R2 CU2'

WHEN RSystem.Client_Version0 = '5.00.7958.1401' THEN 'ConfigMgr 2012 R2 CU3'

WHEN RSystem.Client_Version0 = '5.00.7958.1501' THEN 'ConfigMgr 2012 R2 CU4'

WHEN RSystem.Client_Version0 = '5.00.7958.1604' THEN 'ConfigMgr 2012 R2 CU5'

WHEN RSystem.Client_Version0 = '5.00.8239.1000' THEN 'ConfigMgr 2012 R2 SP1'

WHEN RSystem.Client_Version0 = '5.00.8239.1203' THEN 'ConfigMgr 2012 R2 SP1 CU1'

WHEN RSystem.Client_Version0 = '5.00.8239.1301' THEN 'ConfigMgr 2012 R2 SP1 CU2'

ELSE RSystem.Client_Version0

END,'') AS [Client Version],

--ISNULL(

--CASE

-- When RSystem.Active0 = 1 then 'Ativo'

-- When RSystem.Active0 = 0 then 'Inativo'

--END,'') AS [Client Active],

--ISNULL(RSystem.SMS_Unique_Identifier0,'') [sMS Unique Identifier],

ISNULL(RSystem.AD_Site_Name0,'') [AD Site],

RSystem.Resource_Domain_OR_Workgr0 [Domain OR Workgroup],

OUName.System_OU_Name0 [OU],

ISNULL(

CASE

When SysEnclosure.ChassisTypes0 = 1 AND CSystem.Model0 = 'VirtualBox' THEN 'Máquina Virtual'

When RSystem.Is_Virtual_Machine0 = 1 then 'Máquina Virtual'

When RSystem.Is_Virtual_Machine0 <> 1 then 'Máquina Física'

END,'') AS [is Virtual],

CASE

When RSystem.Operating_System_Name_and0 like '%5.0%' then 'Windows 2000 Server'

When RSystem.Operating_System_Name_and0 like '%5.1%' then 'Windows XP'

When RSystem.Operating_System_Name_and0 like '%server%' and RSystem.Operating_System_Name_and0 like '%5.2%' then 'Windows Server 2003'

When RSystem.Operating_System_Name_and0 like '%workstation%' and RSystem.Operating_System_Name_and0 like '%5.2%' then 'Windows XP 64'

When RSystem.Operating_System_Name_and0 like '%server%' and RSystem.Operating_System_Name_and0 like '%6.0%' then 'Windows Server 2008'

When RSystem.Operating_System_Name_and0 like '%workstation%' and RSystem.Operating_System_Name_and0 like '%6.0%' then 'Windows Vista'

When RSystem.Operating_System_Name_and0 like '%server%' and RSystem.Operating_System_Name_and0 like '%6.1%' then 'Windows Server 2008 R2'

When RSystem.Operating_System_Name_and0 like '%workstation%' and RSystem.Operating_System_Name_and0 like '%6.1%' then 'Windows 7'

When RSystem.Operating_System_Name_and0 like '%server%' and RSystem.Operating_System_Name_and0 like '%6.2%' then 'Windows Server 2012'

When RSystem.Operating_System_Name_and0 like '%workstation%' and RSystem.Operating_System_Name_and0 like '%6.2%' then 'Windows 8'

When RSystem.Operating_System_Name_and0 like '%server%' and RSystem.Operating_System_Name_and0 like '%6.3%' then 'Windows Server 2012 R2'

When RSystem.Operating_System_Name_and0 like '%workstation%' and RSystem.Operating_System_Name_and0 like '%6.3%' then 'Windows 8.1'

When RSystem.Operating_System_Name_and0 like '%10%' then 'Windows 10'

END AS [Operating System],

ISNULL(OS.Caption0,'') [sistema Operacional],

ISNULL(OS.CSDVersion0,'') [service Pack],

ISNULL(SCUM.TopConsoleUser0,'') [Top Console User],

ISNULL(RSystem.User_Domain0 + '\' + RSystem.User_Name0,'') [Last Logged on User],

ISNULL(RSystem2.Last_Logon_Timestamp0,'') [Last Logon Timestamp],

ISNULL(DATEDIFF(Day, RSystem2.Last_Logon_Timestamp0, GETDATE()),'') AS [Dias Sem Comunicar com AD],

ISNULL(

CASE

WHEN CSystem.Model0 LIKE '%Virtual%' THEN 'Virtual'

WHEN SysEnclosure.[ChassisTypes0] in ('3','4','6','7','15') THEN 'Desktop'

WHEN SysEnclosure.[ChassisTypes0] in ('1','8','9','10','21') THEN 'Notebook'

WHEN SysEnclosure.[ChassisTypes0] in ('17','23') THEN 'Servidor'

ELSE SysEnclosure.[ChassisTypes0]

END,'') AS [Chassi Type],

ISNULL(CSystem.Manufacturer0,'') [Fabricante],

ISNULL(

CASE WHEN CSystem.Manufacturer0 = 'LENOVO' THEN CSProduct.Version0

ELSE CSystem.Model0

END,'') AS [Modelo],

ISNULL(CSProduct.IdentifyingNumber0,'') [serial Number],

ISNULL(SysEnclosure.SMBIOSAssetTag0,'') [Asset Tag],

ISNULL(CSystem.TotalPhysicalMemory0,'') [Total Memória Física],

(SELECT sum(LD.FreeSpace0)

FROM v_GS_LOGICAL_DISK LD

WHERE LD.ResourceID = RSystem.ResourceID) [Free Disk Space (MB)],

(SELECT sum(DK.Size0)

FROM v_GS_DISK DK

WHERE DK.ResourceID = RSystem.ResourceID) [Total Disk Size (MB)],

ISNULL(STUFF((SELECT (N', '+NAC.IPAddress0) [text()]

FROM v_GS_NETWORK_ADAPTER_CONFIGURATION NAC

WHERE RSystem.ResourceID = NAC.ResourceID for xml path(N'')),1,1,N''), '')

[iP Address], -- Caso exista mutiplos IP Addres, combinar em uma unica linha

ISNULL(STUFF((SELECT (N', '+NAC.MACAddress0) [text()]

FROM v_GS_NETWORK_ADAPTER_CONFIGURATION NAC

WHERE RSystem.ResourceID = NAC.ResourceID AND NAC.IPEnabled0=1 for xml path(N'')),1,1,N''), '')

[Mac Address], -- Caso exista mutiplos Mac Addres, combinar em uma unica linha

ISNULL(OS.LastBootUpTime0,'') [Last Boot Up Time],

ISNULL(DATEDIFF(Day, OS.LastBootUpTime0, GETDATE()),'') AS [Dias Sem Reiniciar Equipto],

--ISNULL(OS.OSArchitecture0,'') [OS Arch],

ISNULL(

CASE WHEN OS.OSLanguage0 = '1046' THEN 'Portuguese_Brazilian'

WHEN OS.OSLanguage0 = '1033' THEN 'English_United_States'

END,'') AS [OS Language],

ISNULL(Processor.Name0,'') [Processador],

ISNULL(Processor.MaxClockSpeed0,'') [Proc.Clock (GHz)],

BIOS.Name0 [bIOS Descrição],

BIOS.SMBIOSBIOSVersion0 [bIOS Versão],

BIOS.SoftwareElementID0 [bIOS Software],

--ISNULL(CHS.LastOnline,'') [Last Client OnLine],

--ISNULL(DATEDIFF(Day, CHS.LastOnline, GETDATE()),'') AS [Dias Ultima vez Online],

ISNULL(CHS.LastSW,'') [Last Software Inventory],

ISNULL(DATEDIFF(Day, CHS.LastSW, GETDATE()),'') AS [Dias Último Inventário SW],

ISNULL(CHS.LastHW,'') [Last Hardware Inventory],

ISNULL(DATEDIFF(Day, CHS.LastHW, GETDATE()),'') AS [Dias Último Inventário HW],

ISNULL(CHS.LastPolicyRequest,'') [Last Policy Request],

ISNULL(DATEDIFF(Day, CHS.LastPolicyRequest, GETDATE()),'') AS [Dias Último Policy Request]

FROM v_R_System RSystem

LEFT JOIN v_GS_COMPUTER_SYSTEM CSystem ON RSystem.ResourceID = CSystem.ResourceID

LEFT JOIN v_GS_COMPUTER_SYSTEM_Product CSProduct ON RSystem.ResourceID = CSProduct.ResourceID

LEFT JOIN v_GS_PROCESSOR Processor ON RSystem.ResourceID = Processor.ResourceID

LEFT JOIN v_GS_SYSTEM_ENCLOSURE SysEnclosure ON RSystem.ResourceID = SysEnclosure.ResourceID

LEFT JOIN v_GS_AMT_AGENT AMT ON RSystem.ResourceID = AMT.ResourceID

LEFT JOIN v_GS_OPERATING_SYSTEM OS ON RSystem.ResourceID = OS.ResourceID

LEFT JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUM ON RSystem.ResourceID = SCUM.ResourceID

LEFT JOIN v_CH_ClientSummary CHS ON RSystem.ResourceID = CHS.ResourceID

LEFT JOIN v_R_User USR ON (RSystem.User_Domain0 + '\' + RSystem.User_Name0) = USR.Unique_User_Name0

left join v_GS_PC_BIOS BIOS ON RSystem.ResourceID = BIOS.ResourceID

LEFT join v_RA_System_SystemOUName OUName ON RSystem.ResourceID = OUName.ResourceID

LEFT JOIN v_R_System AS RSystem2 ON Rsystem.ResourceID = RSystem2.ResourceID

WHERE

RSystem.Operating_System_Name_and0 like '%workstation%' and

RSystem.Operating_System_Name_and0 <> 'Microsoft Windows NT Workstation 5.0' -- Windows 2000

--and (DATEDIFF(Day, RSystem.Last_Logon_Timestamp0, GETDATE()) between 0 and 45)

ORDER BY [Hostname] ASC

[GarthMJ]

So it is unclear to me, did you look at the built-in reports and did you attempts to merge the two reports?