I am looking for a query that will show all performance counter metrics for all machines across a period of time.
The problem i can see with all of this is that there are numerous disparate tables in the database that hold the information, but joining that information is hard.
At the moment, i can see tables that log all performance data for a single day, this is fine as i can use it to practice my analytics.
So far this is the query i have
select * from ksubscribers.dbo.monitorCounterLog20120208join ksubscribers.dbo.monitorCounteron ksubscribers.dbo.monitorCounterLog20120208.monitorCounterLogId = ksubscribers.dbo.monitorCounter.monitorCounterIdinner join ksubscribers.dbo.vAgentConfigurationon ksubscribers.dbo.vAgentConfiguration.agentGuid = ksubscribers.dbo.monitorCounterLog20120208.agentGuid
The logic of the query is:
1. Select everything from Monitorcounterlog (a daily collection of performance data). This table has to be joined with the MonitorCounter table to get the name of the performance counter and the log data.
2. Also do a join on the vAgentConfiguration on AgentGuid to get the name of the machine matched up against the machine logged in the Monitorcounterlog table.
It returns every row but because of the joins - some of the columns are full of NULLS - the important ones - the performance counter names.
My suspicion is with that query is that the join is the wrong way around, or, more likely, i have gotten the relationships completely wrong. I thought you could join three tables like i did, where you can join the tables on different columns if they matched on each join (a.monitorcounterlog = b.monitorcounterlog & a.agentguid = b.agentguid).
Is there a SQL gun out there that could see about making this query work?
Has anyone done this so i don't have to keep toiling away? Eager to hear the communities thoughts.
Your query works fine for me as is. Have you checked to see that you have valid data in all three tables?
Hi Zippo - sorry to clarify
All those NULLS are what i am trying to avoid, they should have the names and data of the performance counter metrics (name - type - etc)
I don't know if the data is valid across all three tables
MonitorCounterLog and MonitorCounter have MonitorCounterID in common, MonitorCounterLog and vAgentConfiguration have AgentGuid in common.
So maybe it's just i'm using the wrong tables. The query executes, but the results aren't what i was after. Hope this clarifies.
The query executes fine for me and returns data, not nulls, so that's what prompted my question to you.
Ahh sorry again
left join ksubscribers.dbo.monitorCounter
on ksubscribers.dbo.monitorCounterLog20120208.monitorCounterLogId = ksubscribers.dbo.monitorCounter.monitorCounterId
inner join ksubscribers.dbo.vAgentConfiguration
on ksubscribers.dbo.vAgentConfiguration.agentGuid = ksubscribers.dbo.monitorCounterLog20120208.agentGuid
Try that query - the left join gets all the data but NULLS the performance monitor data.
Yes, with the "Left Join" it fails for me too. But try using just a "Join" in place of the "Left Join" and see what you get...
It turns out i wasn't querying data consistant across the tables - i needed monitorcounter tables with agent guids. BUT, it is still not doing what i wanted.
on ksubscribers.dbo.monitorCounterLog20120208.monitorCounterId = ksubscribers.dbo.monitorCounter.monitorCounterId
on ksubscribers.dbo.monitorDeploymentSummary.MonitorSetID = ksubscribers.dbo.monitorCounter.monitorSetId
That query - returns everything , but i think it produces a cartesian product (www.fluffycat.com/.../Cartesian-Joins)
It got to 5 million rows and kept climbing - so i stopped it - if you want to test this be EXTREMELY careful, however your opinions would be appreciated.
I now believe i just have the join syntax right but the data is correct.
Any ideas from SQL Query joining experts? Any ideas Zippo?
That'll take me a bit longer to parse - someone else will probably be quicker at it than I am...
I got it! This works a treat!
select *from ksubscribers.dbo.monitorCounterLog20120208inner join ksubscribers.dbo.monitorCounteron ksubscribers.dbo.monitorCounterLog20120208.monitorCounterId = ksubscribers.dbo.monitorCounter.monitorCounterIdinner join ksubscribers.dbo.vAgentConfigurationon ksubscribers.dbo.monitorCounterLog20120208.agentGuid = ksubscribers.dbo.vAgentConfiguration.agentGuidorder by ksubscribers.dbo.vAgentConfiguration.machName
This will give you the entire days performance data for all servers - with 180 servers, it returns 60 Megabytes of data, i am going to work on the column filtering and look at creating a stored procedure that runs each day and pumps all this data into one big table. It is going to be amazing having a full years data of performance stuff to look at! Just have to get an understanding of how the business intelligence stuff works out - how do i query a table with 24Gigabytes of data into it!
People reading this thread from here on in. Use this query and play with it - where Monitorcounterlog20120208 needs to be changed to reflect a date you want to query.
This query is also a brilliant way to tell how efficient your performance counter metrics are.
Last one here i promise.
SELECT ksubscribers.dbo.vAgentConfiguration.machName AS MachineName, ksubscribers.dbo.vAgentConfiguration.groupName AS GroupName, ksubscribers.dbo.monitorCounter.name AS CounterName, ksubscribers.dbo.monitorCounter.description AS CounterDescription, ksubscribers.dbo.monitorCounterLog20120208.counterValue AS CounterValue, ksubscribers.dbo.monitorCounterLog20120208.eventDateTime AS EventTimeFROM ksubscribers.dbo.monitorCounterLog20120208 INNER JOIN ksubscribers.dbo.monitorCounter ON ksubscribers.dbo.monitorCounterLog20120208.monitorCounterId = ksubscribers.dbo.monitorCounter.monitorCounterId INNER JOIN ksubscribers.dbo.vAgentConfiguration ON ksubscribers.dbo.monitorCounterLog20120208.agentGuid = ksubscribers.dbo.vAgentConfiguration.agentGuidORDER BY MachineName, CounterName, CounterDescription
Interesting work, Mark. Thanks for posting the query.
Thank you Mark for your queries.
Anyone knows how to aggregate the info from all these monitorCounterLogyyyyddmm for the last n days?
Say for a week or a month.
I was trying to pull some data for %Processor Time (and I found IDs) but stuck with creating dataset which will pull data for a period of time.
monitorCounter.monitorCounterId = '1201'
--monitorcounterids for Processor time 1201, 1219, 1228
DECLARE @query VARCHAR(MAX)
SELECT @query = COALESCE(@query + ' UNION ALL ', '') + 'SELECT monitorCounterId, AVG(counterValue) AS AvgCounterValue, CAST(MIN(eventDateTime) AS DATE) AS eventDate FROM ' + TABLE_NAME + ' WHERE monitorCounterId = 1201 GROUP BY monitorCounterId'
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME LIKE 'monitorCounterLog%'
AND NOT (TABLE_NAME LIKE 'monitorCounterLog'
OR TABLE_NAME LIKE 'monitorCounterLogSummary')
That will output the Average counter value per day for a given counter for every day you have a log table. You can output that into a temp table and query for a specific day if required. It should get you close if this isn't exactly what you want.
Thank you for your prompt response.
is there any way to pull this info like on the screen attached so we could have datapoints with values for counter and server. (this is an example for querying data for 1 day )
Not sure if this is considered a contribution, however, i'm fairly certain if you're on 9.0 or above, there is a rollup of the dates in one table for performance counters. I'll have a look for it later but it'll be in a view somewhere, you don't need to do joins on counter logs. I have since moved right away from such complicated queries. My best suggestion is to reach out to the guys at BrightGauge and use even the most basic of reporting to get you by.