Splunk Search

problem with join

Mahmoud
Engager

this is the query, so i'm still a baby in this world (so I'm sorry if there is a dummy mistakes that might drive you crazy when you read this query). However, I'm trying to Join the Source Process Id (from event code 10) with the Process Id ( from event code 1) and then print the command line, I tried to use `type=inner` but it gave me nothing which is wired, because when I look for the first query there is result and the same for the inner query.  


 

index="main" sourcetype="WinEventLog:Sysmon" EventCode=10 lsass SourceImage="C:\\Windows\\system32\\rundll32.exe"
| join left=L right=R type=left where L.SourceProcessId=R.ProcessId
[search EventCode=1 lsass "C:\\Windows\\system32\\rundll32.exe"]
| table L.TargetImage, R.ProcessId, R.commandLine

 




Labels (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @Mahmoud,

Don't use join: Splunk isn't a relational Database, it's a search engine, use stats BY the common key, something like this:

index="main" sourcetype="WinEventLog:Sysmon" lsass SourceImage="C:\\Windows\\system32\\rundll32.exe" EventCode IN (10,1)
| eval ProcessId=coalesce(SourceProcessId,ProcessId)
| stats 
   values(TargetImage) AS TargetImage
   values(commandLine) AS commandLine
   BY ProcessId

if you want to add an additional condition, e.g. only the ProcessId present in both the EventCodes, you can ann an additional final constrain.

index="main" sourcetype="WinEventLog:Sysmon" lsass SourceImage="C:\\Windows\\system32\\rundll32.exe" EventCode IN (10,1)
| eval ProcessId=coalesce(SourceProcessId,ProcessId)
| stats 
   values(TargetImage) AS TargetImage
   values(commandLine) AS commandLine
   dc(EventCode) AS EventCode_count
   BY ProcessId
| where EventCode_count=2
| fields - EventCode_count

Ciao.

Giuseppe

View solution in original post

gcusello
SplunkTrust
SplunkTrust

Hi @Mahmoud,

Don't use join: Splunk isn't a relational Database, it's a search engine, use stats BY the common key, something like this:

index="main" sourcetype="WinEventLog:Sysmon" lsass SourceImage="C:\\Windows\\system32\\rundll32.exe" EventCode IN (10,1)
| eval ProcessId=coalesce(SourceProcessId,ProcessId)
| stats 
   values(TargetImage) AS TargetImage
   values(commandLine) AS commandLine
   BY ProcessId

if you want to add an additional condition, e.g. only the ProcessId present in both the EventCodes, you can ann an additional final constrain.

index="main" sourcetype="WinEventLog:Sysmon" lsass SourceImage="C:\\Windows\\system32\\rundll32.exe" EventCode IN (10,1)
| eval ProcessId=coalesce(SourceProcessId,ProcessId)
| stats 
   values(TargetImage) AS TargetImage
   values(commandLine) AS commandLine
   dc(EventCode) AS EventCode_count
   BY ProcessId
| where EventCode_count=2
| fields - EventCode_count

Ciao.

Giuseppe

Get Updates on the Splunk Community!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...