Geneos - Database - How to get a report of how many critical and warning alerts my gateway(s) are generating?

Geneos does not have such a report as standard, but (assuming you have enabled database logging) the events are logged to the database.  It is therefore possible to write a SQL statement to retrieve this information and push it towards any tool that can receive the SQL output (Excel, for example).  More details on how to do this are listed below.

Note that this is not the same as taking the figures from the gateway-severity plugin, as that only shows current totals and does not differentiate between alerts that day and those not cleared from previous days. These figures are not logged to the database either.

The SQL statement below can be run on the event_table in the Geneos database (replacing the contents between the angle brackets for your own requirements.

MySQL Syntax

SELECT DATABASE(),DATE(FROM_UNIXTIME(timestamp)) as date, 
SUM(CASE WHEN severity=2 THEN 1 ELSE 0 END) as CritVol, 
SUM(CASE WHEN severity=1 THEN 1 ELSE 0 END) as WarnVol 
FROM event_table 
WHERE timestamp >= UNIX_TIMESTAMP('<start_date>') AND timestamp <= UNIX_TIMESTAMP('<end_date>') 
GROUP BY date;

If you have multiple gateways that log to different databases, then you can use a script on the command line to aggregate the results, something of the nature:

#!/bin/bash
set -x
### USE THIS IF LIST OF GATEWAY NAMES ARE IN A FILE
filename=/var/tmp/myfile
mysqlDir=/apps/itrs/mysql
while read DBN; do
DATABASE_NAME=$DBN
echo ""
$mysqlDir/bin/mysql
 -u root --socket=$mysqlDir/data/mysql.sock -B -e "use $DATABASE_NAME; 
SELECT DATABASE(),DATE(FROM_UNIXTIME(timestamp)) as date, SUM(CASE WHEN 
severity=2 THEN 1 ELSE 0 END) as CritVol, SUM(CASE WHEN severity=1 THEN 1
 ELSE 0 END) as WarnVol FROM event_table WHERE timestamp >= 
UNIX_TIMESTAMP('<start_date>') AND timestamp < 
UNIX_TIMESTAMP('<end_date>') GROUP BY date;"
echo ""
done < $filename

Note: The SQL query and script above may require adjustments depending on the database server used by the Gateway. The above example is based on MySQL. The Gateway supports the database servers listed here: Database Logging.