Splunk Search

Search Top 10 user then based on these 10 user to search again by date

Explorer

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.

Tags (2)
0 Karma
1 Solution

Revered Legend

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

View solution in original post

Explorer

Thanks somesoni2. The result is exactly what I want. You are the man. I think i have to change my thinking way of search.

0 Karma

Revered Legend

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

View solution in original post

Explorer

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?

0 Karma

Explorer

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

0 Karma

Revered Legend

I am not sure I get what you need here. Could you provide more information?

0 Karma

Explorer

@somesoni2 If I soft the src_user first, is it possible to show only once the username?

0 Karma

Explorer

Thanks somesoni2. The result is exactly what I want. You are the man. I think i have to change my thinking way of search.

State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!