I am looking to create a SQL query that will return specific averages of data from a collection of Servers. Here are the specifics:
Via a 3rd party monitoring agent, each Server will write certain performance measures into a table in SQL called Server_Measures
. These measures for each server will be recorded every 10 minutes. Each server is assigned 5 measures to monitor and report every 10 minutes.
In this test example, there are a total of 3 servers.
What is needed is to create a SQL Query that will output the average value of each measure for each server by a user-defined time period. (i.e. 7 days,. 14 days 30 days......etc) The report should also have the flexibility to allow the user to define the average calculation by each individual measure for all server OR average calculation ALL measures for all servers.
Lastly, the data in the table Server_Measures
does not contain the actual Name of the Server Or Measure but references this via a GUID. The actual Name of the Server and Measure will need to be referenced by a JOIN with the table AllItems
which contains the GUID to NAME translations.
Any guidance on this request would be appreciated. I am having problem with the logic in my query build attempts.
Here is a graphic of the tables used with data for clarity: