Adding a colum to stats results

[amritanshgupta]

Hey!

Right now I have a search -

source="tcp:6555"| search Message_Type =IP  | stats sum(Bytes) AS Bytes by IP | sort -Bytes | head 10 | eval Bytes = case(Bytes/pow(1024,1) <1024, tostring(round(tonumber(Bytes)/pow(1024,1),2))."K", Bytes/pow(1024,2) <1024, tostring(round(tonumber(Bytes)/pow(1024,2),2))."M", Bytes/pow(1024,3) <1000, tostring(round(tonumber(Bytes)/pow(1024,3),2))."G", true(), Bytes) 

Which gives me

192.168.abc.abc     1.23M    
173.241.abc.abc     436.03K      
40.118.acb.abc  422.66K      
192.168.abc.abc     255.59K      
50.19.abc.abc   83.63K

....

till 10 unique values for IPs.
What I want to do now is a separate column that states if the IP has posted an event in the past 10 minutes.
I had something like this in mind

source="tcp:6565"| search Message_Type =IP  | stats sum(Bytes) AS Bytes by IP | sort -Bytes | head 10 | eval Bytes = case(Bytes/pow(1024,1) <1024, tostring(round(tonumber(Bytes)/pow(1024,1),2))."K", Bytes/pow(1024,2) <1024, tostring(round(tonumber(Bytes)/pow(1024,2),2))."M", Bytes/pow(1024,3) <1000, tostring(round(tonumber(Bytes)/pow(1024,3),2))."G", true(), Bytes) | eval tnow = now()-_time | eval Status = case(tnow <=300, "Up", tnow>300, "Down") 

I kind of understand why this doesnt work but how can I make it work?

Thanks!

[woodcock · Accepted solution]

Like this:

source="tcp:6555"| search Message_Type =IP  | stats max(_time) AS latest_time sum(Bytes) AS Bytes by IP | sort -Bytes | head 10 | eval Bytes = case(Bytes/pow(1024,1) <1024, tostring(round(tonumber(Bytes)/pow(1024,1),2))."K", Bytes/pow(1024,2) <1024, tostring(round(tonumber(Bytes)/pow(1024,2),2))."M", Bytes/pow(1024,3) <1000, tostring(round(tonumber(Bytes)/pow(1024,3),2))."G", true(), Bytes)  | eval eventInLastTenMinutes=if((latest_time>=relative_time(now(), "-10m")), "Yes", "No")

[amritanshgupta]

Thanks! This works like a charm.
I just started with splunk and would really be grateful if you clear some of my doubts.

Can you explain how the stats command works here?
Also whats the difference between if and case?

[woodcock]

A single stats can do many different types of summaries of the data. I told it to look for the highest value for _time ( I could also have used latest(_time) ) as well as summarizing the bytes for each IP. There is no real difference between if and case; the latter is really just a string of the former.