How to get the previous savedsearch by a given savedsearch from the same user?

[splunkrocks2014]

I used the following query to get a list of savedsearches by a given user:

index=_internal user="John Doe"  | table _time user savedsearch_name

The results shows as

    _time                     user                  savedsearch_name
    2017-04-10 13:25:48.706 John Doe                savedsearch_xyz
    2017-04-10 13:25:48.625 John Doe                savedsearch_abc
    2017-04-10 13:25:47.314    John Doe                savedsearch_123

I tried to acomplsh is to get the previous savedsearch_name by given the savedsearch as well. For example,

index=_internal user="John Doe"  [sub search] | where  savedsearch_name="savedsearch_xyz"

The expected result looks like the following:

        _time                     user      current_savedsearch_name    previous_savedsearch_name
        2017-04-10 13:25:48.706 John Doe    savedsearch_xyz             savedsearch_abc

Thanks

[DalJeanis · Accepted solution]

 index=_internal user="John Doe"  
 | table _time user savedsearch_name
 | sort 0 _time
 | streamstats current=f last(savedsearch_name) as priorsearch_name by user
 | where  savedsearch_name="savedsearch_xyz"
 | reverse
 | dedup savedsearch_name

---

...and a run-anywhere data sample ...

| makeresults 
|eval mydata="2017-04-10 13:25:48.706,John Doe,savedsearch_xyz!!!!2017-04-10 13:25:48.625,John Doe,savedsearch_abc!!!!2017-04-10 13:25:47.314,John Doe,savedsearch_123!!!!2017-04-10 13:05:48.706,John Doe,savedsearch_xyz!!!!2017-04-10 13:05:47.314,John Doe,savedsearch_notme"
| makemv delim="!!!!" mydata 
| mvexpand mydata
| makemv delim="," mydata
| eval _time=strptime(mvindex(mydata,0),"%Y-%m-%d %H:%M:%S.%3Q")
| eval user=mvindex(mydata,1)
| eval savedsearch_name=mvindex(mydata,2) 
| table _time user savedsearch_name

 | sort 0 _time
 | streamstats current=f last(savedsearch_name) as priorsearch_name by user
 | where  savedsearch_name="savedsearch_xyz"
 | reverse
 | dedup savedsearch_name

...resulting in ...

_time                    user        savedsearch_name     priorsearch_name
2017-04-10 13:25:48.706  John Doe    savedsearch_xyz      savedsearch_abc

[splunkrocks2014]

Hi Daljeanis, appreciated for your help. This is exactly I am looking for!!!

[woodcock]

Like this:

index=_internal user=* savedsearch_name=*
| dedup _time user savedsearch_name
| streamstats count BY user
| where count<=2

And then either this:

| stats list(*) AS * BY user

Or this (exactly what you asked):

| stats latest(_time) AS _time earliest(savesearch_name) AS previous_savedsearch_name latest(savedsearch_name) AS current_savedsearch_name BY user

[splunkrocks2014]

Hi Woodcock, sorry I didn't state clearly from my request. What I tried to accomplish is like this

index=_internal user="John Doe"  [sub search] | where  savedsearch_name="savedsearch_xyz"

I am thinking that the "sub search" is able to get a list of current and previous savedsearches

Thanks,

[woodcock]

I do not get it at all. Let's skip the how and focus on the what. My answer gives you exactly your stated desire. If that's not really what you need, then do describe exactly what you need and do not get into the weeds of how.

[splunkrocks2014]

Thank you, woodcock. I updated my initial request.

[somesoni2]

Give this a try

index=_internal user=* savedsearch_name=* | dedup _time user savedsearch_name |table _time user savedsearch_name | dedup 2 user
| stats max(_time) as _time first(savedsearch_name) as current_savedsearch_name last(savedsearch_name) as previous_savedsearch_name by user

[splunkrocks2014]

Hi somesoni2, the query returns the same value from current and previous savedsearch names.

[DalJeanis]

If the user runs the same saved search twice in a row, it would have the same name in both places. Remove _time from the first dedup and see if that fixes it.

[somesoni2]

How about this?

index=_internal sourcetype=scheduler user=* savedsearch_name=* | dedup user savedsearch_name | dedup 2 user
 | stats max(_time) as _time first(savedsearch_name) as current_savedsearch_name last(savedsearch_name) as previous_savedsearch_name by user

[splunkrocks2014]

The query works; however, if I wanted to search a specific savedsearch_name and its previous savedsearch_name, the query doesn't work. What I tried to do is like this (of course, my query doesn't work)

index=_internal sourcetype=scheduler user="John Doe"  
| dedup user savedsearch_name 
| dedup 2 user
| stats max(_time) as _time first(savedsearch_name) as current_savedsearch_name last(savedsearch_name) as previous_savedsearch_name by user
| where current_savedsearch_name="savedsearch_xyz"