Splunk Search

date generation is wrong using gentimes

Communicator

Hi,

I am using below code snippet to generate previous 12 months.

| gentimes start=-365 end=-0 increment=0d
| eval Date=strftime(starttime,"%Y-%m-%d")
|sort Date | reverse
| table Date

I see two problems here,

  1. its generating 2017-11-05 twice
  2. its missing 2018-03-11

What am I missing? Please help.

Tags (3)
1 Solution

SplunkTrust
SplunkTrust

@angelinealex, you can use map to get the first day of last year i.e. 01/01/2017 and then pass to gentimes to get all the dates. Finally after reverse, you can retain only first 365 results using head 365

| makeresults
| eval startDateLastYear=strftime(relative_time(_time,"-1y@y"),"%m/%d/%Y")
| fields - _time
| map search="| gentimes start=\"$startDateLastYear$\" end=-0 increment=0d 
| eval Date=strftime(starttime,\"%Y-%m-%d\") 
| sort Date 
| reverse 
| table Date
| head 365"

You can also try the following dashboard code on similar lines to set the token for first day of last year i.e. 01/01/2017 and then pass on the token to search query with gentimes

<dashboard>
  <label>gentimes last one year daylight saving issue</label>
  <search>
    <query>| makeresults
| eval startDateLastYear=strftime(relative_time(_time,"-1y@y"),"%m/%d/%Y")
| fields - _time
    </query>
    <earliest>-1s@s</earliest>
    <latest>@s</latest>
    <done>
      <set token="tokStartDate">$result.startDateLastYear$</set>
    </done>
  </search>
  <row>
    <panel>
      <table>
        <search>
          <query>| gentimes start="$tokStartDate$" end=-0 increment=0d 
| eval Date=strftime(starttime,"%Y-%m-%d") 
| sort Date 
| reverse 
| table Date
| head 365
          </query>
        </search>
      </table>
    </panel>
  </row>
</dashboard>

Please try out and confirm.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

SplunkTrust
SplunkTrust

@angelinealex, you can use map to get the first day of last year i.e. 01/01/2017 and then pass to gentimes to get all the dates. Finally after reverse, you can retain only first 365 results using head 365

| makeresults
| eval startDateLastYear=strftime(relative_time(_time,"-1y@y"),"%m/%d/%Y")
| fields - _time
| map search="| gentimes start=\"$startDateLastYear$\" end=-0 increment=0d 
| eval Date=strftime(starttime,\"%Y-%m-%d\") 
| sort Date 
| reverse 
| table Date
| head 365"

You can also try the following dashboard code on similar lines to set the token for first day of last year i.e. 01/01/2017 and then pass on the token to search query with gentimes

<dashboard>
  <label>gentimes last one year daylight saving issue</label>
  <search>
    <query>| makeresults
| eval startDateLastYear=strftime(relative_time(_time,"-1y@y"),"%m/%d/%Y")
| fields - _time
    </query>
    <earliest>-1s@s</earliest>
    <latest>@s</latest>
    <done>
      <set token="tokStartDate">$result.startDateLastYear$</set>
    </done>
  </search>
  <row>
    <panel>
      <table>
        <search>
          <query>| gentimes start="$tokStartDate$" end=-0 increment=0d 
| eval Date=strftime(starttime,"%Y-%m-%d") 
| sort Date 
| reverse 
| table Date
| head 365
          </query>
        </search>
      </table>
    </panel>
  </row>
</dashboard>

Please try out and confirm.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

Communicator

saved search just worked like champ. Thank you very much for all your help

0 Karma

SplunkTrust
SplunkTrust

Glad you found this working mean while I have submitted a feedback on Splunk Docs that daylight saving start and end dates are not working as expected for gentimes command!

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Communicator

Great, Please let me also know if you get any response for the feedback 🙂

0 Karma

SplunkTrust
SplunkTrust

Splunk team has reopened an existing JIRA ticket for this issue JIRA # SPL-146219.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Communicator

Cool. Thank you for letting me know.

0 Karma

SplunkTrust
SplunkTrust

@angelinealex can you try the following search and check results?

| gentimes start=-365 end=-0 increment=1d 
| eval Date=strftime(starttime,"%Y-%m-%d") 
| reverse
| fields Date
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Communicator

I get the same result for the above query as well.

I figured out the problem. but looking for the solution.
Because of day light saving changes its duplicating 5th Nov, 2017 and missing 11th Mar, 2018.

I am in PST timezone.

0 Karma

SplunkTrust
SplunkTrust

@angelinealex First off... "good catch". I think you have found a bug with gentimes command. You should add bug tag to this question and also report to Splunk Support if you have valid Splunk Entitlement.

Meanwhile, you can try the following search:

| gentimes start=-365 end=-0 increment=1d
| eval Date=strftime(starttime,"%Y-%m-%d")
| fields Date
| dedup Date
| append [| makeresults | eval Date="2018-03-11" | fields - _time]
| sort Date
| reverse
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Communicator

This gives my correct result. But i cant hard code March 11 since it changes every year.

0 Karma

Communicator
    | gentimes start=1/16/17 end=-0 increment=0d 
    | eval Date=strftime(starttime,"%Y-%m-%d") 
    | sort Date 
    | reverse 
    | table Date 

The above sample code gives me the proper result.. i mean without duplicate and missing date from 1/16/17. But how do i pass the dynamic date to start?
Daily I should get previous 12 months dates.

0 Karma

SplunkTrust
SplunkTrust

Following two work (Starting with Jan/Feb)

| gentimes start="01/16/2017" end=-0 increment=0d

Or

| gentimes start="02/16/2017" end=-0 increment=0d

But following does not (starting with March). If you try previous 12 month dates you will have a date in March which gives same issue as what you have noticed. Definitely a BUG with gentimes command.

| gentimes start="03/16/2017" end=-0 increment=0d
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Contributor

Hi!

I checked the same code but it's giving the correct result. It's printing 2017-11-05 once and 2018-03-11 does exist.

Thanks.

0 Karma

Communicator

ahh ok.. then why i am getting wrong result with duplicate date and missing date. Any guess?

0 Karma

Champion

Your query seems ok. I run that query and it giving me proper 365 results.

0 Karma

Communicator

ahh ok.. then why i am getting wrong result with duplicate date and missing date. Any guess?

0 Karma

Champion

Try rerunning search, if still it gives duplicates use dedup command.

Also use below query :

| gentimes start=-365 end=-0 increment=0d | eval Date=strftime(starttime,"%Y-%m-%d") |sort - Date| table Date
0 Karma

Communicator

The above query resulted the same. If I use dedup command then i am not getting duplicate date. But still its missing 2018-03-11.

0 Karma