Splunk Search

How to find previous releases using current release?

sangs8788
Communicator

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

0 Karma
1 Solution

niketn
Legend

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]

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

View solution in original post

niketn
Legend

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]

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

sangs8788
Communicator

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

0 Karma

niketn
Legend

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.

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

sangs8788
Communicator

Thanks. This really helped.

0 Karma

niketn
Legend

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

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

woodcock
Esteemed Legend
0 Karma

sangs8788
Communicator

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 ?

0 Karma
Get Updates on the Splunk Community!

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more with ITSI’s ...

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more! Faster Time to ValueManaging and ...

New Release | Splunk Enterprise 9.3

Admins and Analyst can benefit from:  Seamlessly route data to your local file system to save on storage ...

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...