#!/bin/sh

. /etc/asl/config

MYSQL="/usr/bin/mysql"

echo "Total events in last 24 hours:"
$MYSQL -u$OSSEC_DATABASE_USERNAME -p$OSSEC_DATABASE_PASSWORD $OSSEC_DATABASE -B -e \
	"select count(*) from alert where timestamp > (UNIX_TIMESTAMP() - 86400);"


echo



count=16
while [ $count -gt 0 ]
do
	count=`expr $count - 1`
	echo -n "Total Level $count events:"

	$MYSQL -u$OSSEC_DATABASE_USERNAME -p$OSSEC_DATABASE_PASSWORD $OSSEC_DATABASE -B -e \
  	"select count(alert.rule_id) from alert LEFT JOIN signature on alert.rule_id = signature.rule_id where signature.level = $count and alert.timestamp > (UNIX_TIMESTAMP() - 86400);"
	echo
done								# End of loop



# Web attacks: 60118
echo -n "Web attacks: "
$MYSQL -u$OSSEC_DATABASE_USERNAME -p$OSSEC_DATABASE_PASSWORD $OSSEC_DATABASE -B -e \
	"select count(rule_id) from alert where rule_id = '60118' and alert.timestamp > (UNIX_TIMESTAMP() - 86400);"

# Top 10 Web attackers
## select src_ip from alert where rule_id = '60118' and alert.timestamp > (UNIX_TIMESTAMP() - 86400);
echo -n "Top 10 Attackers: "
$MYSQL -u$OSSEC_DATABASE_USERNAME -p$OSSEC_DATABASE_PASSWORD $OSSEC_DATABASE -B -e \
	"select inet_ntoa(src_ip), count(*) as tmp from alert where rule_id = '60118' and alert.timestamp > (UNIX_TIMESTAMP() - 86400) group by rule_id, src_ip order by tmp desc limit 10;"

# Invalid user attempts
echo -n "Top 10 Invalid User Attempt IP's  in the last 24 hours: "
$MYSQL -u$OSSEC_DATABASE_USERNAME -p$OSSEC_DATABASE_PASSWORD $OSSEC_DATABASE -B -e \
	"select inet_ntoa(src_ip), count(*) as tmp from alert where rule_id = '5712' and alert.timestamp > (UNIX_TIMESTAMP() - 86400) group by rule_id, src_ip order by tmp desc limit 10;"


#
echo -n "Top 10 Invalid User Attempt IP's in the last 72 hours: "
$MYSQL -u$OSSEC_DATABASE_USERNAME -p$OSSEC_DATABASE_PASSWORD $OSSEC_DATABASE -B -e \
	"select inet_ntoa(src_ip), count(*) as tmp from alert where rule_id = '5712' and alert.timestamp > (UNIX_TIMESTAMP() - 259200) group by rule_id, src_ip order by tmp desc limit 10;"


echo -n "Top 25 Invalid User Attempt IP's in the last 30 days: "
$MYSQL -u$OSSEC_DATABASE_USERNAME -p$OSSEC_DATABASE_PASSWORD $OSSEC_DATABASE -B -e \
	"select inet_ntoa(src_ip), count(*) as tmp from alert where rule_id = '5712' and alert.timestamp > (UNIX_TIMESTAMP() - 2592000) group by rule_id, src_ip order by tmp desc limit 25;"




echo -n "Top 10 Alerts in the last 24 hours:"
$MYSQL -u$OSSEC_DATABASE_USERNAME -p$OSSEC_DATABASE_PASSWORD $OSSEC_DATABASE -B -e \
	"select alert.rule_id, count(*) as tmp from alert LEFT JOIN signature on alert.rule_id = signature.rule_id where signature.level > 6 and alert.timestamp > (UNIX_TIMESTAMP() - 86400)  group by alert.rule_id order by tmp desc limit 10;"

echo -n "Top 10 Alerts in the DB "
$MYSQL -u$OSSEC_DATABASE_USERNAME -p$OSSEC_DATABASE_PASSWORD $OSSEC_DATABASE -B -e \
	"select alert.rule_id, count(*) as tmp from alert LEFT JOIN signature on alert.rule_id = signature.rule_id where signature.level > 6 group by alert.rule_id order by tmp desc limit 10;";


# 40113 is viruses detected.

# 4701 ssh protocol mismatch

# 50120 database shutdown

# 20100 IDS event

# 30051 squid

# 7204 changed ethernet

# 5104 promiscuous mode