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
Try
<your index search> |stats count by CID|where count > 1
Hi renjith,
Thanks. This gives me a great hint on how to go about it.
Much appreciated!
@ariel123
Try
<your index search> |stats count by CID|where count > 1
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
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
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.