Splunk Search

Splunk Left Join

IRHM73
Motivator

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

Tags (2)
0 Karma
1 Solution

IRHM73
Motivator

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

View solution in original post

0 Karma

IRHM73
Motivator

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

0 Karma

woodcock
Esteemed Legend

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
Motivator

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

0 Karma

jplumsdaine22
Influencer

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

0 Karma

jplumsdaine22
Influencer

woodcock's is neater than mine - use his

0 Karma

IRHM73
Motivator

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...