Splunk Search

How do I count events for the past 45 days when the start date is the 15th of each month?

amilavsky
Engager

Please Help !!!!

I am trying to create an SPL query to count events for the past 45 days from the start date the 15th of each month, so for instance, January 15 through the past 45 days.

index=okta  earliest=-45d@d latest="1/15/2019:24:00:00" 
| stats min(_time) as lt max(_time) as ft dc(targets{}.login) as unique_user_count
| convert ctime(lt) as start_time
| convert ctime(ft) as end_time
| table start_time,end_time,unique_user_count
| append
[ search index=okta earliest=-45d@d latest="12/15/2018:24:00:00" 
| stats min(_time) as lt max(_time) as ft dc(targets{}.login) as unique_user_count
| convert ctime(lt) as start_time
| convert ctime(ft) as end_time
| table start_time,end_time,unique_user_count
]
  1. I'm trying to avoid hardcode latest time.
  2. earliest=-45d@d does not calculate the correct date from latest="1/15/2019:24:00:00". it's showing Dec7 instead of Dec2
  3. start_time duplicated data from first row
Tags (1)
0 Karma
1 Solution

bangalorep
Communicator

Hello,
- You could use tokens for the latest time if you want to add this in a dashboard
- You could refer to this documentation for snapping the time https://docs.splunk.com/Documentation/Splunk/7.2.3/SearchReference/SearchTimeModifiers
- Could you please elaborate a bit more on this problem?

View solution in original post

0 Karma

mstjohn_splunk
Splunk Employee
Splunk Employee

hi @amilavsky,

Did you have a chance to check out bangalorep 's answer? If it worked, please resolve this post by approving it! If your problem is still not solved, keep us updated so that someone else can help ya.

0 Karma

bangalorep
Communicator

Hello,
- You could use tokens for the latest time if you want to add this in a dashboard
- You could refer to this documentation for snapping the time https://docs.splunk.com/Documentation/Splunk/7.2.3/SearchReference/SearchTimeModifiers
- Could you please elaborate a bit more on this problem?

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...