Anonymous_User Absent Member.
Absent Member.
447 views

Top 10 CPU and MEM report


Hello,

Is there another report or sql query that can give us a better format of
the data than the out of the box nt_reportTopCpuProcs and
_reportTopMemoryProcs? E.g., there is no way to export the data into
excel and you have to drill down to each data point to see the top
procs. We are looking for a report that lists the server and the top
procs over the last 30 days on one page and that can be exported to
excel, csv, etc.

Thanks


--
mcschall
------------------------------------------------------------------------
mcschall's Profile: https://forums.netiq.com/member.php?userid=4778
View this thread: https://forums.netiq.com/showthread.php?t=47515

0 Likes
3 Replies
Anonymous_User Absent Member.
Absent Member.

Re: Top 10 CPU and MEM report


It's complicated - and potentially slow - but possible... below is a
query that works for TopMemoryProcs... basically its just an example,
but you can edit it to specify the data stream (the dataid), the number
of days to include (working back from "now".. but you could change that
also), and the Number of procs to include as "top".. so by default this
will list the top 20 users from the past 30 days.

If what you wanted was to combine the data... ie for PID 123, SUM all
the values together for the time period, then show the top based on the
SUM, that I guess could be done...the information required is in the raw
data... but this query doesn't do that... And the execution of something
like that would take far longer...

But anyway.. as an example, see if this is close to what you are looking
for:-

/*
++
File: TopMemoryConsumersInLastXDays.sql
Created: 18-Apr-2013, by Andy
Reason: Forum request
--
*/

SET NOCOUNT ON

DECLARE @dataid INT
SET @dataid = 223671 -- Change this to be the data id you are interested
in

DECLARE @NumProcesses INT
SET @NumProcesses = 20 -- Change this to be the number of processes over
the time period you want

DECLARE @start INT, @end INT
DECLARE @NumDays INT

--
-- Change the dates below to get your date range
--

SET @NumDays = 30
SELECT @end = DATEDIFF(s, '19700101', GETUTCDATE()) -- GETUTCDATE() is
"now", so change this for the end date.. ie '1-Jan-2013'
SELECT @start = @end - (60 * 60 * 24 * @NumDays)

--
-- This table is used for the extracted information
--

CREATE TABLE #Data (id INT IDENTITY (1, 1), BiasedTime NVARCHAR(20),
DataDetail NVARCHAR(MAX))
INSERT #Data (BiasedTime, DataDetail)
SELECT CONVERT(NVARCHAR(20), BiasedTime), COALESCE(Detaillong,
Detailshort)
FROM dbo.ArchiveData WITH (NOLOCK)
WHERE DataID = @dataid
AND BiasedTime BETWEEN @start AND @end
ORDER BY BiasedTime

DECLARE @Detail NVARCHAR(MAX), @BiasedTime INT

--
-- This table is used for the extracted output
--

CREATE TABLE #Final (ProcessName NVARCHAR(256), MemUsedKB FLOAT,
BiasedTime INT)

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

--
-- Loop through what was extracted
--

WHILE @ctr <= @max BEGIN
SELECT @BiasedTime = BiasedTime, @Detail = N'<ROOT>' + DataDetail +
N'</ROOT>' -- Tag the ROOT stuff on for the xml processing
FROM #Data
WHERE id = @ctr

DECLARE @idoc INT
EXEC sp_xml_preparedocument @idoc OUTPUT, @Detail

INSERT #Final (ProcessName, MemUsedKB, BiasedTime)
SELECT C0, C2, @BiasedTime
FROM OPENXML(@idoc, '/ROOT/R', 2)
WITH (C0 NVARCHAR(256), C2 FLOAT) -- In this stream C0 is the proces
name and C2 the utilisation. C1 is PID if needed

EXEC sp_xml_removedocument @idoc

SET @ctr = @ctr + 1
END

SELECT TOP (@NumProcesses) ProcessName, MemUsedKB, LocalTime =
DATEADD(s, BiasedTime, '19700101')
FROM #Final
WHERE UPPER(ProcessName) != 'TOTAL'
ORDER BY memUsedKB DESC

DROP TABLE #Data
DROP TABLE #Final


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

0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: Top 10 CPU and MEM report


BTW - the problem with the "SUM" idea - apart from the CPU involved to
calculate it... is that a PID isn't reliable... a process A can start
with PID 123 on Monday, and then on Tuesday a process B might gt that
PID if the first one went away...


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

0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: Top 10 CPU and MEM report


And below is for TopCPU (practically the same)

/*
++
File: TopCPUConsumersInLastXDays.sql
Created: 18-Apr-2013, by Andy
Reason: Forum request

select * from DataHeader where KPName like '%TopCp%'
--
*/

SET NOCOUNT ON

DECLARE @dataid INT
SET @dataid = 223671--223671 -- Change this to be the data id you are
interested in

DECLARE @NumProcesses INT
SET @NumProcesses = 20 -- Change this to be the number of processes over
the time period you want

DECLARE @start INT, @end INT
DECLARE @NumDays INT

--
-- Change the dates below to get your date range
--

SET @NumDays = 1
SELECT @end = DATEDIFF(s, '19700101', GETUTCDATE()) -- GETUTCDATE() is
"now", so change this for the end date.. ie '1-Jan-2013'
SELECT @start = @end - (60 * 60 * 24 * @NumDays)

--
-- This table is used for the extracted information
--

CREATE TABLE #Data (id INT IDENTITY (1, 1), BiasedTime NVARCHAR(20),
DataDetail NVARCHAR(MAX))
INSERT #Data (BiasedTime, DataDetail)
SELECT CONVERT(NVARCHAR(20), BiasedTime), COALESCE(Detaillong,
Detailshort)
FROM dbo.ArchiveData WITH (NOLOCK)
WHERE DataID = @dataid
AND BiasedTime BETWEEN @start AND @end
ORDER BY BiasedTime

DECLARE @Detail NVARCHAR(MAX), @BiasedTime INT

--
-- This table is used for the extracted output
--

CREATE TABLE #Final (ProcessName NVARCHAR(256), CPUUtilPercent FLOAT,
BiasedTime INT)

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

--
-- Loop through what was extracted
--

WHILE @ctr <= @max BEGIN
SELECT @BiasedTime = BiasedTime, @Detail = N'<ROOT>' + DataDetail +
N'</ROOT>' -- Tag the ROOT stuff on for the xml processing
FROM #Data
WHERE id = @ctr

DECLARE @idoc INT
EXEC sp_xml_preparedocument @idoc OUTPUT, @Detail

INSERT #Final (ProcessName, CPUUtilPercent, BiasedTime)
SELECT C0, C2, @BiasedTime
FROM OPENXML(@idoc, '/ROOT/R', 2)
WITH (C0 NVARCHAR(256), C2 FLOAT) -- In this stream C0 is the proces
name and C2 the utilisation. C1 is PID if needed

EXEC sp_xml_removedocument @idoc

SET @ctr = @ctr + 1
END

SELECT TOP (@NumProcesses) ProcessName, CPUUtilPercent, LocalTime =
DATEADD(s, BiasedTime, '19700101')
FROM #Final
WHERE UPPER(ProcessName) != 'TOTAL'
ORDER BY CPUUtilPercent DESC

DROP TABLE #Data
DROP TABLE #Final


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

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.