abel5405 Absent Member.
Absent Member.
1397 views

Top CPU Process and Memory Process Report AM 9


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

0 Likes
4 Replies
andy_doran Absent Member.
Absent Member.

Re: Top CPU Process and Memory Process Report AM 9

abel5405;2453264 wrote:
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


In 9.1 the XML is "complete" so in the SQL query you need to look for this line:-

SELECT @BiasedTime = BiasedTime, @Detail = N'<ROOT>' + DataDetail + N'</ROOT>' -- Tag the ROOT stuff on for the xml processing

and change it to:-

SELECT @BiasedTime = BiasedTime, @Detail = DataDetail

Then look for this line:-

FROM OPENXML(@idoc, '/ROOT/R', 2)

and change it to:-

FROM OPENXML(@idoc, '/APPMANAGER/DATADETAIL/TABLE/R', 2)

And it should be OK...
0 Likes
abel5405 Absent Member.
Absent Member.

Re: Top CPU Process and Memory Process Report AM 9


Hey Andy, I am not able to find those lines in the query, am I looking
in the wrong place?


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

0 Likes
andy_doran Absent Member.
Absent Member.

Re: Top CPU Process and Memory Process Report AM 9

I was looking at the code you linked to rather than the code you posted. The code you posted has 2 problems. One is the same issue I was referring to - that the XML format changed for the data detail and now includes the header information. But you are also using a custom function (fnSQLDateTime). The code below has that function removed and the changed XML format....

declare @ServerName nvarchar(max) = 'MARVIN'
--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^^%', 'CPUUtilization') AS Legend,
DATEADD (s, D.ModificationTime - @TimeDif, '19700101') AS DataModTime,
D.Value,
convert(text,coalesce(D.Detaillong, D.Detailshort)) AS Detaillong,
H.Interval,
D.ModificationTime,
DATEADD(s, ((H.ModificationTime - @TimeDif) + H.Interval), '19700101') AS NextCheck,
--The Math is (Current Date) - (H.ModificationTime) = DataAgeMinutes
DATEDIFF(mi, @TodayTD, (DATEADD (s,(D.ModificationTime - @TimeDif), '19700101'))) 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('/APPMANAGER/DATADETAIL/TABLE/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
0 Likes
abel5405 Absent Member.
Absent Member.

Re: Top CPU Process and Memory Process Report AM 9


Sorry Andy, I did not catch that. I can't thank you enough for your
help!! Many, many thanks!!!!

v/r
Chris


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

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.