problem with join

[Mahmoud]

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

[gcusello · Accepted solution]

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