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

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

jscraig2006 — Tue, 06 Jun 2017 21:11:53 GMT

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

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

DalJeanis — Tue, 06 Jun 2017 22:27:58 GMT

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.

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

jscraig2006 — Tue, 06 Jun 2017 23:54:02 GMT

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

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

jscraig2006 — Tue, 06 Jun 2017 23:58:39 GMT

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

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

inventsekar — Tue, 29 Sep 2020 14:19:23 GMT

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

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

M28 — Wed, 30 Nov 2022 09:04:24 GMT

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.