Returning results that match one search but not another

[bnwww]

I have the following query working in SQL and am struggling to get a working Splunk query that will return the same result set. SQL as follows:

SELECT logA.ref, to_char(logA.edate, 'DD-MON-YYYY HH24:MI:SS') as edate
FROM logger logA
WHERE logA.event = 'string1'
AND sysdate > logA.adate

AND NOT EXISTS (SELECT logB.ref FROM logger logB
WHERE logB.event like 'string2'
AND logB.foo = 'bar'
AND logB.ref = logA.ref)
order by ref desc

Essentially I want a table of all events that match search1, but only if there is no events in search2 that have the same value for 'ref' as search1.

The data is a single sourcetype in a single index.

I've tried both subsearch and join but have not had any luck. Any tips would be appreciated.

[thambisetty]

I have come up with below, give a try:

index=yourindex event=string1 NOT (event=string2 foo=bar) 
| stats count by ref,edate 
| sort 0 - ref

[bnwww]

Thanks Thambisetty. This gives me a count of each event ref where the event does not have 'string2' in event and  'bar' in foo, but isn't quite what I'm after.

I need to return the set of events that match 'event=string1' *and also*, where the ref for those matched events is not found in any another event matching 'event=string2 and foo=bar'.

[thambisetty]

sample events:

{
"ref" : "a",
"somekey" : "somevalue",
"otherkey" : "othervalue",
"event" : "string1",
"edate" : "20100101114512345",
"adate" : "20100101123000",
"foo" : "bar"
}
{
"ref" : "b",
"somekey" : "somevalue",
"otherkey" : "othervalue",
"event" : "string1",
"edate" : "20100101114512345",
"adate" : "20100101123000",
"foo" : "sheep"
}

{
"ref" : "c",
"somekey" : "somevalue",
"otherkey" : "othervalue",
"event" : "string2",
"edate" : "20100101114512345",
"adate" : "20100101123000",
"foo" : "bar"
}
{
"ref" : "a",
"somekey" : "somevalue",
"otherkey" : "othervalue",
"event" : "string2",
"edate" : "20100101114512345",
"adate" : "20100101123000",
"foo" : "bar"
}

and search :

source="s5.txt"  sourcetype="_json"
| table event,ref,foo
| eval type=case(event=="string1","search1",event=="string2" AND foo=="bar","search2")
| eventstats dc(type) as dc_type by ref
| search type=search1 OR (dc_type=1 type=search2)

created a field called type and it will have two values : 1st value when event matches string1 and second value when event matches string2 and foo matches bar.
eventstats is to find matches of those two types in events based on ref

finally filter search is where we define what we need : type = search1 returns where event=string1 OR events that are matched with type search2 and their ref is not found in type search1.

hope this is what you are looking for. let me know if my understanding is wrong.

[bnwww]

Thank you thambisetty. Here is the slightly modified version that worked for my use case:

index=myindex sourcetype=mysourcetype
| table event,ref,foo
| eval type=case(event=="string1","search1",event=="string2" AND foo=="bar","search2")
| eventstats dc(type) as dc_type by ref
| search dc_type=1 AND type=search1

This returns only events that match the following criteria:

1. They have 'string1' as their event
2. They do not have a corresponding 'event=string2 AND foo=bar' event, with 'corresponding' in this case being determined by the ref field via  'as dc_type by ref'.

[bnwww]

Thanks Thambisetty, I think this is close.

"eventstats is to find matches of those two types in events based on ref"

Running your query I get all six expected events returned. Field 'dc_type' is 1.

Would I be correct in assuming that, once I have events of "event=string2 and foo=bar" that also have a ref value that matches an event with 'event=string1', I would see 'dc_type = 2' for that event?

[thambisetty]

@bnwww 
Would I be correct in assuming that, once I have events of "event=string2 and foo=bar" that also have a ref value that matches an event with 'event=string1', I would see 'dc_type = 2' for that event?

you are right , but you don't want to get this in results as per your first question right ?

[bnwww]

That's right. Just checking I had a correct understanding of how it worked.

[renjith_nair]

Assuming that event string is a field or could be extracted as a field, try

index="your index" sourcetype="your sourcetype" (event="string1" OR event="string2") ("other conditions including time")
|eventstats values(event) as events by ref
|where mvcount(events) <2 AND event="string1" 

Please provide some sample events after masking any sensitive data.

[bnwww]

'event' is a field, as is ref, edate, adate.

Running your suggested query without the 'where eventCount > 2' line, I have all six events of 'event=string1' returned. Note there are no 'event=string2' events in the data set (as we're trying to match NOT EXIST on that event type).

Running it with the 'where eventCount > 2' line gives me zero results.

I need to return the set of events that match 'event=string1' *and also*, where the ref for those matched events is not found in any another event matching 'event=string2 and foo=bar'.

[renjith_nair]

sorry there was a minor error where values should be replaced by dc.

Nevertheless, what do you mean by "there are no 'event=string2' " , are you not including in the search ?

What we are trying to do is,

• Collect all events (both string1 and string 2) using OR
• Count the presence of event by eventstats by ref -> This should give 2 for those ref which are part of both events (string1 and string2) and 1 for those ref which are part of only string1 OR string2
• Filter the events with where eventCount < 2 so that  only events with string1 OR string2
• Finally AND event=string1 should filter out all events with string2

[bnwww]

Thanks renjith_nair, I edited the search to use dc but still no joy.

When I say "there are no 'event=string2'" events, I mean that the second search - 'event=string2 AND foo=bar' - should not match any events, as there are none in the index that match this query.

What I am trying to do is find Splunk events that have "event=string1", *and that do not have* a corresponding separate event with 'event=string2 and foo=bar'. The 'corresponding' part comes through matching the value of the 'ref' field.

If there is a Splunk event with 'event=string1' and there is also a corresponding separate 'event=string2 and foo=bar' - corresponding as evidenced by both Splunk events having the same value in their 'ref' field - then we don't want to show that data in our results.

Think of it like looking for TCP 'syn' packets that don't have a corresponding 'ack' packet: we don't want to show tcp flows where we have SYN/ACK, just ones where we have SYN and no corresponding ACK.

I have six events that match 'event=string1' and zero events matching 'event=string2 AND foo=bar', so I would expect all six to be included in the count, but I'm seeing zero events even after changing from value to dc.

[renjith_nair]

Alright, could you please share the result of below

index="your index" sourcetype="your sourcetype" (event="string1" OR event="string2") ("other conditions including time")
|eventstats values(event) as eventList by ref
|eval dcEvent=mvcount(eventList)|table ref,event,eventList,dcEvent

[bnwww]

No results found

[bnwww]

Events have this format:

{
"ref" : "abcdefg001",
"somekey" : "somevalue",
"otherkey" : "othervalue",
"event" : "string1",
"edate" : "20100101114512345",
"adate" : "20100101123000",
"foo" : "bar"
}