Lookup file contains release number and its start date. The fields in lookup file are Release and Production (start date of release)
I have a query which lists me Release, its time range
|inputlookup ReleaseCalender.csv |sort Release ASC | reverse |streamstats current=f last(Production) as latest|rename Production as earliest|eval timenow=now()| convert mktime(*) timeformat="%d/%m/%Y"| table Release, earliest, latest
The result would look something like below
Release Earliest Latest
7.4 1500076800 1504915200
7.3 1494633600 1500076800
7.2 1489795200 1494633600
7.1 1484956800 1489795200
6.6 1478908800 1484956800
6.5 1473465600 1478908800
I am able to get the current release date using timenow() . Below query gives me current release based on current date. Say 7.3
|inputlookup ReleaseCalender.csv |sort Release ASC | reverse |streamstats current=f last(Production) as latest|rename Production as earliest|eval timenow=now()| convert mktime(*) timeformat="%d/%m/%Y"| where earliest=timenow | table Release, earliest, latest
How do I fetch the previous release(7.2), 2nd previous release(7.1) or 3rd previous release(6.6) details based on current release query ?
Thanks
Try the following to your search. I have added an eval case to decide the releaseFlag
as Current
, Previous
or Next
based on current time.
| inputlookup ReleaseCalender.csv
| sort Release ASC
| reverse
| streamstats current=f last(Production) as latest
| rename Production as earliest
| eval timenow=now()
| eval releaseFlag=case(timenow>latest,"Previous",timenow<=latest AND timenow>earliest,"Current",timenow<earliest,"Future")
| search releaseFlag="Previous"
| fieldformat earliest=strftime(earliest,"%d/%m/%Y")
| fieldformat latest=strftime(latest,"%d/%m/%Y")
| fieldformat timenow=strftime(timenow,"%d/%m/%Y")
| head 2
Based on how many releases you want to pull you can set head command. Above example will pull two previous releases through | head 2
. If you want to test you can change the timenow to static values for specific release | eval timenow=1489795201
etc and remove the | head 2
pipe.
[Updated to add fieldformat to format Date field as dd/mm/YYYY]
Try the following to your search. I have added an eval case to decide the releaseFlag
as Current
, Previous
or Next
based on current time.
| inputlookup ReleaseCalender.csv
| sort Release ASC
| reverse
| streamstats current=f last(Production) as latest
| rename Production as earliest
| eval timenow=now()
| eval releaseFlag=case(timenow>latest,"Previous",timenow<=latest AND timenow>earliest,"Current",timenow<earliest,"Future")
| search releaseFlag="Previous"
| fieldformat earliest=strftime(earliest,"%d/%m/%Y")
| fieldformat latest=strftime(latest,"%d/%m/%Y")
| fieldformat timenow=strftime(timenow,"%d/%m/%Y")
| head 2
Based on how many releases you want to pull you can set head command. Above example will pull two previous releases through | head 2
. If you want to test you can change the timenow to static values for specific release | eval timenow=1489795201
etc and remove the | head 2
pipe.
[Updated to add fieldformat to format Date field as dd/mm/YYYY]
Is there a command to get the second value or 3rd value alone. Not like all last 2 values ?
Option 1- Using streamstats for counting all iterations
Add the following after the releaseFlag, it will add a counter to each releaseFlag.
| streamstats count as iteration_counter by releaseFlag reset_on_change=true
You can then change your search condition to match both releaseFlag and iteration_counter. For example following will get 2nd iteration counter for Previous Release.
| search releaseFlag="Previous" AND iteration_counter=2
Option 2- Using accum for counting selected iterations
After searching Previous
releases, set the counter to 1
| search releaseFlag="Previous"
| eval iteration_counter=1
Use accum
command to get a cumulative counter
| accum iteration_counter
Then filter to specific iteration using search
| search iteration_counter=2
Please check the options and confirm. Kindly up vote if this helps.
Thanks. This really helped.
@sangs8788, To me the following reverse seems redundant |sort Release ASC | reverse
, you can use |sort - Release
instead.
The most straightforward way is with autoregress
:
https://docs.splunk.com/Documentation/SplunkCloud/latest/SearchReference/Autoregress
How do i get one release number at a time. because with this i see lists 2nd 3rd and 4th all together. Also say above shared query result 7.3 which itself 2nd value in lookup file, i want 2nd, 3rd value after 7.3. How do i get from the selected value ?