Need to average availability for a group of nodes

Question

I have all my nodes grouped by the city in which they are located. In an availability report, how can I not only show the individual node availability for a given period of time, but also show the availability of the group of nodes, say in Los Angeles?

What is the SQL for that one? Help greatly appreciated as I am not a SQL expert by any means.

scottd · Answer

patriot, Are you wanting to do this in SolarWinds Report Writer? If so, it will be a challenge because what you are trying to do is the result of 2 select statements. There is no problem running more than one select statement in a query when using SQL. However, I have not been successful in getting Report Writer to handle a more complex query. It can use variables pretty good, but i dont think it was meant to be able to alter, create, drop, or work with variable tables. That's probably a little too much to ask of reporting tool... In your scenario, i would build a query, in SQL, that would select and load the availability data from each server in question into a different table(This table can also be created as part of the query). In the same query, I would select and load overall(summary of the nodes in the 1st select statement) availability data into the same table. The next select statement would query the data in our table(select *) for the results of the previous select statements. If you are using Report Writer you would leave off the last statement(select *) because you would query this table from Report Writer by just simply 'select * from '. Then your data would be returned something like this small 'SAP Nodes availability' example; | 100% | 97.5% | 96.6% | 99.9% SAP Server Summary | 98.5% Obviously, you would need to know your way around the SQL server applications to accomplish this, but by no means would you have to be a SQL pro to do it. This would require you to build as SSIS package in SQL server. If you have access to a DBA that is willing to assist you in person, he/she could accomplish this task quickly for you(this is simple stuff for them) and automate it so it has fresh data as often as you like. Probably not the answer you are looking for, but...

njoylif · Answer

here is a link to my data in/out which rolls everything up into one...

Here is a link to wan utilization count per month where utilization > 45%...should be able to tweak for adding up your data)

Hope that helps

patriot · Answer

Thanks Larry. I'll look into that. Anyone else have an opinion or some SQL expertise?