How do I search 2 source types with matching data and display the values in a table?

[jscraig2006]

Greetings, I have 2 sourcetypes that I am matching PID. How do I table the remaining values that corresponds to the PIDs

sourcetype=Windows:Netstat
"Protocol": "TCP",
"LocalAddressIP": "127.0.0.1",
"LocalAddressPort": "65365",
"ForeignAddressIP": "127.0.0.1",
"ForeignAddressPort": "65364",
"State": "ESTABLISHED",
"PID": "1608"

sourcetype=Windows:Process
"ProcessName": "firefox",
"Id": 1608,
"Path": "D:\Program Files\Mozilla Firefox\firefox.exe",
"CPU": 241.7079494,
"UserName": "Domain\UserName"

index=windows sourcetype="Windows:NetStat" OR sourcetype="Windows:Process"
| stats values(PID) as NetstatPID, values(Id) as ProcessId
| mvexpand NetstatPID
| mvexpand ProcessId
| where ProcessId=NetstatPID
| table ProcessId, NetstatPID

I can match the ProcessId with NetstatPID, but i would like to table:
ForeignAddressIP
ForeignAddressPort
ProcessName
Path
UserName

Thanks in Advance

[inventsekar]

to better understand the coalesce command - from splunk blogs

Sample data:
Thu Mar 6 11:33:49 EST 2014 src_ip=1.1.1.1
Thu Mar 6 11:33:45 EST 2014 sourceip=8.1.2.3
Thu Mar 6 11:33:48 EST 2014 source_ip=1.1.1.0
Thu Mar 6 11:33:47 EST 2014 sip=1.1.1.199
Thu Mar 6 11:33:46 EST 2014 ip=
Thu Mar 6 11:33:46 EST 2014 ip=22.22.22.22

Here we are going to “coalesce” all the desperate keys for source ip and put them under one common name src_ip for further statistics.
For this example, copy and paste the above data into a file called firewall.log. Then use the oneshot command to index the file:
./splunk add oneshot “/your/log/file/firewall.log” –sourcetype firewall

sourcetype=firewall |eval src_ip = coalesce(src_ip,sourceip,source_ip,sip,ip)

https://www.splunk.com/blog/2014/03/21/search-command-coalesce.html

[jscraig2006 · Accepted solution]

index=windows sourcetype="Windows:NetStat" OR sourcetype="Windows:Process"
 | lookup dnslookup clientip as ForeignAddressIP OUTPUT clienthost as d_host
 | eval matchID=coalesce(PID,Id)
 | stats values(*) as * by matchID
 | table host, matchID, ProcessName, Path, d_host,State, UserName

Thanks to DalJeanis

[DalJeanis]

That's not doing what you think it is. It's just throwing all the values in a pot, then pulling out the ones that match, but they aren't still connected to anything. You need to connect the Process records to the NetStat records by their matching values.

Start with this --

 index=windows sourcetype="Windows:NetStat" OR sourcetype="Windows:Process"
| eval matchID=coalesce(ProcessId,NetstatPID)
| stats values(*) as * by matchID
| table matchID ...all your other fields you care about...

Technically, you could have used ProcessId OR NetstatPID instead of creating a new field, but it's clearer what is going on here this way.

[M28]

Hi, I have similar usecase ,But ProcessId is present both the source types.But i need to match the data ProcessId of source1 and NetStatPID of source2. Since field name(ProcessId) is common in both the sources ,it's not working fine.

Please suggest.

[jscraig2006]

Excellent, that is exactly what I needed. Works like a champ! Thank you!