Adding Time/date to " Traffic" log type data set

[ronslocal]

Hi all, We are trying to create a report that will help us to identify p2p users when we receive RIAA/MPAA letters. We are using NAT so we need to be able to translate back to internal IPs. Our query looks like this so far, but we cant seem to figure out how to simply add the time/date to the data set. Can anyone help? Thanks! SELECT srcip,srcport,dstip,dstport,transip,transport,count(*) as count FROM $log WHERE $filter AND transip IS NOT NULL GROUP BY srcip,srcport,dstip,dstport,transip,transport Ron

[hzhao_FTNT]

Hi Ron, For timestamp of every single record: SELECT from_dtime(dtime) as timestamp, srcip,srcport,dstip,dstport,transip,transport,count(*) as count FROM $log WHERE $filter AND transip IS NOT NULL GROUP BY timestamp, srcip,srcport,dstip,dstport,transip,transport For hour of day: SELECT $hour_of_day as hod, srcip,srcport,dstip,dstport,transip,transport,count(*) as count FROM $log WHERE $filter AND transip IS NOT NULL GROUP BY hod, srcip,srcport,dstip,dstport,transip,transport For day of week: SELECT $day_of_week as dow, srcip,srcport,dstip,dstport,transip,transport,count(*) as count FROM $log WHERE $filter AND transip IS NOT NULL GROUP BY dow, srcip,srcport,dstip,dstport,transip,transport Regards, hz

[ronslocal]

Thank hz! Thats exactly what we were looking for! Ron