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
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
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
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
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.
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.
Excellent, that is exactly what I needed. Works like a champ! Thank you!