Good evening ladies and gents,
I have been working on a piece of SQL that accurately reports on Performance Data lately, only to find it is limited by a few things.
First up, the background information:
1. Performance Data is only stored for a certain number of days2. You can increase the number of days safely, we do up to 30 days3. Carefully plan how frequently you want to collect data - read this to analyse how many database writes a day you do off how frequently you collect X how many performance counters are applied. We collect every 15 minutes, across 200 servers, with 20 performance counters on each with no negative impact the VSA .
Now to my question, one of the performance counters is "System Uptime"
The Kaseya reporting always gets this wrong, i haven't seen it accurate pretty much ever, so i am trying to get it done myself.
Given the above limitations, consider this SQL
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.monitorCounterLog20120221.counterValue AS CounterValue,ksubscribers.dbo.monitorCounterLog20120221.eventDateTime AS EventTimeFROM ksubscribers.dbo.monitorCounterLog20120221 INNER JOINksubscribers.dbo.monitorCounter ONksubscribers.dbo.monitorCounterLog20120221.monitorCounterId = ksubscribers.dbo.monitorCounter.monitorCounterId INNER JOINksubscribers.dbo.vAgentConfiguration ONksubscribers.dbo.monitorCounterLog20120221.agentGuid = ksubscribers.dbo.vAgentConfiguration.agentGuidwhere name = 'System Uptime'and machName = 'admin-main'ORDER BY MachineName, CounterName, CounterDescription, EventTime
This pulls Uptime data for 1 day. What i want to know, and what i have considered is this.
If i use SQL Management Studio, i can copy and paste that query 30 times into one "Super Query". The problem is, it outputs to a grid 30 times, so then, i have to save each grid to a CSV File, open each CSV file, and put the data into one file and THEN i can get the true uptime, manually. To achieve accurate uptime i'd have to....
1 Find the total available hours in a month (111,600 in a 31 day month)2 Get the up time for each server, assuming the servers rebooted at the same time each month (we know it doesn't so this is impossible), every time the data is 0, count until the next 0 minus 13 Get each value before each 0 (each server reboot), add them 4 times, total them, convert to minutes, then divide by 111,600.
See how complicated this is?
Have i over complicated it?
Is there a way i can automate SQL to do a subquery of every "MonitorCounterLog" available in a given period?
Any feedback on how you do server uptime reporting would be ace!
Maybe i missed the point and there is a simple way of doing it?
what do you mean the system uptime is wrong. we have a system uptime counter and it' 100% correct i just checked it 2 seconds ago
BY THE WAY what you want to do is use a UNION in your sql so you only end up with one veiw
I have never had the system up time in the Exec Summary report correct, in fact, half the details in the Exec Summary report have been inaccurate.
Could you go through exactly how you get your up time to report correctly? - do you do anything different to the exec summary report?
Cheers for the union thing - i got it working amazing.
I now have queries set up that will fire off each month, for the last 30 days of performance data, Customers are going to love this.
Unless someone has a better way of doing it, i will upload a zipped up directory of how i have set it up - when i finish the project.
What i have done for monthly performance averages is:
1. One Folder containing sub folders for each month of the year.
2. In each sub folder is an excel document with the connections to our database - and the unique monthly queries to get performance data.
3. At the end of every month i simply open that excel document, run the queries, and copy and paste the data i want into our reports (pivot and get pretty pictures
It'll make sense when i post what i am seeing / doing to the community i promise.
i just ran a exec report on a server that i know has been up all the time . cos i went to the command line and checked and it reported in the exec sum correctly