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
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
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
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
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
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
woodcock's is neater than mine - use his
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