April 3, 2014 at 11:51 pm
I have got a dataset which is pulling monitoring counter values for logical drives, ram and cpu for multiple machines. From this I have created a table which groups the counters by machine and then gives the max, min and average for each counter instance per machine and then highlights any over 80%
What I now need to do is perform a count of how many machines have a counter which is over 80% and I am going around in circles trying to figure this out. I need something along the lines of: for each machine (if counter instance has avg value > 80, True) Count true
April 4, 2014 at 1:06 am
What is your data source?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 7, 2014 at 12:25 am
Its an sql database
April 7, 2014 at 12:27 am
This is something you can easily add in the source query with a CASE statement.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 8, 2014 at 12:59 am
I have got:
SELECT
viewReportMonitorCounterLog.agentGuid
,viewReportMonitorCounterLog.ComputerName
,viewReportMonitorCounterLog.GroupName
,viewReportMonitorCounterLog.[Counter Object]
,viewReportMonitorCounterLog.Counter
,viewReportMonitorCounterLog.[Counter Instance]
,"Critical Utilisation" = CASE
WHEN viewReportMonitorCounterLog.[Value] >= 80 THEN 1
WHEN viewReportMonitorCounterLog.[Value] < 80 THEN 0
END
,viewReportMonitorCounterLog.[Value]
,viewReportMonitorCounterLog.[Time(UTC)]
FROM
viewReportMonitorCounterLog
Should it possible in the case statement to use AVG(viewReportMonitorCounterLog.[Value]) >= 80 THEN 1 etc? I set that and then I was getting the following error:
TITLE: Microsoft SQL Server Report Builder
------------------------------
An error occurred while executing the query.
Column 'viewReportMonitorCounterLog.agentGuid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
April 8, 2014 at 1:04 am
If you want to calculate aggregates in your query, you need to use a GROUP BY clause.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 8, 2014 at 2:22 am
Where would the Group By statement go?
April 8, 2014 at 2:33 am
I was rereading your original question, and I need some more info.
You said first you would like to have a count of how many machines have a value over 80%. (What is a machine btw, is it viewReportMonitorCounterLog.ComputerName?)
Buy you also want to calculate the average. Over all the machines? Or over viewReportMonitorCounterLog.[Counter Instance]? Does [Counter Instance] have multiple rows in the data set?
Maybe you can give some sample data and your desired output.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 8, 2014 at 3:22 am
Yes a machine is viewReportMonitorCounterLog.ComputerName.
The avg needs to be calculated per counter instance per machine - ie avg of c drive values for machine 1, avg of c drive values for machine 2 etc and if any counter instance average is over 80 on one machine that machine is then counted, if no counter instances average above 80, this machine is ignored.
For example:
Machine Counter Instance Avg Value
Server 1 c: 65.27
Server 1 E: 84.64
Server 2 C: 37.27
Server 2 D: 57.00
Server 2 E: 55.61
Server 3 C: 20.00
Server 3 E: 46.89
I'm trying to get an output which is just 1 row in a table:
'Number of Machines Exceeding Critical Utilisation Values' '1'
April 9, 2014 at 1:59 am
Sorry for the late reply, I have been busy.
Here is how I would solve it using TSQL:
-- create temp table
CREATE TABLE #viewReportMonitorCounterLog
([Machine] VARCHAR(10) NOT NULL
,[Counter Instance] VARCHAR(10) NOT NULL
,[Counter Object] VARCHAR(10) NOT NULL
,[Value] NUMERIC(4,2) NOT NULL
);
-- populate temp table with sample values
INSERT INTO #viewReportMonitorCounterLog([Machine],[Counter Instance],[Counter Object],[Value])
VALUES ('Server 1','C:','A',70.00)
,('Server 1','C:','B',61.87)
,('Server 1','E:','A',84.64)
,('Server 2','C:','A',37.27)
,('Server 2','D:','A',57.00)
,('Server 2','E:','A',55.61)
,('Server 3','C:','A',20.00)
,('Server 3','E:','A',46.89);
--SELECT * FROM #viewReportMonitorCounterLog;
-- calculate average values per counter instance
SELECT
[Machine]
,[Counter Instance]
,[Avg Value] = AVG([Value])
FROM #viewReportMonitorCounterLog
GROUP BY [Machine], [Counter Instance];
-- calculate critical utilisation
WITH cte_AvgValue AS
(
SELECT
[Machine]
,[Counter Instance]
,[Avg Value] = AVG([Value])
FROM #viewReportMonitorCounterLog
GROUP BY [Machine], [Counter Instance]
) -- same select as before
,cte_Criticals AS
(
SELECT [Machine], MaxValue = MAX([Avg Value]) -- get the highest average value for the current machine
FROM cte_AvgValue
GROUP BY [Machine]
HAVING MAX([Avg Value]) >= 80.00 -- only keep the machines that are critical
)
SELECT [Number of Machines Exceeding Critical Utilisation Values] = COUNT(*)
FROM cte_Criticals;
DROP TABLE #viewReportMonitorCounterLog;
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply