How to pick dynamic date value and add as filter in query?

[Siri9996]

HI Team,

I am posting only part of the query to avoid confusion. the sourcetype logs data for past 10 days everyday I search for it because thats how its designed and I am fine with it. However, I want to filter only for yesterdays date for another purpose and want to use the same source. If I simply put 2023-05-16 as a filter in my search query I can get the data but  I want to automate it to fetch results everyday and every day I run, it should by default take yesterdays value. 

I simple want to print the value of result.yesterday in my search query like how i can mention 2023-05-16. Could you please let me know how I can achieve this? 

| makeresults | eval yesterday = strftime(relative_time(now(), "-1d@d"), "%Y-%m-%d")|fields - relative_time
$result.yesterday$
search index=infra source=*emr* sourcetype="aws:description" source="*emr_clusters" account_id="a0087"
| dedup Id
| rename Name as cluster_name,
Id as JobFlowId
Status.Timeline.CreationDateTime as cluster_creation_on,
Status.Timeline.ReadyDateTime as cluster_ready_on,
Status.Timeline.EndDateTime as cluster_terminate_on

Thanks in advance

[yuanliu]

A little confusing.  What's preventing you from using simple search terms like this?

 index=infra source=*emr* sourcetype="aws:description" source="*emr_clusters" account_id="a0087" earliest=-1d@d latest=-0d@d
| dedup Id
| rename Name as cluster_name,
Id as JobFlowId
Status.Timeline.CreationDateTime as cluster_creation_on,
Status.Timeline.ReadyDateTime as cluster_ready_on,
Status.Timeline.EndDateTime as cluster_terminate_on

[Siri9996]

hey, thanks for your response.

So this sourcetype scrapes data from our AWS console. AWS console displays data for last 10 days and this data is pused to splunk as is. 
When i make use of this source i get details of all resources that were present for past 10 days along with its creation date. I dont want anything except yesterdays date and if i want to manually load up and put 2023-05-17 i will only get results of that particular day because date is also part of the log thats getting pushed.

Now i have to automate it and i need dynamic value to be printed as a search filter like mentioned above.

Thanks,

Siri 

[yuanliu · Accepted solution]

First to clarify.  AWS console gives you 10-days' worth of data every day.  I get that.  Is this set of data in individual events, e.g., one event for 2023-05-17, another for 2023-05-16, and so on?  Or are all 10 days in one event?  Based on your original description, it is the former.  Correct?

If each event only contains data for one particular time, not all 10 days' worth, the best solution is to change ingestion to use that time as event time, i.e., _time.  Is there some reason to not do that?

If that is not possible, the next best thing is to look for a field that is already extracted that contains this timestamp.  Is there such a field in your raw event?

If by default, Splunk has not extracted this value into a field, the next best thing would be to add your own automatic extraction.

[Siri9996]

Hi @yuanliu

Missed this reply of yours. Thanks a lot. i have extracted the field and gave the value that i want.

[Siri9996]

Hi @yuanliu 

Hope I am clear.  Any leads would be of great help here. 

I have mentioned about the other way I tried. I am fine with either ways if its working. 

[Siri9996]

OR

I tried another way. When i created dashboard and called the $date$ value in my query it worked. So,  I changed it to report and called the field in the query that is part of ref="EMR-test report".  I just added $date$ in the query that is part of report. But somehow its not picking. please let me know if I am missing anything here.

<form version="1.1">
<search>
<query>| makeresults| eval today = strftime(relative_time(now(), "-1d@d"), "%Y-%m-%d")|fields - relative_time</query>
<done>
<set token="defaut_time">$result.today$</set>
</done>
</search>
<fieldset submitButton="false" autoRun="true">
<input type="text" token="date">
<label>date</label>
<default>$defaut_time$</default>
</input>
</fieldset>
<row>
<panel>
<table>
<search ref="EMR-test report"></search>
<option name="count">100</option>
<option name="dataOverlayMode">none</option>
<option name="drilldown">none</option>
<option name="percentagesRow">false</option>
<option name="refresh.display">progressbar</option>
<option name="rowNumbers">false</option>
<option name="totalsRow">false</option>
<option name="wrap">true</option>
</table>
</panel>
</row>
</form>

[Siri9996]

Hi All,

Any leads will be of great help