Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

HELP with the sql Query on report writer

I'm not an expert in SQL ,i need some help with the sql query at Solarwinds Report Writer ... Actually , i want to generate a report with this exact informations ( IP_Address, CPULoad, Last Boot , and Availibility )

The problem is that , th CPU Load and last boot are in the same sql query , but when i want to add the AVAILIBILITY chart , i can't because it's in another TYPE of report ...

PLEASe i Need some HELP . if someone could show me how to create an sql query to preview all this informations in only one chart .

Find more posts tagged with

Accepted answers

zackm

stuartwhyte is correct; this will be a very expensive report in regards to cycles on your SQL box.

In your other question about this (Re: How to display polling Engine as well as Other node information?) you mentioned you wanted PEAK CPU, This will get you the PEAK CPU load from any one polling cycle out of the last 7 days, along with the AVG Availability from all polling cycles over the same time period:

select  n.nodeid  ,n.caption as 'DEVICE'  ,n.ip_address as 'IP ADDRESS'  ,n.lastboot as 'LAST BOOT'  ,max(c.maxload) as 'PEAK CPU'  ,avg(r.availability) as 'AVG AVAILABILITY'from nodes njoin cpuload c on n.nodeid=c.nodeidjoin responsetime r on n.nodeid=r.nodeidwhere c.datetime > (getdate() -7)and r.datetime > (getdate() -7)and n.ip_address in ('1.1.1.1','1.1.1.2','1.1.1.3')group by n.nodeid, n.caption, n.ip_address, n.lastboot

I would warn you though, looking at only 8 devices took almost 30 seconds on my lab machine that is a fairly standard SQL build. I wouldn't push this one too hard with more than 15-20 devices if possible. However, you can always cross your fingers and try

If you want the AVG CPU load from all polling cycles and the AVG Availability from all polling cycles over the last 7 days, use this one (which surprisingly took only 1 second more to complete):

select  n.nodeid  ,n.caption as 'DEVICE'  ,n.ip_address as 'IP ADDRESS'  ,n.lastboot as 'LAST BOOT'  ,avg(c.avgload) as 'AVG CPU'  ,avg(r.availability) as 'AVG AVAILABILITY'from nodes njoin cpuload c on n.nodeid=c.nodeidjoin responsetime r on n.nodeid=r.nodeidwhere c.datetime > (getdate() -7)and r.datetime > (getdate() -7)and n.ip_address in ('1.1.1.1','1.1.1.2','1.1.1.3')group by n.nodeid, n.caption, n.ip_address, n.lastboot

Obviously, for your environment, you would replace the IP Addresses from the line below to the ones you are interested in. The syntax is ('ip address','ip address','etc','etc')

and n.ip_address in ('1.1.1.1','1.1.1.2','1.1.1.3')

Hope this helps, I would be interested to hear how many IP Addresses you run against and how long the report takes to generate.

-ZackM

Loop1 Systems: SolarWinds Training and Professional Services

LinkedIN: Loop1 Systems
Facebook: Loop1 Systems
Twitter: @Loop1Systems

All comments

stuartwhyte

Hi,

For CPU and availability - over what time frame are you looking for? I expect you are looking for the average over this timeframe?

Stuart

aimy.k

Hi
yes i'm looking for the average

stuartwhyte

Having two averages from two different tables will make this an expensive query (particularly as Availability is quite expensive in itself), which will probably timeout.

Let me have a look if I can make this work for you...

aimy.k

Ok , thank you So much , i appreciate it

zackm

stuartwhyte is correct; this will be a very expensive report in regards to cycles on your SQL box.

select  n.nodeid  ,n.caption as 'DEVICE'  ,n.ip_address as 'IP ADDRESS'  ,n.lastboot as 'LAST BOOT'  ,max(c.maxload) as 'PEAK CPU'  ,avg(r.availability) as 'AVG AVAILABILITY'from nodes njoin cpuload c on n.nodeid=c.nodeidjoin responsetime r on n.nodeid=r.nodeidwhere c.datetime > (getdate() -7)and r.datetime > (getdate() -7)and n.ip_address in ('1.1.1.1','1.1.1.2','1.1.1.3')group by n.nodeid, n.caption, n.ip_address, n.lastboot

If you want the AVG CPU load from all polling cycles and the AVG Availability from all polling cycles over the last 7 days, use this one (which surprisingly took only 1 second more to complete):

select  n.nodeid  ,n.caption as 'DEVICE'  ,n.ip_address as 'IP ADDRESS'  ,n.lastboot as 'LAST BOOT'  ,avg(c.avgload) as 'AVG CPU'  ,avg(r.availability) as 'AVG AVAILABILITY'from nodes njoin cpuload c on n.nodeid=c.nodeidjoin responsetime r on n.nodeid=r.nodeidwhere c.datetime > (getdate() -7)and r.datetime > (getdate() -7)and n.ip_address in ('1.1.1.1','1.1.1.2','1.1.1.3')group by n.nodeid, n.caption, n.ip_address, n.lastboot

Obviously, for your environment, you would replace the IP Addresses from the line below to the ones you are interested in. The syntax is ('ip address','ip address','etc','etc')

and n.ip_address in ('1.1.1.1','1.1.1.2','1.1.1.3')

Hope this helps, I would be interested to hear how many IP Addresses you run against and how long the report takes to generate.

-ZackM

Loop1 Systems: SolarWinds Training and Professional Services

LinkedIN: Loop1 Systems
Facebook: Loop1 Systems
Twitter: @Loop1Systems

aimy.k

Thank you so much for the help, i'm actually looking for less than 20devices, so i guess it's not too much i'm gonna try it

thank youu

aimy.k

Hi,

i tried it with a list of only 15 IP Addresses and it worked , it took me 25 seconds to generate the repport ,

the chart contains this informations:

*Nodes.NodeID

*Nodes.IP_addresse

*Nodes.Caption

*CPULoad.TotalMemory

*CPULoad.AvgLoad

*CPULoad.MAXLoad

*CPULoad.AvgPercentMemoryUsed

*ResponseTime.availability

*Nodes.LastBoot

*Nodes.ResponseTime

Again , Thank you for your help

rstoney00

Zack is right,that will be somewhat expensive query. You could cut down the time/cost a little using a custom property. For example, a custom node property of "IncludeInReportX"

Then instead of using the n.ip_address in (<ip addresses list>) you can use n.IncludeInReportX = '1'