Memory Report by Collection
Here at the state I get some interesting requests sometimes. The latest task was to create a report that would show a count of machine at 500 meg intervals. I had some ideas of how to do it and I knew what I wanted it to look like when it was completed but the force isn't with me when it comes to some of the more complex SQL queries (I’m not a programmer). Special thanks to John Nelson (affectionately known by his colleagues as Number 2) ,the force is strong with this one, who was able to write this awesome code for me.
As you may know if you have read any of my blogs on patch management you know that I use a parent collection that has all of the agencies listed below. So I use this list of collections so that I can give reports on agencies and the Commonwealth as a whole.
Again thanks John.
Here is what the report looks like:
Report Code
**************************************
SELECT
col.Name AS [Agency],
SUM(CASE WHEN (mem.TotalPhysicalMemory0/524288) = 0 THEN 1 END) AS [Up to 512MB],
SUM(CASE WHEN (mem.TotalPhysicalMemory0/524288) = 1 THEN 1 END) AS [512MB to 1GB],
SUM(CASE WHEN (mem.TotalPhysicalMemory0/524288) = 2 THEN 1 END) AS [1GB to 1.5GB],
SUM(CASE WHEN (mem.TotalPhysicalMemory0/524288) = 3 THEN 1 END) AS [1.5GB TO 2GB],
SUM(CASE WHEN (mem.TotalPhysicalMemory0/524288) = 4 THEN 1 END) AS [2GB to 2.5GB],
SUM(CASE WHEN (mem.TotalPhysicalMemory0/524288) = 5 THEN 1 END) AS [2.5GB to 3GB],
SUM(CASE WHEN (mem.TotalPhysicalMemory0/524288) = 6 THEN 1 END) AS [3GB to 3.5GB],
SUM(CASE WHEN (mem.TotalPhysicalMemory0/524288) = 7 THEN 1 END) AS [3.5GB to 4GB],
SUM(CASE WHEN (mem.TotalPhysicalMemory0/524288) > 7 THEN 1 END) AS [Over 4GB]
FROM
dbo.v_GS_X86_PC_MEMORY AS mem
INNER JOIN v_FullCollectionMembership AS mbr
ON mem.resourceID = mbr.resourceID
AND CollectionID IN (SELECT subCollectionID FROM dbo.v_CollectToSubCollect WHERE ParentCollectionID = 'xxxxxxxx') <-- change to your parent collection
INNER JOIN dbo.v_Collection AS col
ON mbr.CollectionID = col.CollectionID
INNER JOIN dbo.v_R_System AS sys
ON mem.resourceID = sys.resourceID
AND sys.Operating_System_Name_and0 NOT LIKE '%Server%'
GROUP BY col.Name
ORDER BY 1
*************************************
Chris Stauffer <><