Splunk Search

Adding a colum to stats results

amritanshgupta
Explorer

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!

1 Solution

woodcock
Esteemed Legend

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")

View solution in original post

woodcock
Esteemed Legend

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")

View solution in original post

amritanshgupta
Explorer

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?

0 Karma

woodcock
Esteemed Legend

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.

Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!