topic Re: Query with an additional condition in Splunk Search

Query with an additional condition

ivan123357 — Wed, 25 Oct 2023 13:55:12 GMT

Hi!

Faced with writing a query with an additional check and I can't find a way out. I will be glad if you tell me the direction or help with advice.

We have the following custom logic:

1. When user do some action(it is not important) we generate an event in index=custom with the following fields: evt_id: 1, user_id: 555 (example)

2. The user should confirm that he is doing this "some action" in third-party app, and this app generate to the index=custom the next event: evt_id: 2, user_id:555 (example) msg:confirmed

3. If user NOT CONFIRMED the SOME ACTION from step 1 - we need to generate alert. It means, that Splunk didn't receive evt_id:2 in index=custom

The alert logic is following:

We need to alert when evt_id: 1 was more than 5 minutes ago(the time that the user has to confirm "some action') and when NO evt_id: 2 with the same user_id by the time the alert starts working.

I understood that I need to do the first search like(example):

index=custom evt_id=1 earliest=-5m latest=-7m

But I have no idea how to implement additional condition with evt_id:2. if we didn't have the user_id field, then I could use stats count command but I need to correlate both events(1 and 2) with the field user_id.

Thanks for you help, have a nice day.

Re: Query with an additional condition

ITWhisperer — Wed, 25 Oct 2023 15:58:01 GMT

Assuming you are correlating by user id (as you don't appear to have the action id in your index), you could do something like this

index=custom evt_id=1 OR evt_id=2 earliest=-7m latest=now | stats latest(evt_id) as last_event latest(_time) as last_time by user_id | where last_event=1 AND last_time < relative_time(now(), "-5m")

Re: Query with an additional condition

gcusello — Wed, 25 Oct 2023 17:10:17 GMT

Hi @ivan123357 ,

I'd try something like this:

index=custom (evt_id=1 OR evt_id=2) earliest=-5m latest=-7m | stats last(evt_id) AS evt_id earliest(_time) AS earliest latest(_time) AS latest BY user_id | where evt_id=1 OR (latest-earliest>300)

Ciao.

Giuseppe