topic Re: Anything wrong is with join query? in Splunk Search

Anything wrong is with join query?

kyoung2580 — Thu, 18 Mar 2021 15:06:23 GMT

I have inserted the same data in splunk and mysql.

Splunk query:

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.

Re: Anything wrong is with join query?

manjunathmeti — Thu, 18 Mar 2021 07:30:42 GMT

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.

Re: Anything wrong is with join query?

kyoung2580 — Thu, 18 Mar 2021 10:35:35 GMT

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