Calculate Between Current Date and 30 Days

[IRHM73]

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

I'm trying to put together a search which extracts records in Splunk which are greater than 30 days from the current date using the field generatedAt as the field whereby to calculate the 30 days.

Using a post I found here I've put together the following search:

index=main auditSource=for-frontend auditType=FormSaved
     | eval epochevent=strptime(generatedAt, "%Y/%m/%d") | eval epoch30daysago=relative_time(now(), "-30d@d" ) | where epoch30daysago>=epochevent
     | table detail.referenceNumber
     | search NOT[search index=main auditSource=for-frontend auditType=FormSubmission]

The search runs and correctly doesn't display any events, but to test this, I used the time period as 1 hour because I've entered some test submissions within this time period, but the search still isn't returning any events.

Could someone possibly look at this please and offer some guidance on where I've gone wrong?

Many thanks and kind regards

Chris

[IRHM73 · Accepted solution]

Hi, I have been able to solve the problem with this query:

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(), "-10m@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]
          | table detail.referenceNumber submissiondate c_time

Many thanks and kindest regards

Chris

[jplumsdaine22]

Hi Chris,

where epoch30daysago>=epochevent will only return events that are more than 30 days old. If you change the time picker to 1 hour but don't change this expression you will still only see events that are older than 30 days, so your events from the last hour will not display.

If you want to test why not change the epoch30daysago eval to use -1h@h ?

Also your subsearch is very broad. You might want to restrict the fields that get returned eg

search NOT[search index=main auditSource=for-frontend auditType=FormSubmission | fields detail.referenceNumber]

Remember you can always check what a subsearch does by looking aty the remoteSearch field in the job inspector

[IRHM73]

Hi, thank you for replying to my post and for the advice on the subsearch.

I did change the eval to -1h@h, and it still did not return the desired records.

Many thanks and kind regards

Chris

[jplumsdaine22]

If you're not getting events then either

1. The epochtime is being calculated incorrectly
2. They are getting excluded by the subsearch

index=main auditSource=for-frontend auditType=FormSaved | eval epochevent=strptime(generatedAt, "%Y/%m/%d") | eval epoch1hago=relative_time(now(), "-1h@h" ) | table detail.referenceNumber  epochevent epoch30daysago

This search should tell you whats happening with the epochdates - if it looks correct then your search at the end is filtering them out.

[IRHM73]

Hi, thank you for coming back to me with this.

I've tried the query you kindly provided i.e without the subsearch and it is not returning anything in the fields "epochevent" and "epoch30daysago", so it must be something wrong with the initial query I was using.

Many thanks and kind regards

Chris

[jplumsdaine22]

ah sorry typo there in my search. Should be

index=main auditSource=for-frontend auditType=FormSaved | eval epochevent=strptime(generatedAt, "%Y/%m/%d") | eval epoch1hago=relative_time(now(), "-1h@h" ) | table detail.referenceNumber  epochevent epoch1hago

But given you're getting no values epochevent my guess is that the generatedAt field is not actually UNIX time. Can you post some sample data?

[krish3]

Why there is a not in second search with main index??

are you sending your events to some other index?

search NOT[search index=main auditSource=for-frontend auditType=FormSubmission]

[IRHM73]

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

The reason behind the "NOT Search" is that for each detail.referenceNumber it is possible to have two auditTypes "Form Saved" and "Submission".

The query will only extract those submissions which only have "Form Saved" as the auditType.

I hope this helps.

Many thanks and kind regards

Chris