Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Report Builder 3.0 Expression count Expand / Collapse
Author
Message
Posted Thursday, April 3, 2014 11:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 1, 2014 10:15 PM
Points: 9, Visits: 21
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
Post #1558370
Posted Friday, April 4, 2014 1:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 13,251, Visits: 11,030
What is your data source?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1558386
Posted Monday, April 7, 2014 12:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 1, 2014 10:15 PM
Points: 9, Visits: 21
Its an sql database
Post #1558926
Posted Monday, April 7, 2014 12:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 13,251, Visits: 11,030
This is something you can easily add in the source query with a CASE statement.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1558928
Posted Tuesday, April 8, 2014 12:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 1, 2014 10:15 PM
Points: 9, Visits: 21
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.

Post #1559378
Posted Tuesday, April 8, 2014 1:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 13,251, Visits: 11,030
If you want to calculate aggregates in your query, you need to use a GROUP BY clause.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1559381
Posted Tuesday, April 8, 2014 2:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 1, 2014 10:15 PM
Points: 9, Visits: 21
Where would the Group By statement go?
Post #1559399
Posted Tuesday, April 8, 2014 2:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 13,251, Visits: 11,030
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1559402
Posted Tuesday, April 8, 2014 3:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 1, 2014 10:15 PM
Points: 9, Visits: 21
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'

Post #1559411
Posted Wednesday, April 9, 2014 1:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 13,251, Visits: 11,030
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;





How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1559819
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse