topic Re: Search to sum if IP is observed in different days in Splunk Search

Search to sum if IP is observed in different days

fatsug — Thu, 21 Jul 2022 10:38:25 GMT

Hello community

I’m trying to figure out how to perform a search which considers events on different days.

The idea is to search for an events by IP address and what I’d like to achieve is to check if the same IP (the same type of event) is observed in more than one specified timeframe (day/week/month). I started out with the following:

<base-search> earliest="-7d@d" latest="@d"
| stats count by ip date

And thought I could compare if IP address occurs on more than one date. Though I suppose I’d have to loop through all the results for each IP and I could not get the SPL to work at all.

Instead I figured that I could use something like

| bin span=1d _time
| stats count as c_ip by _time

I figured I could compare content of bins somehow, though the bins are still just by “date”.

I figured I’d be able to combine this with something like “eval” to get IP addresses which has events on more than one date in rage, preferably with number of events per date/bin and a total. Thi smay also need some "fillnull" or something.

IP	2022-06-29	2022-07-01	2022-07-02	2022-07-12	Sum
<ip1>	6	5	8	2	21
<ip2>	-	5	-	4	9

Though I am not having any success.

I hope I managed to articulate my idea here. If so, is what I’m aiming fore possible? Any suggestions/feedback is greatly appreciated, close enough would be a lot better than nothing 🙂

Best regards

// G

Re: Search to sum if IP is observed in different days

ITWhisperer — Thu, 21 Jul 2022 11:21:03 GMT

| stats count by ip date | xyseries ip date count | addtotals col=f row=t

Re: Search to sum if IP is observed in different days

fatsug — Thu, 21 Jul 2022 11:35:28 GMT

@ITWhispererGreat start, thank you!

So, this gives me the table I was after. However, it does not filter out only IP adresses which has events on more than one date. Taking the same example again and expanding

IP	2022-06-29	2022-07-01	2022-07-02	2022-07-12	Sum
<ip1>	6	5	8	2	21
<ip2>	-	5	-	4	9
<ip3>	-	23	-	-	23

In this example, <ip3> should not be included in the results as there are only events on a single date. I'm only looking for IPs with events on at least two different dates.

Best regards

// G

Re: Search to sum if IP is observed in different days

ITWhisperer — Thu, 21 Jul 2022 11:44:49 GMT

Re: Search to sum if IP is observed in different days

fatsug — Thu, 21 Jul 2022 11:48:40 GMT

Nice! Big thank you, exactly what I needed