Does anyone have some SQL queries they use for reports?

[danrog]

I am a newbie when it comes to complex SQL queries. Does anyone have some SQL queries/scripts they use to generate more complex reports then whats offered in 1.7.1? I am looking for something that will give me:

"Top/Bottom X Hosts by Mem Usage over the last X days/weeks/months"
"Top/Bottom X Hosts by CPU Load Avg over the last X...."
And maybe if the Item values support it
"Top/Bottom X Hosts by {ITEMID} over the last X...."

If not, then could someone start me off with the calculation and comparison I would have to do?

Thanks in advance.

[nelsonab]

You might be in for an interesting adventure with regards to the queries you'll use. The trick is to unravel the data points from a serial stream into a table format which you can then query more easilly.

Feel free to check out the Bandwidth reports I created using some Ruby foo and some crazy Mysql Queries at

http://trac.red-tux.net

[danrog]

Nelsonab, I looked are your bandwidth report (very nice by the way) before posting (and used some of what I found in there). I was having a tough time with the SQL syntax itself, which prompted me to post. I decided to spend some time today and read some MySQL docs and I came up with this.

This is my first pass at SQL code, so feel free to tell me that there are better or faster ways to do this. I verified that this query and the graphs match up so I think this is correct. Now that I have this I am going to wrap it into a php script and possibly add some graphs :)


SELECT h.itemid, avg(h.value) AS avg, hs.host FROM history h
LEFT JOIN items i ON h.itemid = i.itemid
LEFT JOIN hosts hs ON i.hostid = hs.hostid
WHERE i.description like 'Processor Load'
AND h.clock>='2009-11-06'
AND h.clock<=unix_timestamp(timestampadd(day,'15','2009-11-06'))
GROUP BY h.itemid
ORDER BY avg DESC
LIMIT 10;

[exkg]

Hi,


I thibk that BarGraphs Reports (new in 1.8) can help.
It's workink like a charm.


[]s,
Luciano