Why is my "NOT [subsearch...]" still returning events that should be excluded from results?

[IRHM73]

Hi, I wonder whether someone may be able to help me please.

I've put together the following search which:

• For each detail.referenceNumber,
• Check to see whether it has the auditType "Form Saved" and,
• Not the auditType "FormSubmission".

• If these apply extract the information using the latest event for the "detail.referenceNumber"

  index=main auditSource=for-frontend auditType=FormSaved
  | eval generatedAt=strptime(generatedAt, "%Y-%m-%dT%H:%M:%S")|convert timeformat="%d/%b/%Y %H:%M" ctime(generatedAt)
  | rename generatedAt As "submissiondate"
  | rename detail.name As "fullname"
  | head 1
  | table submissiondate detail.referenceNumber
  | search NOT[search index=main auditSource=for-frontend auditType=FormSubmission
  | table submissiondate detail.referenceNumber]
  The search works well, but I am now wanting to adapt this and amalgamate multiple fields into one, so I have put together the following:

  index=main auditSource=for-frontend auditType=FormSaved
  | eval generatedAt=strptime(generatedAt, "%Y-%m-%dT%H:%M:%S")|convert timeformat="%d/%b/%Y %H:%M" ctime(generatedAt)
  | rename generatedAt As "submissiondate"
  | rename detail.name As "fullname"
  | rename detail.referenceNumber As "refno"
  | head 1
  | eval Full_Details= "Ref No: ".refno.", Fullname: ".fullname.", Date and Time of submission: ".submissiondate
  | makemv delim=", " Full_Details
  | table Full_Details
  | search NOT[search index=main auditSource=for-frontend auditType=FormSubmission
  | table Full_Details]
  The problem I have is that when I run this search, it is extracting those submissions with an auditType of "FormSubmission" which it shouldn't and I've no idea why.

Could someone have a look at this please and let me know where I've gone wrong.

Many thanks and kind regards

Chris

[IRHM73 · Accepted solution]

Hi, I just wanted to let you know that I've managed to create a working query with:

index=main auditSource=for-frontend auditType=FormSaved
| eval generatedAt=strptime(generatedAt, "%Y-%m-%dT%H:%M:%S")|convert timeformat="%d/%b/%Y" ctime(generatedAt)
| rename generatedAt As "submissiondate"
| eval testtime=relative_time(now(), "-1m@m" ) 
| eval c_time=strftime(testtime,"%d/%b/%Y")
| where c_time=submissiondate
| search NOT[search index=main auditSource=for-frontend auditType=FormSubmission
| table detail.referenceNumber]
| head 1
| rename detail.referenceNumber As "refno"
| rename detail.name As "fullname"
| rename detail.90DayExpirationDate As "expirydate"
| eval expirydate=strptime(expirydate, "%Y-%m-%dT%H:%M:%S")|convert timeformat="%d/%b/%Y" ctime(expirydate)
| eval Full_Details= "Ref No: ".refno.", Fullname: ".fullname.", Date and Time of submission: ".submissiondate.", Expiry Date: ".expirydate
| makemv delim=", " Full_Details
| table Full_Details

Many thanks and kind regards

Chris

[polymorphic]

If I understand this correctly, this should work:

index=main auditSource=for-frontend auditType=FormSaved auditType!=FormSubmission
          | eval generatedAt=strptime(generatedAt, "%Y-%m-%dT%H:%M:%S")|convert timeformat="%d/%b/%Y %H:%M" ctime(generatedAt)
          | rename generatedAt As "submissiondate"
          | rename detail.name As "fullname"
          | rename detail.referenceNumber As "refno"
          | head 1
          | eval Full_Details= "Ref No: ".refno.", Fullname: ".fullname.", Date and Time of submission: ".submissiondate
          | makemv delim=", " Full_Details
          | table  Full_Details

Or if you need the 'auditType=FormSubmission' then:

    index=main auditSource=for-frontend (auditType=FormSaved OR auditType=FormSubmission)
.....

And use eval(if(auditType="FormSaved", 'iftrue', 'iffalse'))

Subsearches are a pain, and introduce extra load on your searchhead. Avoid using subsearches if possible.

[IRHM73]

Hi many thanks for taking the time to reply to my post.

Unfortunately the auditType!=FormSubmission element wasn't working, but please see my answer with the solution.

Any yes 'subsearches' are a pain!

Many thanks and kind regards

Chris

[jplumsdaine22]

Subsearches can be tricky things. It's worth checking what your subsearch results look like. You can see this in the remote search section of the job inspector. I suspect it is returning NOT (), which then becomes search NOT NOT (), which will not exclude any results for you.

I recommend you have a read of the documentation on subsearch formatting: http://docs.splunk.com/Documentation/Splunk/6.3.1/Search/Changetheformatofsubsearchresults

[IRHM73]

Hi, thank you for coming back to me with this. I've read the documentation and to be honest I'm none the wiser. as I understand it the second search is run first, so I would have thought that the formatting should be on the first part of my query. I'm not sure whether I've understood this correctly?

Many thanks and kind regards

Chris

[jplumsdaine22]

Hi Chris, what I mean is this.

Your subsearch is search index=main auditSource=for-frontend auditType=FormSubmission | table Full_Details

From what I can tell, Full_Details is a field that you created earlier in the search pipeline. The subsearch cannot actually access that field, so none of the events returned by your search will have any value for the field. So the result of the subsearch will be an empty set ()

Post the output from the job inspector as I suggested if this is not making sense and I'll be able to help further

[javiergn]

Have you tried running the second search without the makemv line or adding it at the bottom?

[IRHM73]

Hi, thank you for taking the time to reply to my post.

Yes I have moved this to the bottom of the second query and unfortunately it still displays the information.

Many thanks and kind regards

Chris

[jplumsdaine22]

Why did you swap the position of "auditType=FormSubmission" between the two queries?

[IRHM73]

Hi, many thanks for replying.

It was a typo, now corrected.

Many thanks and kind regards

Chris