Splunk Search

publish yesterday's data from today's date

martin61
Engager

I want to get QID list from yesterday’s published data.  For that I'm using PUBLISHED_DATETIME field with yesterday’s date. The date format for that field result is in GMT format (2005-11-11T08:00:00Z). For example, I’m running this search on may 4th, but I need to get QID fields with published date as 05/03/2022. (May 3rd)

|table QID PUBLISHED_DATETIME

Labels (1)
0 Karma

bowesmana
SplunkTrust
SplunkTrust

You could either do a simple string match where clause on the PUBLISHED_DATETIME field or comvert it to epoch and compare.

| where match(PUBLISHED_DATETIME, "^2022-05-03")

of course if you want to compare always against yesterday, then you would need to make a field with yesterday's date. Here's an example that creates some dummy dates and then searches for yesterday

| makeresults count=20
| eval today=strftime(now(), "%Y-%m-")
| eval day=tonumber(strftime(now(), "%d")) - 1
| eval PUBLISHED_DATETIME=today."0".(random() % day + 1)
| eval yesterday=strftime(relative_time(now(), "-d@d"), "^%F")
| where match(PUBLISHED_DATETIME, yesterday)

So, you need to format a string like eval yesterday=... and then the where clause will string match.

Alternatively you can do with with epoch comparison with numeric comparison after parsing the PUBLISHED_DATETIME and calculating start end end times for yesterday, e.g. 

| makeresults count=20
| eval today=strftime(now(), "%Y-%m-")
| eval day=tonumber(strftime(now(), "%d")) - 1
| eval PUBLISHED_DATETIME=today."0".(random() % day + 1)."T08:00:00Z"
| eval pdate=strptime(PUBLISHED_DATETIME,"%FT%TZ")
| eval yesterday_start=relative_time(now(), "-d@d")
| eval yesterday_end=relative_time(now(), "@d")
| where pdate>=yesterday_start AND pdate<yesterday_end

You can run both of these example searches that will show you how it works

 

 

0 Karma
Get Updates on the Splunk Community!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...