Useful SQL scripts for analyzing the table with the captured packets

TMeter 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