############################ Traffic Analysis Script per Node

############################

Data We want on Daily Basis:

1: Total Count of (SELECT,INSERT,UPDATE,DELETE) Queries executed DAY-1. DONE
2: Total Count of SELECT queries previous-day. DONE
3: Total Count of INSERT queries previous-day. DONE
4: Total Count of UPDATE queries previous-day. DONE
5: Total Count of DELETE queries previous-day. DONE

6: Max TPS of SELECT queries previous-day. DONE

7: Max TPS of INSERT queries previous-day. DONE
8: Max TPS of UPDATE queries previous-day. DONE
9: Max TPS of DELETE queries previous-day. DONE

10: Max Memory Utilitzation previous-day.

11: Max CPU Utilitzation previous-day.

############################ Planing ############################

1: First we will create read-only user for gathering stats.
2: Create Script which will gather required states and truncate mysql.general_log
table into file name convention will be "Stats_nodeIP_date.txt"
3: Schedule the cron job to execute the script on daily basis.

############################ Implementation ############################

create user 'traffic_analyzer'@'localhost' identified by '********'; --

Creating Read only user for traffic analysis.
grant select on mysql.* to 'traffic_analyzer'@'localhost';
--Granting Read-Only rights on mysql schema.
grant drop on mysql.general_log to 'traffic_analyzer'@'localhost';
--Granting privilege to user to truncate mysql.general_log.
flush privileges;

cd /home/wso2/scripts/node_stats
--Switch to directory where to create script and store files.
vi node232_stats.sh
--Creating script file.
chmod +x node232_stats.sh
--Making the script executable

========================= Script Starts =========================

#!/bin/bash

# Set the file name using the current date

filename="Your-Path/node_stats_$(date --date="yesterday" +%Y%m%d).txt"
prev_date=$(date --date="yesterday" +%Y-%m-%d) # format like 2025-04-27
day=$(echo $prev_date | awk -F'-' '{print $3}')
padded_day=$(printf "%02d" $day) # Pad day with leading zero if necessary

#### This Script is compiled by Dost Muhammad Khalil(DBA) to analyze daily traffic
on nodes.
#### This Script provide States from database for Day-1 and also provide max cpu &
Memory Utilization for Day-1
#### This Script is compiled as we currently don't have any monitoring tool in
future if any monitoring tool like PMM, MEM is
#### deplyed this script should be disabled.
#### This Script gather data from general query log, slow query log and sa files.
#### This Script Purge the old data from general query log so that to maintain
storage.

echo -e "\n\n############ The Script is Compiled By Dost Muhammad Khalil to analyze

traffic for Day-1 ############\n" >> $filename

# ------THIS QUERY WILL GIVE TOTAL QUERIES COUNT(SELECT,INSERT,UPDATE,DELETE) FOR

DAY-1 --------
echo -e "\n\n############ Total Count of SELECT,INSERT,UPDATE,DELETE Queries For
Previous-day ############\n" >> $filename
mysql -u traffic_analyzer -p'Mysql123#' -t -e "
SELECT DATE(event_time) AS query_date,COUNT(argument) AS ALL_DML
FROM mysql.general_log
WHERE argument REGEXP 'SELECT|UPDATE|INSERT|DELETE' AND DATE(event_time) = '$
{prev_date}'
GROUP BY DATE(event_time);" >> $filename

# ------THIS QUERY WILL GIVE TOTAL QUERIES COUNT(SELECT FOR DAY-1) --------
echo -e "\n############ Total Count of SELECT Queries For Previous-day
############\n" >> $filename
mysql -u traffic_analyzer -p'Mysql123#' -t -e "
SELECT DATE(event_time) AS query_date,COUNT(argument) AS SELECT_COUNT
FROM mysql.general_log
WHERE argument LIKE '%SELECT%' AND DATE(event_time) = '${prev_date}'
GROUP BY DATE(event_time);" >> $filename

# ------THIS QUERY WILL GIVE TOTAL QUERIES COUNT(INSERT FOR DAY-1) --------
echo -e "\n############ Total Count of INSERT Queries For Previous-day
############\n" >> $filename
mysql -u traffic_analyzer -p'Mysql123#' -t -e "
SELECT DATE(event_time) AS query_date,COUNT(argument) AS INSERT_COUNT
FROM mysql.general_log
WHERE argument LIKE '%INSERT%' AND DATE(event_time) = '${prev_date}'
GROUP BY DATE(event_time);" >> $filename

# ------THIS QUERY WILL GIVE TOTAL QUERIES COUNT(UPDATE FOR DAY-1) --------
echo -e "\n############ Total Count of UPDATE Queries For Previous-day
############\n" >> $filename
mysql -u traffic_analyzer -p'Mysql123#' -t -e "
SELECT DATE(event_time) AS query_date,COUNT(argument) AS UPDATE_COUNT
FROM mysql.general_log
WHERE argument LIKE '%UPDATE%' AND DATE(event_time) = '${prev_date}'
GROUP BY DATE(event_time);" >> $filename

