| Author |
Topic |
|
Nathan.Young
Starting Member
9 Posts |
 Posted - 2010-06-22 : 10:06:23
|
Hello, all. I have a SQL query designed to fetch data on the CPU utilization of a specific server over a period of time. We have a system that automatically polls the server every five minutes and records the CPU usage, among other things, in a database. My query is a simple report, designed to summarize the data by Company Name, Node Name, CPU Average, and CPU Maximum. My coworker for whom I made this requested that I attempt to find a way to have a single SQL query return that same data for all of our servers, with each server being on its own row. I don't know how to do this, or if it is even possible.I'm newish to SQL, so I don't know if I've provided enough information to help you all help me, so I will be checking back frequently today to see if anyone has anything to ask me. I appreciate the help you may choose to offer me. Thanks.Nathan Young=================================================SELECT TOP 10000 Nodes.Caption AS NodeName,CustomNodePollers_CustomPollers.UniqueName AS Poller_Name,AVG(CONVERT(INT, CustomNodePollerStatistics_CustomPollerStatistics.Status)) AS CPU_Average,MAX(CONVERT(INT, CustomNodePollerStatistics_CustomPollerStatistics.Status)) AS CPU_MaximumFROM ( (Nodes INNER JOIN CustomPollerAssignment CustomNodePollerAssignment_CustomPollerAssignment ON (Nodes.NodeID = CustomNodePollerAssignment_CustomPollerAssignment.NodeID) ) INNER JOIN CustomPollerStatistics CustomNodePollerStatistics_CustomPollerStatistics ON (CustomNodePollerAssignment_CustomPollerAssignment.CustomPollerAssignmentID = CustomNodePollerStatistics_CustomPollerStatistics.CustomPollerAssignmentID))INNER JOIN CustomPollers CustomNodePollers_CustomPollersON (CustomNodePollerAssignment_CustomPollerAssignment.CustomPollerID = CustomNodePollers_CustomPollers.CustomPollerID)WHERE (DateTime BETWEEN 40297 AND 40327.9999884259)AND ( (Nodes.CustomerName = 'CUSTOMER_NAME_HERE') AND (CustomNodePollers_CustomPollers.UniqueName = 'hrProcessorLoad') AND (Nodes.Caption = 'USUALLY_THE_MACHINE_NAME'))AND(CustomNodePollerAssignment_CustomPollerAssignment.InterfaceID = 0)GROUP BY Nodes.Caption, CustomNodePollers_CustomPollers.UniqueName =================================================As a side-note, I do a datatype cast because the data I'm working with is of type varchar and I need to run an average on it. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-06-22 : 10:37:59
|
| seems like what you need is a ssis package with for each loop which loops over each server and execute this query on each and populate results to table. you can then query this table after loop to get data for all servers------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Nathan.Young
Starting Member
9 Posts |
Posted - 2010-06-22 : 10:49:55
|
quote:
Originally posted by visakh16
seems like what you need is a ssis package with for each loop which loops over each server and execute this query on each and populate results to table. you can then query this table after loop to get data for all servers
Alright. Thanks for the lead. I'll start doing research with that in mind. Granted, you're probably right, but I can't yet say whether I know if this will suit our needs, as I don't know much about SQL at all. It'll take me some time to do some reading before I might have more questions to ask. If you don't mind checking back on this post in a bit, or even giving me some more information on ssis, or perhaps some powerful resources that I can use while looking into this, I'd be most appreciative. |
|
|
|
Nathan.Young
Starting Member
9 Posts |
Posted - 2010-06-22 : 11:01:37
|
| From just a few minutes of poking around, it seems that SSIS requires more than just SQL statements to function; like maybe some kind of .net? Am I mistaken in thinking this? We use a custom reporting platform that doesn't appear to accept anything more than basic SQL statements. Is there a way to accomplish my goal in a less elegant way using purely SQL statements? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-06-22 : 11:12:38
|
| Does the system that collects data put it all into a single database? If so, the query shouldn't be too hard to come up with. Can you post some DDL and DML? Here is a link that might be of help to you for posting information so it'll be easier for us to write queries against it:http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
Nathan.Young
Starting Member
9 Posts |
Posted - 2010-06-22 : 11:18:35
|
quote:
Originally posted by Lamprey
Does the system that collects data put it all into a single database? If so, the query shouldn't be too hard to come up with. Can you post some DDL and DML? Here is a link that might be of help to you for posting information so it'll be easier for us to write queries against it:http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Yes, we have a polling machine that polls each server once every 5 minutes and store this information in a single database. We determine what data goes where using the Nodes.Caption field, which is essentially the name of the Network Node, usually a hostname.Give me a brief moment to figure out what DDL and DML means, and then get that information to you. I actually just got into SQL for my company the other day when I used a little bit of intuition to ghetto-rig a quick and dirty solution to a problem. The higher-ups seem to think that even though I don't know SQL, that I can figure it out, regardless. Bear with my inexperience as I try to get this information. |
|
|
|
Nathan.Young
Starting Member
9 Posts |
Posted - 2010-06-22 : 11:30:54
|
| The system that we use to index the data is a proprietary system; we don't have access to the exact DDL that it uses. However, I do believe that I can give you some info on the actual table naming conventions that it uses, through which you might be able to infer the information you need. Will that work? |
|
|
|
Nathan.Young
Starting Member
9 Posts |
Posted - 2010-06-22 : 11:48:36
|
quote:
Originally posted by Lamprey
Can you post some DDL and DML?
Can I find this information from within Microsoft SQL Server Management Studio? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-06-22 : 12:05:18
|
| Sure. The DDL is the script(s) to create the tables. From Management Studio use the Object Explorer to get to the the tables in your database then, simply, right-click on the table and select Script Table As -> Create To. That will produce the DDL of the table. |
|
|
|
Nathan.Young
Starting Member
9 Posts |
Posted - 2010-06-22 : 12:09:10
|
quote:
Originally posted by Lamprey
Sure. The DDL is the script(s) to create the tables. From Management Studio use the Object Explorer to get to the the tables in your database then, simply, right-click on the table and select Script Table As -> Create To. That will produce the DDL of the table.
One issue is that the tables referenced in the code I posted earlier are not tables. They're views, which makes this really, quite icky. I'm about to step out to lunch. I'll post them in an hour, when I get back. |
|
|
|
Nathan.Young
Starting Member
9 Posts |
Posted - 2010-06-22 : 14:02:45
|
Found a solution. If I omit the line (Nodes.Caption = 'USUALLY_THE_MACHINE_NAME') it gives me exactly what I need. |
|
|
|
|
[SOLVED] Usage reporting, group by row