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
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
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
Hi Daljeanis, appreciated for your help. This is exactly I am looking for!!!
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
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,
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.
Thank you, woodcock. I updated my initial request.
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
Hi somesoni2, the query returns the same value from current and previous savedsearch names.
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.
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
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"