Home > Uncategorized > A few argus SQL queries to pull useful data

A few argus SQL queries to pull useful data

Since I’ve been slow to work on my master plan to render argus data using d3js, I figured I’d share a few SQL queries that I use to get daily reports of bandwidth utilization.

I will provide a script I run daily. Note that you can grab smaller increments as you wish with stime between UNIX_TIMESTAMP(‘YYYY-MM-DD HH:mm:ss’).

#!/bin/bash

#script will run at 12:15AM, so it would apply to the day before
export yestdate=$(date -d "yesterday" +%Y%m%d) #table name is argustable_YYYYMMDD
echo "Reporting for $yestdate" > ~/dailyreport.txt
echo "" >> ~/dailyreport.txt
echo "" >> ~/dailyreport.txt

echo "-- MOST BANDWIDTH PER SOURCE AND DESTINATION ADDRESSES --" >> ~/dailyreport.txt
echo "Top 10 Uploaded to addresses: sum of src -> dst transaction (upload) bytes per source address" >> ~/dailyreport.txt
mysql -u argus -pSQLPASSWORD -e "select distinct saddr, daddr, sum(sbytes) as sbytes_sum, sum(dbytes) as dbytes_sum from \`argus\`.\`argustable_$yestdate\` group by saddr, daddr order by sbytes_sum desc limit 10;" >> ~/dailyreport.txt
echo "" >> ~/dailyreport.txt
echo "Top 10 Downloaded from addresses: sum of dst -> src transaction (download) bytes per source address" >> ~/dailyreport.txt
mysql -u argus -pSQLPASSWORD -e "select distinct saddr, daddr, sum(dbytes) as dbytes_sum, sum(sbytes) as sbytes_sum from \`argus\`.\`argustable_$yestdate\` group by saddr, daddr order by dbytes_sum desc limit 10;" >> ~/dailyreport.txt
echo "" >> ~/dailyreport.txt


echo "" >> ~/dailyreport.txt
echo "" >> ~/dailyreport.txt
echo "" >> ~/dailyreport.txt
echo "" >> ~/dailyreport.txt

echo "-- GENERAL BANDWIDTH CONSUMPTION --" >> ~/dailyreport.txt
echo "Top 10 sending source addresses: src -> dst transaction bytes, by source address" >> ~/dailyreport.txt
mysql -u argus -pSQLPASSWORD -e "select saddr, sum(sbytes) as sbytes_sum, sum(dbytes) as dbytes_sum from \`argus\`.\`argustable_$yestdate\` group by saddr order by sbytes_sum desc limit 10;" >> ~/dailyreport.txt
echo "" >> ~/dailyreport.txt
echo "Top 10 receiving source addresses: dst -> src transaction bytes, by source address" >> ~/dailyreport.txt
mysql -u argus -pSQLPASSWORD -e "select saddr, sum(dbytes) as dbytes_sum, sum(sbytes) as sbytes_sum from \`argus\`.\`argustable_$yestdate\` group by saddr order by dbytes_sum desc limit 10;" >> ~/dailyreport.txt
echo "" >> ~/dailyreport.txt
echo "" >> ~/dailyreport.txt

echo "Top 10 sending destination addresses: src -> dst transaction bytes, by destination address" >> ~/dailyreport.txt
mysql -u argus -pSQLPASSWORD -e "select daddr, sum(sbytes) as sbytes_sum, sum(dbytes) as dbytes_sum from \`argus\`.\`argustable_$yestdate\` group by daddr order by sbytes_sum desc limit 10;" >> ~/dailyreport.txt
echo "" >> ~/dailyreport.txt
echo "Top 10 receiving destination addresses: dst -> src transaction bytes, by destination address" >> ~/dailyreport.txt
mysql -u argus -pSQLPASSWORD -e "select daddr, sum(dbytes) as dbytes_sum, sum(sbytes) as sbytes_sum from \`argus\`.\`argustable_$yestdate\` group by daddr order by dbytes_sum desc limit 10;" >> ~/dailyreport.txt
echo "" >> ~/dailyreport.txt
echo "" >> ~/dailyreport.txt

echo "-- FLOW DURATION --" >> ~/dailyreport.txt
echo "Top 10 longest flows (by source address)" >> ~/dailyreport.txt
mysql -u argus -pSQLPASSWORD -e "select saddr, daddr, sum(dur) as dur_sum from \`argus\`.\`argustable_$yestdate\` group by saddr order by dur_sum desc limit 10;" >> ~/dailyreport.txt
echo "" >> ~/dailyreport.txt
echo "Top 10 longest flows (by destination address)" >> ~/dailyreport.txt
mysql -u argus -pSQLPASSWORD -e "select saddr, daddr, sum(dur) as dur_sum from \`argus\`.\`argustable_$yestdate\` group by daddr order by dur_sum desc limit 10;" >> ~/dailyreport.txt
echo "" >> ~/dailyreport.txt
echo "" >> ~/dailyreport.txt

echo "-- FLOW COUNT --" >> ~/dailyreport.txt
echo "Top 10 flow counts (by source address)" >> ~/dailyreport.txt
mysql -u argus -pSQLPASSWORD -e "select distinct saddr, count(daddr) as record_count from \`argus\`.\`argustable_$yestdate\` group by saddr order by record_count desc limit 10;" >> ~/dailyreport.txt
echo "" >> ~/dailyreport.txt
echo "Top 10 flow counts (by destination address)" >> ~/dailyreport.txt
mysql -u argus -pSQLPASSWORD -e "select distinct daddr, count(saddr) as record_count from \`argus\`.\`argustable_$yestdate\` group by daddr order by record_count desc limit 10;" >> ~/dailyreport.txt

If you’d like to send yourself an Email, add the following line:

cat dailyreport.txt | /bin/mail -s "Daily bandwidth utilization report for $yestdate from $HOSTNAME" mbrown@domain.com
Advertisements
  1. Dude E Dude
    July 19, 2012 at 4:18 pm

    Thnks

    • July 19, 2012 at 5:03 pm

      No problem. Feel free to post some back in the comments as you move forward. The real trick here would be to feed these through scripts to generate some graphs. I’ve yet to look deeply at that. Here is an idea, with reference to PacketQ, using jQuery-based flot.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: