Splunk Search

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

snipedown21
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

DalJeanis
Legend

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

DalJeanis
Legend

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

0 Karma

snipedown21
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

DalJeanis
Legend

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

knielsen
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

snipedown21
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
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...