Forum Discussion

crlima's avatar
13 years ago

Memory swapping report

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)