topic Splunk Left Join in Splunk Search

Splunk Left Join

IRHM73 — Fri, 27 Nov 2015 14:34:06 GMT

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

I'm trying to return the following details:

Submission Date
Reference Number

With the following criteria:

Where the reference number has an auditType=FormSaved but does not have the auditType=FormSubmission (Because it is possible to have both auditTypes for the same reference number)

this is what I've put together so far, but I just can't get this to work.

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"
    | table submissiondate detail.referenceNumber
    | join type=left detail.referenceNumber[search index=main auditSource=for-frontend NOT 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"
    | table submissiondate detail.referenceNumber]

I just wondered whether someone could look at this please and let me know where I've gone wrong.

Many thanks and kind regards

Re: Splunk Left Join

jplumsdaine22 — Fri, 27 Nov 2015 16:16:03 GMT

You should be able to save yourself the join like so:

index=main auditSource=for-frontend auditType=* | stats values(*) as * by detail.referenceNumber |search NOT auditType=FormSubmission | fields generatedAt detail.referenceNumber

Depending on your data you may have multipll values in each cell now, but you should not have any detail.referenceNumbers that have an associated auditType=FormSubmission

Re: Splunk Left Join

woodcock — Fri, 27 Nov 2015 16:16:11 GMT

Like this:

 index=main auditSource=for-frontend
 | stats values(*) AS * BY detail.referenceNumber
 | where auditType="FormSaved" AND NOT auditType="FormSubmission"
 | eval generatedAt=strptime(mvindex(generatedAt, 0), "%Y-%m-%dT%H:%M:%S")
 | convert timeformat="%d/%b/%Y %H:%M" ctime(generatedAt)
 | rename generatedAt AS "submissiondate"
 | table submissiondate detail.referenceNumber

Re: Splunk Left Join

jplumsdaine22 — Fri, 27 Nov 2015 16:17:04 GMT

woodcock's is neater than mine - use his

Re: Splunk Left Join

IRHM73 — Wed, 02 Dec 2015 06:25:27 GMT

Hi @woodcock, thank you for taking the time to reply to my post and my apologies for not coming back to you sooner, but unfortunately, this is not extracting any information in the table.

But I have managed to find the answer here

Many thanks and kind regards

Chris

Re: Splunk Left Join

IRHM73 — Wed, 02 Dec 2015 06:32:10 GMT

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

I made some changes your query changing auditType=* to auditType=FormSaved, and although this does return the fields in the table, it still displays those submissions which have both "FormSaved" and "FormSubmission" for the same reference number.

But I have found the answer here

Many thanks and kind regards

Chris

Re: Splunk Left Join

IRHM73 — Wed, 02 Dec 2015 07:07:45 GMT

All,

After searching the Splunk answers I found the solution here

So my final solution is:

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"
     | table submissiondate detail.referenceNumber
     | search NOT[search index=main auditSource=for-frontend auditType=FormSubmission
     | table submissiondate detail.referenceNumber]

Many thanks and kind regards

Chris