How to query for similar events from aggregated data and few other criteria.

[mahe90]

Hi,

I'm trying to find/create a splunk query for the following.

My log is something like below:

time=2018-10-26 06:09:21 UTC source=1.2.3.4 dest=5.6.7.8 bytes=100

I'm aggregating the bytes something like below for 30min interval:

index="abc" | bucket _time span=30m | stats values(source), values(dest), sum(bytes) as total_bytes

If there are some source hosts which are sending to destination with fixed a data sizes (i.e, total_bytes) for every 30min and for last 1 day, then i would like to know those sources using splunk query.

Basically, following conditions should exists:

a) bytes are same. (total_bytes for the current bucket span and previous should be identical)
b) destination is same
c) time span bucket count 48 (meaning , 30min span for last 24 hours. 24*2 = 48).

Could you please throw some light on creating the query for the above. I really appreciate your help this regard.

Thanks,
Mahesh

[Richfez · Accepted solution]

You might be able to do this by re-stats-ing the stats (lol) and counting by the sum of your bytes.

I think you need to mildly rework your actual stats, though, since you want it where the destination is the same (e.g. by dest as part of the stats). We also don't need the source in there, and having it in there might complicate things. Also I don't see where you are doing the _time in your by clause either, so I'm going to assume that's just a copy/paste oversight. So our new stats is...

index="abc" | bucket _time span=30m 
| stats sum(bytes) as total_bytes by dest, _time

You should then have results that are _time (in 30 minute chunks), dest, and total_bytes.

Now we want to count these results, looking for the same dest and same total_bytes.

index="abc" | bucket _time span=30m 
| stats sum(bytes) as total_bytes by dest, _time
| stats count by dest, total_bytes

So that should give you a consolidated list of how many times total_bytes occurs by dest over the entire time period. One final piece is to search that result for where is greater than 47. Or equal to 48. Or larger than 5, whatever, you'll easily see how to make that happen... 🙂

index="abc" | bucket _time span=30m 
| stats sum(bytes) as total_bytes by dest, _time
| stats count by dest, total_bytes
| search count>47

Do those, step by step so that you can a) modify it a bit if I got a field name wrong or something, and b) so you understand each piece. That way if you have a similar problem you should be able to handle it yourself!

Happy Splunking,
Rich

[mahe90]

Thank you , Rich.