Hi,
I need a sql report showing the changes in the machine's memory made by someone. The report below it's outdated, because the tables referred have been changed.
Regards.
SELECT ConfigRequestTbl.Name, DATEDIFF(d, ConfigRequestTbl.LastConfigRequest, GETDATE()) AS [Days since in contact with NS], CASE WHEN DATEDIFF(d,
ConfigRequestTbl.LastConfigRequest, GETDATE()) > 5 THEN 1 ELSE 0 END AS [>5days Since Last contact with NS],
dbo.Inv_AeX_HW_Serial_Number.[System Manufacturer], CASE WHEN dbo.Inv_AeX_HW_Serial_Number.[Computer Type] IS NULL
THEN 'Unknown' WHEN dbo.Inv_AeX_HW_Serial_Number.[Computer Type] = 'Low Profile Desktop' THEN 'Deskop' WHEN dbo.Inv_AeX_HW_Serial_Number.[Computer Type]
= 'Main Server Chassis' THEN 'Server' WHEN dbo.Inv_AeX_HW_Serial_Number.[Computer Type] = 'Mini Tower' THEN 'Desktop' WHEN dbo.Inv_AeX_HW_Serial_Number.[Computer Type]
= 'Notebook' THEN 'Laptop' WHEN dbo.Inv_AeX_HW_Serial_Number.[Computer Type] = 'Portable' THEN 'Laptop' WHEN dbo.Inv_AeX_HW_Serial_Number.[Computer Type]
= 'Space Saving' THEN 'Server' WHEN dbo.Inv_AeX_HW_Serial_Number.[Computer Type] = 'Tower' THEN 'Desktop' ELSE dbo.Inv_AeX_HW_Serial_Number.[Computer Type]
END AS [Asset Type], dbo.Inv_AeX_HW_Serial_Number.[Serial Number], LOG_HD.[Total Size in GB] AS [Total Logical Disk Size (GB)],
LOG_HD.[Total Free Space in GB] AS [Total Logical Disk Free Space(GB)], LOG_HD.[Number of disks ],
dbo.Inv_AeX_HW_Memory.[Total Physical Memory] / 1048576 AS [Total Ram(MB)], dbo.vComputer.[OS Name]
FROM (SELECT DISTINCT
_ResourceGuid, ROUND(SUM([Size in MBytes] / 1024), 3) AS [Total Size in GB], SUM([Free Space in MBytes] / 1024)
AS [Total Free Space in GB], COUNT(Name) AS [Number of disks ]
FROM dbo.Inv_AeX_HW_Logical_Disk
WHERE (Description = 'Local Disk')
GROUP BY _ResourceGuid) AS LOG_HD LEFT OUTER JOIN
dbo.vComputer ON LOG_HD._ResourceGuid = dbo.vComputer.Guid RIGHT OUTER JOIN
dbo.Inv_AeX_HW_Serial_Number ON dbo.vComputer.Guid = dbo.Inv_AeX_HW_Serial_Number._ResourceGuid RIGHT OUTER JOIN
(SELECT MachineNames.Name, LastConfigReq.EventTime AS LastConfigRequest, MachineNames.Guid
FROM (SELECT DISTINCT i.Name, i.Guid
FROM dbo.Item AS i CROSS JOIN
dbo.Evt_NS_Client_Config_Request AS nscr
WHERE (i.Guid IN
(SELECT ResourceGuid
FROM dbo.Evt_NS_Client_Config_Request AS nscr)) AND
(i.ClassGuid LIKE '539626D8-A35A-47EB-8B4A-64D3DA110D01') AND (i.Guid NOT IN
(SELECT DISTINCT _ResourceGuid
FROM dbo.Inv_AeX_AC_Identification
WHERE ([OS Type] LIKE '%server%'))) AND (i.Guid IN
(SELECT DISTINCT _ResourceGuid
FROM dbo.Inv_AeX_AC_Client_Agent
WHERE ([Agent Name] LIKE 'Altiris Agent')))) AS MachineNames LEFT OUTER JOIN
(SELECT ResourceGuid, MAX(_eventTime) AS EventTime
FROM dbo.Evt_NS_Client_Config_Request
GROUP BY ResourceGuid) AS LastConfigReq ON MachineNames.Guid = LastConfigReq.ResourceGuid) AS ConfigRequestTbl ON
dbo.vComputer.Guid = ConfigRequestTbl.Guid LEFT OUTER JOIN
dbo.Inv_AeX_HW_Memory ON dbo.vComputer.Guid = dbo.Inv_AeX_HW_Memory._ResourceGuid
WHERE (ConfigRequestTbl.Name IS NOT NULL)
