In this dataset, transactions (#3 + #9 + #10 - Mike), and (#5 + #7 +#11 - Alex) -- Would be displayed.
# | Time | User | Transaction |
1 | 12:01 | David | Login from 1.1.1.1 |
2 | 12:01 | Joe | Login from 2.2.2.2 |
3 | 12:02 | Mike | Login from 1.1.1.1 |
4 | 12:03 | David | Something else |
5 | 12:05 | Alex | Login from 1.1.1.1 |
6 | 12:06 | Mike | Something else |
7 | 12:09 | Alex | Delete table |
8 | 12:10 | Joe | Delete table |
9 | 12:06 | Mike | Delete table |
10 | 12:09 | Mike | Insert Table |
11 | 12:14 | Alex | Insert Table |
12 | 12:20 | David | Delete table |
Looking for one search to find all events where within 10 minutes:
1. User logged in from IP address 1.1.1.1 (Search: userIP = "1.1.1.1" transaction="Logged")
2. The same user then deleted a table (Search: databaseAction = "DeleteTable")
3. The same user then inserted a table (Search: databaseAction = "InsertTable")
I can use startswith and endswith with transaction, but this only gives me the first and last event, not the second.
First, thank you for giving a clear illustration of input, desired output, and the logic linking the two. Let me confirm: Are you skipping Joe because IP address is not 1.1.1.1?
Assuming this is correct, you are looking for something like
<some index search> transaction IN (Logged, DeleteTable)
| stats list(transaction) as transaction min(_time) as logon_time max(_time) as delete_time values(userIP) as userIP by User
| where mvindex(transaction, 0) == "Logged" AND mvindex(transaction, -1) LIKE "DeleteTable"
AND delete_time < relative_time(logon_time, "+10min") AND userIP == "1.1.1.1"
| fieldformat logon_time = strftime(logon_time, "%F %T")
| fieldformat delete_time = strftime(delete_time, "%F %T")
Output from your sample data is
User | transaction | logon_time | delete_time | userIP |
Alex | Logged DeleteTable | 2023-11-05 12:05:00 | 2023-11-05 12:10:00 | 1.1.1.1 |
Mike | Logged DeleteTable | 2023-11-05 12:02:00 | 2023-11-05 12:06:00 | 1.1.1.1 |
This is an emulation you can play with and compare with real data
| makeresults
| eval _raw="# Time User Transaction
1 12:01 David Login from 1.1.1.1
2 12:01 Joe Login from 2.2.2.2
3 12:02 Mike Login from 1.1.1.1
4 12:03 David Something else
5 12:05 Alex Login from 1.1.1.1
6 12:06 Mike Something else
7 12:09 Joe Delete table
8 12:10 Alex Delete table
9 12:06 Mike Delete table
10 12:20 David Delete table"
| multikv forceheader=1
| eval transaction = case(Transaction LIKE "Login from %", "Logged", Transaction == "Delete table", "DeleteTable", true(), "SomethingElse")
| rex field=Transaction "Login from (?<userIP>.+)"
| fields - _* linecount Transaction
| eval _time = strptime(Time, "%H:%M")
| search transaction IN (Logged, DeleteTable)
``` the above emulates
<some index search> transaction IN (Logged, DeleteTable)
```
Elegant solution. I'll take a look with our prod data and respond here. Thanks again the the reply.