filter by only failed events which never passed

[vishwasgopala]

INFO [] () process='isValid', result='failed', dacNumber='[DAC_111_646]',  accountNumber=1122333

INFO [] () process='isValid', result='failed', dacNumber='[DAC_111_777]',  accountNumber=1122333

INFO [] () process='isValid', result='failed', dacNumber='[DAC_111_888]',  accountNumber=1122333

INFO [] () process='isValid', result='success', dacNumber='[DAC_111_777]',  accountNumber=1122333

INFO [] () process='isValid', result='success', dacNumber='[DAC_111_999]',  accountNumber=1122333

INFO [] () process='isValid', result='success', dacNumber='[DAC_111_646]',  accountNumber=1122333

How to get all failed dacNumber which never passed.  In the above example it should give me DAC_111_777. Please help.

[ashvinpandey]

@vishwasgopala Try adding the below query after you index=<<anything>>

| rex field=_raw "result\=\'(?P<result>\w+).*dacNumber\=\'\[(?P<dacNumber>.*?)\]"
| search result="failed"
| dedup dacNumber
| table dacNumber result

Also if this reply helped you in solving your problem an up-vote would be appreciated.

[richgalloway · Accepted solution]

Select the most recent event for each dacNumber then discard the successful ones.  The remainder will be failures.  In SPL:

... | dedup dacNumber
| where result = failed