Query with an additional condition

[ivan123357]

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.

[gcusello]

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

[ITWhisperer · Accepted solution]

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")