topic Re: How to search the number of times an IP address comes up in our network traffic over different time ranges? in Splunk Search

How to search the number of times an IP address comes up in our network traffic over different time ranges?

phspec — Tue, 12 Apr 2016 21:11:20 GMT

I'm searching for how frequently an IP address comes up in our network traffic during a 30, 30-60-60-90- and 90-120 day period. My search looks like the one below:

index=networkTraffic | stats count(Dst_IP) by Dst_IP

Re: How to search the number of times an IP address comes up in our network traffic over different time ranges?

twinspop — Tue, 12 Apr 2016 21:28:29 GMT

There likely is a better way to do this, but it works.

index=networkTraffic earliest=-120d | stats count count(eval(now()-_time<(86400*30))) as 30d count(eval(now()-_time>(86400*30) and now()-_time<(86400*60))) as "30d-60d" count(eval(now()-_time>(86400*60) and now()-_time<(86400*90))) as "60d-90d" count(eval(now()-_time>(86400*90))) as "90d-120d" by Dst_IP

Re: How to search the number of times an IP address comes up in our network traffic over different time ranges?

somesoni2 — Tue, 12 Apr 2016 21:29:35 GMT

Try like this

index=networkTraffic | bucket _time span=30d | stats count(Dst_IP) as count by _time Dst_IP | eval day=floor((now()-_time)/86400) | eval Period=tostring(day)."-".tostring(day+30) | chart values(count) over Dst_IP by Period

Re: How to search the number of times an IP address comes up in our network traffic over different time ranges?

martin_mueller — Tue, 12 Apr 2016 21:34:44 GMT

I'd separate the time-binning and stats-ing into two steps:

index=networkTraffic earliest=-120d | eval bin = case(_time >= relative_time(now(), "-30d"), "0-30", _time >= relative_time(now(), "-60d"), "30-60", _time >= relative_time(now(), "-90d"), "60-90", 1=1, "90-120") | stats count by bin Dst_IP

To make this run at reasonable speed, you'll want to define an accelerated data model for your network traffic, or fill a summary index with daily counts by IP.

Re: How to search the number of times an IP address comes up in our network traffic over different time ranges?

twinspop — Tue, 12 Apr 2016 22:32:37 GMT

Yeah, I was thinking more in the "arbitrary" ranges mindset without realizing he had requested 30 day buckets. 🙂

Re: How to search the number of times an IP address comes up in our network traffic over different time ranges?

phspec — Mon, 25 Apr 2016 20:49:20 GMT

I get a "120-150" day column even though my earliest=-120d@d. I've also tried earliest=-120d. Could you possibly point towards why I'm getting an extra 5th column? My search is below

index=networkTraffic earliest=-120d@d | dedup source | bucket _time span=30d | stats count(VDst_IP) as count by _time Dst_IP | eval day=floor((now()-_time)/86400) | eval Period=tostring(day)."-".tostring(day+30) | chart values(count) over Dst_IP by Period | Sort by Dst_IP

Re: How to search the number of times an IP address comes up in our network traffic over different time ranges?

martin_mueller — Mon, 25 Apr 2016 21:26:18 GMT

The time range -120d@d contains things older than 120 days - snap to start of day. Those get sorted into the 120-150 bin.

Re: How to search the number of times an IP address comes up in our network traffic over different time ranges?

phspec — Mon, 25 Apr 2016 21:32:39 GMT

should I just do -120d instead of -120d@d then?

Re: How to search the number of times an IP address comes up in our network traffic over different time ranges?

martin_mueller — Mon, 25 Apr 2016 21:34:59 GMT

That should work, provided there's no daylight savings time adding an hour.

The relative_time() approach I posted further down should survive daylight savings time oddities.

Re: How to search the number of times an IP address comes up in our network traffic over different time ranges?

phspec — Mon, 25 Apr 2016 21:45:57 GMT

so the time section of my search should look like, earliest=relative_time(-120d)

Re: How to search the number of times an IP address comes up in our network traffic over different time ranges?

martin_mueller — Mon, 25 Apr 2016 22:15:14 GMT

No, that's no valid syntax. Look at my April 12th comment below.

Re: How to search the number of times an IP address comes up in our network traffic over different time ranges?

phspec — Wed, 27 Apr 2016 20:51:02 GMT

I've done the search with 120d@d, 120d, and relative_time(now(), "-120d"). All of them return a '120-150' column, so I don't believe that column is being returned because of the time range the search is being executed on. I believe the '120-150' column is being made because of this line, 'eval Period=tostring(day)."-".tostring(day+30)', so I'm trying to edit my search to include, 'eval if(day<=120 , Period=tostring(day)."-".tostring(day+30), "NULL")', but I keep getting errors due to incorrect syntax. Any help is appreciated.