abel5405 Absent Member.
Absent Member.
2026 views

Revisiting TOP 10 CPU Proc Report

Greetings,
Tried reopening the last thread but it was closed. Any graciously created a way for me to parse out Top CPU Proc data into an SSRS report. I was wondering if it were possible to re-write the query so the results are saved into a ScratchDB. Below is my attempt at it but I fear it does not work. Can this be done? As always many many thanks for any help.

___________________________________


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

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

--Used as a check point for importing new data.
Declare @CheckPoint as INT
select @CheckPoint = (Select max(time) from [Server\QDB].QDB.dbo.data)

CREATE TABLE TopdCPUData (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 TopCPUData (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
AND D.Time > @CheckPoint

ORDER BY
D.ModificationTime DESC

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

CREATE TABLE TopCPUDetail(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 TopCPUDetail (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
TopCPUData A
INNER JOIN TopCPUDetail B
ON A.id = B.LinkID
ORDER BY
A.ModificationTime DESC
Tags (1)
0 Likes
2 Replies
AutomaticReply Absent Member.
Absent Member.

Re: Revisiting TOP 10 CPU Proc Report

abel5405,

It appears that in the past few days you have not received a response to your
posting. That concerns us, and has triggered this automated reply.

These forums are peer-to-peer, best effort, volunteer run and that if your issue
is urgent or not getting a response, you might try one of the following options:

- Visit https://www.microfocus.com/support-and-services and search the knowledgebase and/or check
all the other self support options and support programs available.
- Open a service request: https://www.microfocus.com/support
- You could also try posting your message again. Make sure it is posted in the
correct newsgroup. (http://forums.microfocus.com)
- You might consider hiring a local partner to assist you.
https://www.partnernetprogram.com/partnerfinder/find.html

Be sure to read the forum FAQ about what to expect in the way of responses:
http://forums.microfocus.com/faq.php

Sometimes this automatic posting will alert someone that can respond.

If this is a reply to a duplicate posting or otherwise posted in error, please
ignore and accept our apologies and rest assured we will issue a stern reprimand
to our posting bot.

Good luck!

Your Micro Focus Forums Team
http://forums.microfocus.com



0 Likes
andy_doran Absent Member.
Absent Member.

Re: Revisiting TOP 10 CPU Proc Report

What's not working?
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.