Group events by a field and fetch those ones where a second field does not match a given value

[ariel123]

I have these events with CID which normally come as a pair of TranType Request and Response.
2017-12-04 09:45:01 CID=111 TranType=Response
2017-12-04 09:44:01 CID=111 TranType=Request
2017-12-04 09:42:01 CID=222 TranType=Request
2017-12-04 09:41:01 CID=333 TranType=Response
2017-12-04 09:39:01 CID=333 TranType=Request
2017-12-04 09:37:01 CID=444 TranType=Request

All I need is to display all the CID that are missing the corresponding TranType of Response such that the above will show as:
CID 222
CID 444

Appreciate your advice on this. Thanks!

[ariel123]

Hi renjith,
Thanks. This gives me a great hint on how to go about it.

Much appreciated!

[renjith_nair · Accepted solution]

@ariel123

Try

        <your index search> |stats count by CID|where count > 1

[harsmarvania57]

Hi @ariel123,

Based on sample data I have created 2 different queries. Can you please try both the queries ? First 6 lines are for sample data only.

| makeresults
| eval raw="2017-12-04 09:45:01 CID=111 TranType=Response:::2017-12-04 09:44:01 CID=111 TranType=Request:::2017-12-04 09:42:01 CID=222 TranType=Request:::2017-12-04 09:41:01 CID=333 TranType=Response:::2017-12-04 09:39:01 CID=333 TranType=Request:::2017-12-04 09:37:01 CID=444 TranType=Request"
| makemv delim=":::" raw
| mvexpand raw
| rename raw AS _raw
| rex "CID=(?<CID>\d+)\sTranType=(?<TranType>\w+)"
| transaction CID
| mvcombine TranType
| eval Result=if(TranType="Request Response",1,0)
| where Result=0

Second query using stats command

| makeresults
| eval raw="2017-12-04 09:45:01 CID=111 TranType=Response:::2017-12-04 09:44:01 CID=111 TranType=Request:::2017-12-04 09:42:01 CID=222 TranType=Request:::2017-12-04 09:41:01 CID=333 TranType=Response:::2017-12-04 09:39:01 CID=333 TranType=Request:::2017-12-04 09:37:01 CID=444 TranType=Request"
| makemv delim=":::" raw
| mvexpand raw
| rename raw AS _raw
| rex "CID=(?<CID>\d+)\sTranType=(?<TranType>\w+)"
| stats values(TranType) AS TranType by CID
| mvcombine TranType
| eval Result=if(TranType="Request Response",1,0)
| where Result=0

[ariel123]

Hi harsmarvania57,
Thanks for your reply. I never thought it would be a bit this complicated though 🙂 Is there any other way of accomplishing it without having to rely on delimiters specially the CID and TranType fields are available?

Cheers

[harsmarvania57]

Hi @ariel123,

As I mentioned earlier first 6 lines are used to generate sample data to create a query in my environment. So to simplify, your query will be

Using transaction

<your search> | transaction CID
 | mvcombine TranType
 | eval Result=if(TranType="Request Response",1,0)
 | where Result=0

Using stats

<your search> | stats values(TranType) AS TranType by CID
 | mvcombine TranType
 | eval Result=if(TranType="Request Response",1,0)
 | where Result=0

I prefer second one because transfroms will use more resources.