How to find out the record which has unique value

[jianyu75074]

I have records have 2 fields:
phone number result
1111 success
2222 success
2222 failed
3333 success
3333 failed
4444 failed

How to get the phone number which got failed ONLY. in this example, I want to get "4444"
if I search by result=failed, I got 2222,3333 and 4444. But I want to exclude 2222 and 3333 as they got "success"

is there any quick way to do that ? thanks!

[woodcock]

Like this:

... | stats values(result) AS results dc(result) AS result_count BY phone number
| where result_count="1" AND results="failed"

[gcusello · Accepted solution]

Hi @jianyu75074,
try this

| makeresults 
 | eval _raw=" number result
 1111 success
 2222 success
 2222 failed
 3333 success
 3333 failed
 4444 failed" 
 | multikv forceheader=1 
 | fields - linecount _raw _time
 | stats values(result) AS result dc(result) AS dc_result BY number
 | search result=failed AND dc_result=1

where the solution are the last two rows.

Ciao.
Giuseppe

[manjunathmeti]

Try this:

| mvcombine result | search result!="*success*"

Sample query:

| makeresults 
| eval _raw=" number result
1111 success
2222 success
2222 failed
3333 success
3333 failed
4444 failed" 
| multikv forceheader=1 
| fields - linecount _raw _time 
| mvcombine result 
| search result!="*success*"