Jump to content


polonio210

How to include memory in a specific Report.

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...