Anything wrong is with join query?

[kyoung2580]

I have inserted the same data in splunk and mysql.

Splunk query:

index=sysmon EventCode=3
| stats count as sysmon_count by img_name
| sort sysmon_count desc
| join type=inner img_name
    [ search index=winevent EventCode=5156
    | stats count as winevt_count by img_name ]
| table img_name, sysmon_count, winevt_count

Result:

img_name	sysmon_count	winevt_count
splunkd.exe	3697	3701
python3.exe	614	3071
streamfwd.exe	614	1228
chrome.exe	211	910
svchost.exe	36	97
System	22	34
taskhostw.exe	1	2
whale_update.exe	1	1

Mysql query:

select a.img_name, a.sysmon_count, b.winevt_count
from (
	select img_name, count(*) as sysmon_count
	from sysmon
	where eventcode = 3
	group by img_name
	order by sysmon_count desc) a
join (
	select img_name, count(*) as winevt_count
	from winevent
	where eventcode=5156
	group by img_name
	order by winevt_count desc) b
on a.img_name = b.img_name

Result:

img_name	sysmon_count	winevt_count
splunkd.exe	3697	3701
python3.exe	614	3071
streamfwd.exe	614	1228
chrome.exe	211	910
svchost.exe	36	97
System	22	34
RuntimeBroker.exe	2	2
taskhostw.exe	1	2
backgroundTaskHost.exe	1	1
OfficeClickToRun.exe	1	1
POWERPNT.EXT	1	1
MsMpEng.exe	1	1
CEIP.exe	1	1
whale_update.exe	1	1

Add:

I have found the cause of join problem. Mysql is case-insensitive but splunk is case-sensitive. It can get the same result as mysql when change the join field(img_name) to lowercase. 

index=sysmon EventCode=3
| eval img_name = lower(img_name)
| stats count as sysmon_count by img_name
| sort sysmon_count desc 
| join type=inner img_name
    [ search index=winevent EventCode=5156
    | eval img_name = lower(img_name)
    | stats count as winevt_count by img_name ]

[kyoung2580]

@manjunathmeti It works! This is very helpful. Thank you very much.:)

[manjunathmeti · Accepted solution]

hi @kyoung2580,
You don't need a join command, you can try this,

index=sysmon EventCode=3 OR EventCode=5156
| stats count(eval(EventCode=3)) as sysmon_count, count(eval(EventCode=5156)) as winevt_count by img_name
| sort sysmon_count desc
| table img_name, sysmon_count, winevt_count

If you still find some img_name values missing then they might not be there in your index OR you might need to increase the search time range depending on when the data is collected in Splunk and event timestamps.

index=sysmon EventCode=3 OR EventCode=5156 earliest=1
| stats count(eval(EventCode=3)) as sysmon_count, count(eval(EventCode=5156)) as winevt_count by img_name
| sort sysmon_count desc
| table img_name, sysmon_count, winevt_count

If this reply helps you, an upvote/like would be appreciated.