So, to start with, I have a table like this.
Person role Time
abc DBA 15-5-2017
abc SE 15-5-2017
xyz blahblah 14-2-2016
. . .
. . .
. . .
. . .
the table looks like this and I have two entries for each person, and the table delivers detail about what role the person had before becoming a DBA.
Now, I want to take the timestamp lets say, 15-5-2017, and iterate down the Time column, and match another row with the same timestamp. I want to do this for each result in the result set I obtain for:
index=something event_name="some other thing" event_type="yet another thing" |table prsnl_name, role, event_name, event_type, _time | where role like "%DB%"
this gives me a table only containing the DBAs or DBCs.
Lastly, I want result sets which look like:
Person role Time
abc DBA 15-5-2017
abc SE 15-5-2017
xyz DBA 14-2-2016
xyz ST 14-2-2016
pqr DBA 21-8-2011
pqr AC 21-8-2011
. . .
Thank you so much.
-Snipedown
This would serve your request...
index=something event_name="some other thing" event_type="yet another thing"
| stats values(role) as role by _time, prsnl_name
| sort 0 - _time
| where role like "%DB%"
| dedup prsnl_name
| mvexpand role
| eval sortme = if(match(role,"%DB%"),1,2)
| sort 0 prsnl_name sortme
...but personally, I would do something more like this...
index=something event_name="some other thing" event_type="yet another thing"
| stats values(role) as role by _time, prsnl_name
| sort 0 - _time
| where role like "%DB%"
| dedup prsnl_name
| eval roleDB=mvfilter(match(role,"%DB%"))
| eval rolePrior=mvfilter(NOT match(role,"%DB%"))
| table _time, prsnl_name roleDB rolePrior
Notes -
1) | reverse
is probably more efficient than | sort 0 - _time
, but I wanted the purpose of the statement to be clear.
2) If you only want people who are currently DBs, then switch the order of | dedup
and | where role
This would serve your request...
index=something event_name="some other thing" event_type="yet another thing"
| stats values(role) as role by _time, prsnl_name
| sort 0 - _time
| where role like "%DB%"
| dedup prsnl_name
| mvexpand role
| eval sortme = if(match(role,"%DB%"),1,2)
| sort 0 prsnl_name sortme
...but personally, I would do something more like this...
index=something event_name="some other thing" event_type="yet another thing"
| stats values(role) as role by _time, prsnl_name
| sort 0 - _time
| where role like "%DB%"
| dedup prsnl_name
| eval roleDB=mvfilter(match(role,"%DB%"))
| eval rolePrior=mvfilter(NOT match(role,"%DB%"))
| table _time, prsnl_name roleDB rolePrior
Notes -
1) | reverse
is probably more efficient than | sort 0 - _time
, but I wanted the purpose of the statement to be clear.
2) If you only want people who are currently DBs, then switch the order of | dedup
and | where role
The second solution works butter smooth. I needed both the rows to appear in my result set. Would it be possible to modify the search to do that instead?
Thank you for your effort.
Much appreciated, DalJeanis.
Not sure why the other wouldn't work too, but this will use the structure from the second to produce the multiline output.
index=something event_name="some other thing" event_type="yet another thing"
| stats values(role) as role by _time, prsnl_name
| sort 0 - _time
| where role like "%DB%"
| dedup prsnl_name
| eval fan = mvappend("current","prior")
| mvexpand fan
| eval role=if(fan="current",mvfilter(match(role,"%DB%")),mvfilter(NOT match(role,"%DB%")))
| table _time, prsnl_name role
Try:
index=something event_name="some other thing" event_type="yet another thing" [search index=something event_name="some other thing" event_type="yet another thing" role="*DB" | stats count by prsnl_name | fields prsnl_name ] | table prsnl_name, role, event_name, event_type, _time | sort prsnl_name
Or for something different:
index=something event_name="some other thing" event_type="yet another thing" | eval relevant=if(like(role,"%DB%),1,null()) | stats values(relevant) as relevant values(role) as role, values(_time) as _time by prsnl_name | where isnotnull(relevant) | fields - relevant
Are you sure that the search you gave me is doing this?
"Now, I want to take the timestamp lets say, 15-5-2017, and iterate down the Time column, and match another row with the same timestamp."
Because I don't see a time based comparison to fetch rows with same timestamps.