Splunk Search

Calculate Between Current Date and 30 Days

IRHM73
Motivator

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

0 Karma
1 Solution

IRHM73
Motivator

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

View solution in original post

0 Karma

IRHM73
Motivator

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

0 Karma

jplumsdaine22
Influencer

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
Motivator

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

0 Karma

jplumsdaine22
Influencer

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.

0 Karma

IRHM73
Motivator

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

0 Karma

jplumsdaine22
Influencer

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?

0 Karma

krish3
Contributor

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]

0 Karma

IRHM73
Motivator

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...