How to find previous releases using current release?

[sangs8788]

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

[niketn · Accepted solution]

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]

[sangs8788]

Is there a command to get the second value or 3rd value alone. Not like all last 2 values ?

[niketn]

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.

[sangs8788]

Thanks. This really helped.

[niketn]

@sangs8788, To me the following reverse seems redundant |sort Release ASC | reverse, you can use |sort - Release instead.

[woodcock]

The most straightforward way is with autoregress:
https://docs.splunk.com/Documentation/SplunkCloud/latest/SearchReference/Autoregress

[sangs8788]

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 ?