Splunk Search

How do I iterate and match values within a column of my search result?

Path Finder

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

0 Karma
1 Solution

SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

Path Finder

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.

0 Karma

SplunkTrust
SplunkTrust

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
0 Karma

Contributor

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
0 Karma

Path Finder

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.

0 Karma