How to compare two fields from two different source types?

[ahmed_aladwani]

Hello everybody,

This is actually my first post here so forgive me if I missed up or posted in the wrong section. I'm trying to compare/corelate two fields values from different source types and same index. Please find two sample of event I'm trying to work on.

1) sample of the first source type

index=wineventlog

sourcetype=Script:ListeningPorts

host=computer1

dest=172.*.*.*

dest_port=50000

process_id=151111

2) sample of the second source type

index=wineventlog

sourcetype=WinHostMon

source=process

host=computer1

Path=***.exe

Process=**.exe

ProcessId=151111 

I'm trying to corelate process_id and ProcessId fields to get the process field and make a count table. Sample output:

process_id   |  dest_port  |  count   |  host               |  Path      |  process

151111               50000              1          Computer1      **.exe      **.exe

I tried this query but it didn't give me the right result

index=wineventlog sourcetype=Script:ListeningPorts dest="172.*.*.*" host="Computer1"
| table host process_id dest, dest_port
| rename process_id as ProcessId

| join type=inner host ProcessId
[search index=wineventlog sourcetype=WinHostMon
| table  ProcessId dest_port host Path process]

[bowesmana · Accepted solution]

Try to avoid using join where possible, it has a number of limitations) - and in this case it's possible

(index=wineventlog sourcetype=Script:ListeningPorts dest="172.*.*.*" host="Computer1") OR (index=wineventlog sourcetype=WinHostMon host=Computer1)
| eval process_id=coalesce(process_id, ProcessId)
| stats values(dest) as dest values(Path) as Path values(process) as process by host process_id dest_port

The final stats statement will depend on what you want your output to be. This will probably be faster than a join and avoid its limitations