Is this a Join, subsearch, or something else?

[theeven]


In my search I am at a stage where I have something like below.

USERID EVENT STATUS
1 HELLO PASS
2 HELLO FAIL
3 HELLO FAIL
4 HELLO PASS
2 HELLO PASS
3 HELLO PASS
7 HELLO FAIL
4 HELLO PASS
8 HELLO PASS

I need a way to list all USERID who have encountered both PASS and FAIL STATUS

2
3

help?
thanks.

[gkanapathy · Accepted solution]

Pretty straightforward:

... | stats values(STATUS) as statuses by USERID | where statuses=="PASS" AND statuses=="FAIL"

[theeven]

Okay here's my solution. Works good for me.

| stats values(STATUS) as STATUS_MV by USERID 
| eval STATUS_COUNT = mvcount(STATUS_MV) 
| search STATUS_COUNT=2

In my case, Status can only take one of the 2 conditions (PASS/FAIL). In other case ">" operator could also be used.

[Runals]

That is similar to how I would approach it

...| stats dc(STATUS) by USERID

[theeven]

I am planning to group timechart per_day() at the end.

[lukejadamec]

How far back in time do you want to look?
For users that have both pass and fail, in the past hour, day, month?

[theeven]

not sure if i get it.

[yannK]

I hate to say that, but maybe a transaction may be useful.

mysearch PASS OR FAIL | transaction USERID | search PASS AND FAIL | table USERID

[HiroshiSatoh]

I did not think of this. The Helpful simple.

[lukejadamec]

What is the timeframe?