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
| stats count by ip date
| eventstats count as days by ip
| where days > 1
| xyseries ip date count
| addtotals col=f row=t
| stats count by ip date
| xyseries ip date count
| addtotals col=f row=t
@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
| stats count by ip date
| eventstats count as days by ip
| where days > 1
| xyseries ip date count
| addtotals col=f row=t
Nice! Big thank you, exactly what I needed