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 ```