# ------THIS QUERY WILL GIVE TOTAL QUERIES COUNT(DELETE FOR DAY-1) --------
echo -e "\n############ Total Count of DELETE Queries For Previous-day
############\n" >> $filename
mysql -u traffic_analyzer -p'Mysql123#' -t -e "
SELECT DATE(event_time) AS query_date,COUNT(argument) AS DELETE_COUNT
FROM mysql.general_log
WHERE argument LIKE '%DELETE%' AND DATE(event_time) = '${prev_date}'
GROUP BY DATE(event_time);" >> $filename

# ------THIS QUERY WILL GIVE MAX_TPS For SELECT QUERIES FOR DAY-1 --------
echo -e "\n############ This will give MAX_TPS For SELECT Queries For Previous-day
############\n" >> $filename
mysql -u traffic_analyzer -p'Mysql123#' -t -e "
SELECT DATE_FORMAT(event_time, '%Y-%m-%d %H:%i:%s') AS time_second,COUNT(*) AS tps
FROM mysql.general_log
WHERE argument LIKE '%SELECT%' and date(event_time) = '${prev_date}'
GROUP BY time_second
ORDER BY tps DESC
limit 1;" >> $filename

# ------THIS QUERY WILL GIVE MAX_TPS For INSERT QUERIES FOR DAY-1 --------
echo -e "\n############ This will give MAX_TPS For INSERT Queries For Previous-day
############\n" >> $filename
mysql -u traffic_analyzer -p'Mysql123#' -t -e "
SELECT DATE_FORMAT(event_time, '%Y-%m-%d %H:%i:%s') AS time_second,COUNT(*) AS tps
FROM mysql.general_log
WHERE argument LIKE '%INSERT%' and date(event_time) = '${prev_date}'
GROUP BY time_second
ORDER BY tps DESC
limit 1;" >> $filename

# ------THIS QUERY WILL GIVE MAX_TPS For UPDATE QUERIES FOR DAY-1 --------
echo -e "\n############ This will give MAX_TPS For UPDATE Queries For Previous-day
############\n" >> $filename
mysql -u traffic_analyzer -p'Mysql123#' -t -e "
SELECT DATE_FORMAT(event_time, '%Y-%m-%d %H:%i:%s') AS time_second,COUNT(*) AS tps
FROM mysql.general_log
WHERE argument LIKE '%UPDATE%' and date(event_time) = '${prev_date}'
GROUP BY time_second
ORDER BY tps DESC
limit 1;" >> $filename

# ------THIS QUERY WILL GIVE MAX_TPS For DELETE QUERIES FOR DAY-1 --------
echo -e "\n############ This will give MAX_TPS For DELETE Queries For Previous-day
############\n" >> $filename
mysql -u traffic_analyzer -p'Mysql123#' -t -e "
SELECT DATE_FORMAT(event_time, '%Y-%m-%d %H:%i:%s') AS time_second,COUNT(*) AS tps
FROM mysql.general_log
WHERE argument LIKE '%DELETE%' and date(event_time) = '${prev_date}'
GROUP BY time_second
ORDER BY tps DESC
limit 1;" >> $filename

# ------THIS QUERY WILL GIVE THE TOTAL COUNT OF SLOW SELECT QUERIES FOR DAY-1
--------
echo -e "\n############ THIS QUERY WILL GIVE THE TOTAL COUNT OF SLOW SELECT QUERIES
FOR DAY-1 ############\n" >> $filename
mysql -u traffic_analyzer -p'Mysql123#' -t -e "
select COUNT(*) from mysql.slow_log where user_host not like '%traffic_analyzer%'
and sql_text like '%SELECT%'and DATE(start_time)='${prev_date}';\G" >> $filename

# ------THIS QUERY WILL GIVE THE LIST OF SLOW SELECT QUERIES FOR DAY-1 --------
echo -e "\n############ THIS QUERY WILL GIVE LIST OF SLOW SELECT QUERIES FOR DAY-1
############\n" >> $filename
mysql -u traffic_analyzer -p'Mysql123#' -t -e "
select sql_text from mysql.slow_log where user_host not like '%traffic_analyzer%'
and sql_text like '%SELECT%'and DATE(start_time)='${prev_date}';\G" >> $filename

# ------ THIS SECTION WILL GIVE MAX MEMORY UTILIZATION FOR DAY-1 --------
echo -e "\n############ Max Memory Utilization For Previous-day ############\n" >>
$filename
sar -r -f /var/log/sa/sa${padded_day} | head -n 3; sar -r -f /var/log/sa/sa$
{padded_day} | awk 'NR > 3 {print $0, $5}' | sort -k6,6nr | head -n 1 >> $filename

# ------ THIS SECTION WILL GIVE MAX MEMORY UTILIZATION FOR DAY-1 --------
echo -e "\n############ Max CPUs Utilization For Previous-day ############\n" >>
$filename
sar -u -f /var/log/sa/sa${padded_day} | sort -k 4 -n -r | head -n 1 >> $filename

# ------ THIS SECTION WILL TRUNCATE TABLE mysql.general_log --------

echo -e "\n Truncating mysql.general_log Table Starts"
mysql -u traffic_analyzer -p'Mysql123#' -t -e "TRUNCATE TABLE mysql.general_log;"
echo -e "\n Truncating mysql.general_log Table Completed!"

========================= Script Ends =========================

crontab -e --Edit crontab

01 12 * * * /home/wso2/scripts/node_stats/node232_stats.sh --Add the line which
will execute the script on 1 minutes pass 12AM daily.