Splunk Search

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

Communicator

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

Tags (3)
0 Karma
1 Solution

Communicator
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

View solution in original post

0 Karma

Champion

to better understand the coalesce command - from splunk blogs

Sample data:
Thu Mar 6 11:33:49 EST 2014 srcip=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 srcip 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 srcip = coalesce(srcip,sourceip,sourceip,sip,ip)

alt text

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

0 Karma

Communicator
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

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

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.

0 Karma

Communicator

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

0 Karma