I am quite new to Splunk search query. I have collected traffic logs from paloalto firewall. I want to have the Top 10 user monthly report which sum up the total byte size and sessions by a month. I have done the below search to get our expected result.
index=pan_logs sourcetype=pan_traffic | stats sum(bytes) as totalbytes, count(session_id) as Sessions by src_user | eval "Total Size"=
formatbytes(totalbytes)| sort -totalbytes | table src_user "Total Size" Sessions | head 10
On the other way, I need to divided the results by days. The below search a specific user and divides the total bytes and sessions per day.
index=pan_logs sourcetype=pan_traffic src_user="user1" | eval time=strptime(generated_time,"%Y/%m/%d %H:%M:%S")| bucket time span=1d | stats sum(bytes) as totalbytes, count(session_id) as Sessions by time | eval "Total Size"=
formatbytes(totalbytes)| eval Date=strftime(time, "%d/%m/%Y") | sort -Date | table Date "Total Size" Sessions
I would like the 2nd search to know the TOP 10 users and get the result of "divided by date" on that TOP 10 users.
My wordings seems confusing. What i need to achieve is to get Monthly Top 10 Users and their total size and sessions per day.
Can anyone give me some hints on it?
Thanks in advance.
Give this a try
index=pan_logs sourcetype=pan_traffic
| eval Date=strftime(strptime(generated_time,"%Y/%m/%d %H:%M:%S"), "%d/%m/%Y")
| stats sum(bytes) as totalbytes, count(session_id) as Sessions by Date,src_user
| eventstats sum(totalbytes) as TotalUserBytes by src_user
| sort -TotalUserBytes
| streamstats window=1 current=f first(TotalUserBytes) as prevTotalUserBytes
| eval sno=if(isnull(prevTotalUserBytes) OR prevTotalUserBytes!=TotalUserBytes,1,0)
| accum sno | where sno < 11
| fields - TotalUserBytes,prevTotalUserBytes,sno
| sort Date,src_user
Thanks somesoni2. The result is exactly what I want. You are the man. I think i have to change my thinking way of search.
Give this a try
index=pan_logs sourcetype=pan_traffic
| eval Date=strftime(strptime(generated_time,"%Y/%m/%d %H:%M:%S"), "%d/%m/%Y")
| stats sum(bytes) as totalbytes, count(session_id) as Sessions by Date,src_user
| eventstats sum(totalbytes) as TotalUserBytes by src_user
| sort -TotalUserBytes
| streamstats window=1 current=f first(TotalUserBytes) as prevTotalUserBytes
| eval sno=if(isnull(prevTotalUserBytes) OR prevTotalUserBytes!=TotalUserBytes,1,0)
| accum sno | where sno < 11
| fields - TotalUserBytes,prevTotalUserBytes,sno
| sort Date,src_user
The result is super perfect. But I was requested to show the username only once in the report. Like:
abc\user1 01/07/2014 3.62 GB 15320 46.99 GB
02/07/2014 2.46 GB 12954 46.99 GB
03/07/2014 3.62 GB 18994 46.99 GB
abc\user2 01/07/2014 2.92 GB 336094 37.83 GB
03/07/2014 2.96 GB 353829 37.83 GB
04/07/2014 2.44 GB 278589 37.83 GB
Is it possible?
Sorry for my typo. I was looking the possibility to show the results which grouped by username.
By your help, i can get something like below:
abc\user1 01/07/2014 3.62 GB 15320 46.99 GB
abc\user1 02/07/2014 2.46 GB 12954 46.99 GB
abc\user1 03/07/2014 3.62 GB 18994 46.99 GB
abc\user2 01/07/2014 2.92 GB 336094 37.83 GB
abc\user2 03/07/2014 2.96 GB 353829 37.83 GB
abc\user2 04/07/2014 2.44 GB 278589 37.83 GB
I am not sure I get what you need here. Could you provide more information?
@somesoni2 If I soft the src_user first, is it possible to show only once the username?
Thanks somesoni2. The result is exactly what I want. You are the man. I think i have to change my thinking way of search.