While onsite today, we were trying to create an Executive Overview report using iReport. Biggest problem we ran into was coming up with a correct SQL query. To keep things simple, we want to display the number of critical AND major alarms per hostname on a graph. Using the NAS_ALARMS table seems like the way to go. We were able to show the number of critical alarms per hostname by using this statement:
select hostname, count(nimid) from nas_alarms where severity='critical' group by hostname
Just couldn't figure out how to incorporate major alarms in there as well. We tried aliases, joins, and unions but our sql statements just wouldn't give us the right output. I'm thinking we'd want three columns as the output when you run the sql statment, one for hostname, one for number of critical alarms, and one for major alarms? Hopefully someone can figure it out pretty quickly and post a working SQL statment.