TrafMeter :: Useful SQL scripts for analyzing the table with the captured packets
Useful SQL scripts for analyzing the table with the captured packets
TrafMeter doesn't have a native tool for building the reports from a Packet Logging database. To help in creating your custom SQL-scripts for building various reports we provide some Microsoft SQL Server examples listed below.
1. Calculating the total amount of traffic for every filter
select filterid, sum(sent),sum(recv) from packets group by filterid
2. Calculating the total amount of traffic for every filter for a specific day
select filterid as filterid, sum(sent) as sent, sum(recv) as recv from packets
where datediff(day,'2002-11-24',ts)=0
group by filterid
order by filterid
3. Calculating the total amount of traffic for every filter for a specific month
select filterid as filterid, sum(sent) as sent, sum(recv) as recv from packets
where datediff(month,'2002-11-01',ts)=0
group by filterid
order by filterid
4. Calculating the total amount of traffic per day for a specific filter for a specific month
Set the variable @dd to the first day of the required month.
declare @dd datetime
set @dd='2002-11-01'
select datediff(day,@dd,ts) as mday, sum(sent) as sent, sum(recv) as recv
from packets
where (filterid=1 AND (datediff(month,@dd,ts)=0))
group by datediff(day,@dd,ts)
order by datediff(day,@dd,ts)
The example above shows the dates with a zero-based index. E.g. the first day of the month will be 0, the second day of month will be 1. To avoid this problem, you can use the following script with a temporary table:
drop table #p1
go
declare @dd datetime
set @dd='2002-11-01'
select datediff(day,@dd,ts) as mday, sum(sent) as sent, sum(recv) as recv
into #p1
from packets
where (filterid=1 AND (datediff(month,@dd,ts)=0))
group by datediff(day,@dd,ts)
go
update #p1 set mday=mday+1
go
select * from #p1 order by mday
5. Calculating the total amount of traffic per host for a specific filter for a specific month
select dst_ip, sum(sent) as sent, sum(recv) as recv
from packets
where (filterid=1 AND (datediff(month,'2002-11-01',ts)=0))
group by dst_ip
