Splunk Search

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

jscraig2006
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

jscraig2006
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

inventsekar
Ultra Champion

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)

alt text

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

0 Karma

jscraig2006
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

0 Karma

DalJeanis
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

M28
Explorer

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.

 

0 Karma

jscraig2006
Communicator

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...