Video Screencast Help
Give us your opinion and win with Symantec! Please help us by taking this survey to tell us about your experience with Symantec Connect, so that we can continue to grow and improve.  Take the survey.

Memory swapping report

Created: 14 Sep 2012 • Updated: 16 Oct 2012 | 1 comment
crlima's picture
This issue has been solved. See solution.

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)

Discussion Filed Under:

Comments 1 CommentJump to latest comment

Marianne's picture

You may get better response if you post in the relevant forum....

Herewith Community Product Map: https://www-secure.symantec.com/connect/community-product-map

Supporting Storage Foundation and VCS on Unix and Windows as well as NetBackup on Unix and Windows
Handy NBU Links

SOLUTION