Greetings All,
Andy was very kind enough to help me by creating a query I can use in
SSRS to show the top processes consuming memory and CPU, here is the URL
to that thread. http://tinyurl.com/chewnl8. I am unable to get the
query to work in AM 9 and I am not sure why. Below is the query I am
using. I would appreciate any help with getting this to work again. As
always many thanks for all your help.

----------------------------------------------

--Get UTC Time
DECLARE
@UTCtd AS DATETIME
SET
@UTCtd = GETUTCDATE()

--Get Today's Date and Time
DECLARE
@TodayTD AS DATETIME
SET
@TodayTD = GETDATE()

--Get Time Zone Informaiton
DECLARE
@TimeDif AS INT
SET
@TimeDif = DATEDIFF(hh, getutcdate(), getdate())

CREATE TABLE #TempData (id INT IDENTITY(1,1), ObjID INT, HID INT,
MachineName VARCHAR(128), DataStatus INT, Legend VARCHAR(1024),
DataModTime DATETIME, Value FLOAT, Detail VARCHAR(MAX), Interval INT,
ModificationTime INT, NextCheck DATETIME, DataAgeMinutes INT)
INSERT #TempData (ObjID, HID, MachineName, DataStatus, Legend,
DataModTime, Value, Detail, Interval, ModificationTime, NextCheck,
DataAgeMinutes)
SELECT
H.ObjID AS ObjID,
H.DataID AS HID,
H.MachineName,
H.Status AS DatAStatus,
REPLACE(H.HiddenLegend, 'Top Processes Consuming the CPU^^%', 'CPU
Utilization') AS Legend,
DATEADD (HH,@TimeDif,dbo.fnSQLDateTime (D.ModificationTime)) AS
DataModTime,
D.Value,
convert(text,coalesce(D.Detaillong, D.Detailshort)) AS Detaillong,
H.Interval,
D.ModificationTime,
DATEADD(ss, H.Interval,DATEADD (HH,@TimeDif,dbo.fnSQLDateTime
(H.ModificationTime))) AS NextCheck,
--The Math is (Current Date) - (H.ModificationTime) = DataAgeMinutes
DATEDIFF(mi, @TodayTD, (DATEADD (HH,@TimeDif,dbo.fnSQLDateTime
(D.ModificationTime)))) AS DataAgeMinutes
FROM
DataHeader H WITH (NOLOCK)
INNER JOIN Data D WITH (NOLOCK)
ON H.DataID = D.DataID
WHERE
H.MachineName IN (@ServerName)
AND KPName LIKE '%TopCpuProc%'
AND H.Status &0x08000000 = 0
ORDER BY
D.ModificationTime DESC

DECLARE
@ctr INT,
@max INT,
@Detail VARCHAR(MAX),
@DetailXML XML

CREATE TABLE #TempDetail(LinkID INT, ProcessName VARCHAR(1024), PID INT,
CPUUtil FLOAT)

SELECT
@ctr = 1,
@max = MAX(id)
FROM
#TempData

WHILE @ctr <= @max
BEGIN
SELECT
@Detail = Detail
FROM
#TempData
WHERE
id = @ctr

IF @Detail IS NOT NULL
BEGIN
SET @DetailXML = CONVERT(XML, @Detail)

INSERT #TempDetail (LinkID, ProcessName, PID, CPUUtil)
SELECT
LinkID = @ctr,
ProcessName = D.c.value('C0[1]', 'NVARCHAR(1024)'),
PID = D.c.value('C1[1]', 'INT'),
CPUUtil = D.c.value('C2[1]', 'FLOAT')
FROM
@DetailXML.nodes('/R') D (c)
END

SET @ctr = @ctr + 1
END

SELECT
A.ObjID,
A.HID,
A.MachineName,
A.DataStatus,
A.Legend,
A.DataModTime,
A.Value,
B.ProcessName,
B.PID,
B.CPUUtil,
A.Interval,
A.ModificationTime,
A.NextCheck,
A.DataAgeMinutes
FROM
#TempData A
INNER JOIN #TempDetail B
ON A.id = B.LinkID
ORDER BY
A.ModificationTime DESC
DROP TABLE #TempData
DROP TABLE #TempDetail


--
abel5405
------------------------------------------------------------------------
abel5405's Profile: https://forums.netiq.com/member.php?userid=5035
View this thread: https://forums.netiq.com/showthread.php?t=57584