How to search for a field value that has appeared as the value of some other field?

[the_wolverine]

I have a simple case where I want to see if the value of one field has shown up as the value of another field.

rec=dns a=3.4.2.3
rec=dns a=5.1.2.3
rec=tcpsession server=5.1.2.3
rec=tcpsession server=3.4.2.3
rec=tcpsession server=6.3.2.4
rec=tcpsession server=5.6.7.8

intended search: (show me rec=tcpsession events in which server=(some value that has shown up as the value of "a" in a "rec=dns" event)

intended results: rec=tcpsession server=5.1.2.3 rec=tcpsession server=3.4.2.3

transaction seems to be one way, but i'm looking for a simpler search structure that doesn't require rex.

[sideview]

If the limits of the subsearch are an issue you can do a similar thing just with stats. There's probably a less heavyhanded way to do this and still use stats, but if you really just want the list of servers at the end of the day, this will do it too:

rename a as server | stats values(rec) as multi_valued_rec by server | where multi_valued_rec="dns"

rename the a values to server. We do this so we can end up with uniform 'server' fields in the results. Then our stats can do a 'by server', so we'll end up with one ip address per row, and a multivalued field called 'multivalued_rec'. Where (and even stats itself) are perfectly capable of dealing with multivalued fields so we just filter the ip list with a where clause and there you go.

[gkanapathy]

probably want ... | where multi_valued_rec="dns" AND multi_valued_rec="tcpsession" at the end in this case. and just to be explicit, the initial search term before the rename would be rec=dns OR rec=tcpsession | ...

[gkanapathy · Accepted solution]

You should use subsearch:

rec=tcpsession [ search rec=dns | dedup a | fields a | rename a as server ]

This will work for the number of distinct values of a up to the limit of the format command, which by default is 100.