Archive
Highlighted

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

Engager

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!

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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
0 Karma
Highlighted

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

Engager

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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.

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

@ariel123

Try

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

View solution in original post

0 Karma
Highlighted

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

Engager

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

Much appreciated!

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.