Splunk Left Join

[IRHM73]

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

[IRHM73 · Accepted solution]

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

[woodcock]

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

[IRHM73]

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

[jplumsdaine22]

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

[jplumsdaine22]

woodcock's is neater than mine - use his

[IRHM73]

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