Anonymous_User Absent Member.
Absent Member.
738 views

SQL Query to get data from TopCPUProcs


Greetings All,
I am using SSRS to build out reports for disk space, CPU, and other
metrics. Disk space and other metrics are pretty simple to put in a
tablix and make it look good but the TopCPUProcs and TopMemory Procs I
am having a problem with. The query I am using is:
_____________________________
declare @ServerName as varchar(100)

--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())

select
DataHeader.ObjID as ObjID,
DataHeader.DataID as DataHeaderID,
DataHeader.MachineName,
DataHeader.Status as DataStatus,
REPLACE(DataHeader.Legend, 'Top Processes Consuming the CPU^^%', 'CPU
Utilization') as Legend,
DATEADD (HH,@TimeDif,dbo.fnSQLDateTime (Data.ModificationTime)) as
DataModTime,
Data.Value,
convert(text,Data.Detaillong) as Detaillong,
DataHeader.Interval,
Data.ModificationTime,
DATEADD(ss, DataHeader.Interval,DATEADD (HH,@TimeDif,dbo.fnSQLDateTime
(DataHeader.ModificationTime))) as NextCheck,
--The Math is (Current Date) - (DataHeader.ModificationTime) =
DataAgeMinutes
DATEDIFF(mi, @TodayTD, (DATEADD (HH,@TimeDif,dbo.fnSQLDateTime
(Data.ModificationTime)))) as DataAgeMinutes
from
DataHeader with (NOLOCK)
Left Join Data on DataHeader.DataID = Data.DataID
where
DataHeader.MachineName in (@ServerName)
and
KPName like '%TopCPUProc%'
and
DataHeader.Status <> 134217728

order by data.ModificationTime desc
_________________________________________

@ServerName is passed in SSRS to get the right server name. The issue I
am having is presenting the "DetailLong" data, it looks like it's in XML
but I can't seem to get it formated correctly in SSRS. Can that data
some how be reformated so it is SSRS friendly? Many thanks in advance
for your help.

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=53142

0 Likes
2 Replies
Anonymous_User Absent Member.
Absent Member.

Re: SQL Query to get data from TopCPUProcs


In this case, the detail is in XML format as it contains a table which
holds the top X process information. So each data point might have
several values within it. The XML table is intended to be rendered by
the "client" ie the OC or CC UI when you decide to look at it. Normally
for reporting you are interested in the value for a data point rather
than the detail.

It might not make sense to pull out the detail for each data point when
uploading to SSRS - I guess that is for you to decide. But if it has to
be done in the SQL extraction phase, then really the only way to do it
is in a loop. So you pull the data you need into a temporary table, then
process each row to extract the detail and finally join the 2 sets of
data together when extraction to SSRS. And example is below (the code in
the loop can be extracted and used "standalone" - so you could use that
differently if you wanted to... this is just an example).

I also changed the base code you posted - firstly because you should be
checking Detailshort and Detaillong - we only write to one of those
fields. It's likely in the case of this KS we will always use
Detaillong, but not guaranteed (if the detail is 255 characters or less
it ends up in Detailshort). Also you check for an absolute value for
Status and that is a bitwise setting. So that is changed to exclude
deleted data using the bitwise value 0x08000000. And also - Legend can
be modified by the user. So I changed it to use HiddenLegend which
cannot be changed.

And a note on the "Time" fields - in case you are not aware and this is
useful.. The fields Time and BiasedTime are both set by the agent, and
represent the actual time the data point was generated. Time is GMT and
BiasedTime is local time on the agent (so the difference between the two
give you the timezone offset from GMT). The ModificationTime field is
GMT but is the time the data point was written to the QDB. For various
reasons, this might be different to Time - and could be different by
some minutes. So if you want to show data according to the time it was
gathered, use Time rather than ModificationTime...

Example:-

DECLARE
@ServerName AS VARCHAR(128) = 'VORBIS'

--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 #Data (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 #Data (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 #Detail(LinkID INT, ProcessName VARCHAR(1024), PID INT,
CPUUtil FLOAT)

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

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

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

INSERT #Detail (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
#Data A
INNER JOIN #Detail B
ON A.id = B.LinkID
ORDER BY
A.ModificationTime DESC
DROP TABLE #Data
DROP TABLE #Detail


--
Andy Doran
Software Engineer Consultant (NetIQ)
------------------------------------------------------------------------
andy_doran's Profile: https://forums.netiq.com/member.php?userid=3937
View this thread: https://forums.netiq.com/showthread.php?t=53142

0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: SQL Query to get data from TopCPUProcs


Many, many thanks Andy for your info and help! This is awsome! I can't
say thanks enough!


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

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.