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"