How to search for matching fields using 2 different host with same sourcetype?

[mrtolu6]

I'm looking to find matching field (lets call this field action) from 2 different host with the same sourcetype.

example Sourcetype=pan host=1 and host=2

I'm looking to create a ta table that would show the matching field for field action (I only want the matching field to generate result)

so if host 1 has action=allowed and host 2 has action=allowed. I want to create a table that would include the time, action, src, dest.

[DalJeanis · Accepted solution]

Try this...

index=foo sourcetype=pan (host=1 OR host=2)
| fields host action src dest
| eval time1=case(host="1",_time) 
| eval time2=case(host="2",_time) 
| eval src1=case(host="1",src) 
| eval src2=case(host="2",src) 
| eval dest1=case(host="1",dest) 
| eval dest2=case(host="2",dest) 
| stats values(time*) as time* values(dest*) as dest* values(src*) as src* values(host) as host by action
| where mvcount(host)>1