1

Currently I put hourly traffic (total number of input requests) of my website in a MySQL table. I keep data for the last 90 days.

I want to check every hour, lets say 6th hour, that whether the traffic has increased/decreased beyond some threshold than last 7 days or last 30 days 6th hour traffic. Basically, I see a pattern of traffic. Different hours have different values.

To generate alerts, I want to find various statistical metrics. After reading a little, I found out that Statsd can be used for this purpose.

Is it correct things to use for sending alerts like this? Is there any better/simpler solution for this?

I don't intend to build any dashboards.

My current data looks like this:

+---------------------+---------------------+-----------+----------+
| startTime           | endTime             | component | traffic  |
+---------------------+---------------------+-----------+----------+
| 2015-05-01 00:00:00 | 2015-05-01 01:00:00 | rest      | 29090345 |
| 2015-05-01 01:00:00 | 2015-05-01 02:00:00 | rest      | 32224087 |
| 2015-05-01 02:00:00 | 2015-05-01 03:00:00 | rest      | 35165799 |
| 2015-05-01 03:00:00 | 2015-05-01 04:00:00 | rest      | 36903464 |
| 2015-05-01 04:00:00 | 2015-05-01 05:00:00 | rest      | 40394130 |
| 2015-05-01 05:00:00 | 2015-05-01 06:00:00 | rest      | 44874862 |
| 2015-05-01 06:00:00 | 2015-05-01 07:00:00 | rest      | 49988600 |
| 2015-05-01 07:00:00 | 2015-05-01 08:00:00 | rest      | 52240544 |
| 2015-05-01 08:00:00 | 2015-05-01 09:00:00 | rest      | 54517705 |
| 2015-05-01 09:00:00 | 2015-05-01 10:00:00 | rest      | 55277967 |
| 2015-05-01 10:00:00 | 2015-05-01 11:00:00 | rest      | 55285309 |
| 2015-05-01 11:00:00 | 2015-05-01 12:00:00 | rest      | 55572614 |
Asmita
  • 11
  • 2

2 Answers2

0

Perhaps InfluxDB might be interesting for you. InfluxDB is a time-series database.

You could push your data either directly to InfluxDB via

  • statsd
  • collectd binary interface
  • graphite protocol
  • REST API

You can query InfluxDB via REST-API and there don't need a graphical interface. But Grafana works nice with it.

ppuschmann
  • 610
  • 1
  • 6
  • 16
0

You may use the following SQL script to compare the traffic.

set @threshold = 50;  /*threshold for comparing the traffic*/
set @limit = 30  /*how many days to consider while generating avg value*/

/*calculate the time range, comparison is done for the last hour*/
set @end_time = current_timestamp();  
set @end_time = timestamp(date(@end_time), maketime(hour(@end_time), 0, 0));
set @start_time = date_sub(@end_time, interval 1 hour);

/*find out the traffic for the last hour*/
select traffic
        from test.traffic_stats
        where startTime >= @start_time
            and endTime <= @end_time
    into @curr_traffic;

/*now find out the avg traffic for the past @limit days*/
select ifnull(avg(traffic), 0)
        from test.traffic_stats
        where startTime < @start_time
            and startTime >= date_sub(@start_time, interval @limit day)
            and time(startTime) >= time(@start_time)
            and time(endTime) <= time(@end_time)
    into @avg_traffic;

/*generate the report*/
select concat(
        'Current traffic '
        @curr_traffic,
        ' is '
        if(@curr_traffic > @avg_traffic + @threshold,
            'more',
            if(@curr_traffic < @avg_traffic - @threshold,
                'less',
                'same'
            )
        ), 
        ' compared to the avg traffic ', 
        @avg_traffic
    ) as result;

The script will generate the report based on avg traffic for the last 30 days by querying test.traffic_stats table. Please modify the script to match your requirement. Now save this SQL script as report.sql and you can use Cron to run it at specific intervals using mysql command line as given below.

mysql -hhost -uuser -ppassword -s <report.sql | awk '{print $1}'

This will extract the result and print to stdout. Now you can use GNU Mailutils to send out the alert to your email address. 

mail -s "$(mysql -hhost -uuser -ppassword -s <report.sql | awk '{print $1}')" you@example.com
Vishal
  • 121
  • 4