?
Solved

Request for SQL Query that will calculate specific averages.

Posted on 2012-08-13
7
Medium Priority
?
583 Views
Last Modified: 2012-08-27
Hello,

 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:

Table examples to be used in query build specs


Thank You
0
Comment
Question by:Charlie_Melega
7 Comments
 
LVL 12

Accepted Solution

by:
Habib Pourfard earned 1400 total points
ID: 38289297
hope this helps:
DECLARE @ServerName varchar(64) = 'All',
        @Measure    varchar(64) = 'All'

SELECT AI1.Name AS ServerName, AI2.Name As [Measure], AVG(SM.Measure_value) AS Average
FROM ServerMeasures SM 
INNER JOIN AllItems AI1 ON SM.Server_guid = AI1.Guid
INNER JOIN AllItems AI2 ON SM.Measure_value = AI2.Guid
WHERE (@ServerName = 'All' OR AI1.Name = @ServerName) 
AND   (@Measure = 'All' OR AI2.Name = @Measure)
--AND   (YourDateField Between @StartDate AND @EndDate)
GROUP BY AI1.Name, AI2.Name

Open in new window

0
 

Author Comment

by:Charlie_Melega
ID: 38289480
Hello Pourfard,

Thanks for the quick reply. I attempted to run your query but since I have SQL 2005 I needed to change the DECLARE statement to add SET. Afterwards,  it appears the Avg function is having a problem with the column data type definition. I receive the error

Operand data type varchar is invalid for avg operator.

I am continuing to test this but would appreciate any thoghts you may have.

Thank You

Curent query I am using;

DECLARE @ServerName VarChar(255)
SET @ServerName = 'All'

DECLARE @Measure BigInt
SET @Measure = 'All'

SELECT AI1.Name AS ServerName, AI2.Name As [Measure], AVG(SM.Measure_value) AS Average
FROM Server_Measures SM
INNER JOIN AllItems AI1 ON SM.Server_guid = AI1.Guid
INNER JOIN AllItems AI2 ON SM.Measure_value = AI2.Guid
WHERE (@ServerName = 'All' OR AI1.Name = @ServerName)
AND   (@Measure = 'All' OR AI2.Name = @Measure)
--AND   (YourDateField Between @StartDate AND @EndDate)
GROUP BY AI1.Name, AI2.Name
0
 

Author Comment

by:Charlie_Melega
ID: 38289822
I think my problem is that I am trying to run this query under  SQL 2005 and running into SLQ syntax issues between 2008 and 2005 queries. Also, my data type definitions might be incorrect for the query you have provided:

Server_Measures

Time_Stamp  varchar (255)
Server_Guid  bigint
Measure_Guid bigint
Measure_Value  decimal


AllItems

Guid  bigint
Name  varchar (255)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38290370
I suspect you just need to change the following (no points please):

DECLARE @ServerName VarChar(255)
SET @ServerName = 'All'

DECLARE @Measure BigInt
SET @Measure = 'All'

SELECT AI1.Name AS ServerName, AI2.Name As [Measure], AVG(SM.Measure_value) AS Average
FROM Server_Measures SM
INNER JOIN AllItems AI1 ON SM.Server_Guid = AI1.Guid
INNER JOIN AllItems AI2 ON SM.Measure_Guid = AI2.Guid
WHERE (@ServerName = 'All' OR AI1.Name = @ServerName)
AND   (@Measure = 'All' OR AI2.Name = @Measure)
--AND   (YourDateField Between @StartDate AND @EndDate)
GROUP BY AI1.Name, AI2.Name
0
 

Author Comment

by:Charlie_Melega
ID: 38290448
Thanks for the assistance scperkins. I continue to receive the data type error. I am continuing testing.

bigint error
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 600 total points
ID: 38290466
Good point.  It should be:

DECLARE @ServerName VarChar(255)
SET @ServerName = 'All'

DECLARE @Measure varchar(255)
SET @Measure = 'All'

SELECT AI1.Name AS ServerName, AI2.Name As [Measure], AVG(SM.Measure_value) AS Average
FROM Server_Measures SM
INNER JOIN AllItems AI1 ON SM.Server_Guid = AI1.Guid
INNER JOIN AllItems AI2 ON SM.Measure_Guid = AI2.Guid
WHERE (@ServerName = 'All' OR AI1.Name = @ServerName)
AND   (@Measure = 'All' OR AI2.Name = @Measure)
--AND   (YourDateField Between @StartDate AND @EndDate)
GROUP BY AI1.Name, AI2.Name
0
 
LVL 9

Expert Comment

by:keyu
ID: 38290851
hi try this might helps you..

syntax might vary this is just an idea


Select avg(Measure_Value) as avgmeasure,name from Server_Measures,AllItems  where  Server_Measures.server_guid=AllItems .guid group by name
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question