Splunk Search

date generation is wrong using gentimes

angelinealex
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

niketnilay
Legend

@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

niketnilay
Legend

@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

angelinealex
Communicator

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

0 Karma

niketnilay
Legend

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

angelinealex
Communicator

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

0 Karma

niketnilay
Legend

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

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

angelinealex
Communicator

Cool. Thank you for letting me know.

0 Karma

niketnilay
Legend

@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

angelinealex
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

niketnilay
Legend

@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

angelinealex
Communicator

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

0 Karma

angelinealex
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

niketnilay
Legend

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

MousumiChowdhur
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

angelinealex
Communicator

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

0 Karma

p_gurav
Champion

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

0 Karma

angelinealex
Communicator

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

0 Karma

p_gurav
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

angelinealex
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
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!