Getting Data In

Help with a join command

stwong
Communicator

Hi all,

I'd like to join 2 Windows events using instance_ID as following:

sourcetype="WinEventLog:security" EventCode=299 | join instance_ID [search sourcetype="WinEventLog:security" EventCode=500]

For fields common to both searches, only the one in subsearch can be retained e.g. EventCode=500 in above search.

Shall I rename such fields in either main or subsearch (except the ones used in join) before joining ?

Off-topic: will there be ways faster than join for the same query?

Sorry for the newbie question.

Thanks a lot.
Rgds
/ST Won

0 Karma
1 Solution

oda
Communicator

If you want action like a search sentence, you will need "rename".

If you want to group, there is a "transaction" command.
sourcetype="WinEventLog:security" | transaction instance_ID

Please try it.

View solution in original post

stwong
Communicator

Thanks for all your replies.

We're doing query to correlate some windows event, and keep all fields in all 3 related events. some of the fields in different events have the same field name.

event a:
field1 -> find event b
field2 -> find event c
field3
field 20...

event b:
field 1
field 10
field 11
field 20

event c:
field 2
field 15
field 16
field 20

Seems using join repeatedly + rename works.

Thanks again.
/st

0 Karma

oda
Communicator

If you want action like a search sentence, you will need "rename".

If you want to group, there is a "transaction" command.
sourcetype="WinEventLog:security" | transaction instance_ID

Please try it.

gcusello
SplunkTrust
SplunkTrust

Hi stwong,
at first check if you have upper and lower cases in instance_ID.

Often (not always!) you can use stats count instead join that it's faster, something like this

sourcetype="WinEventLog:security" (EventCode=299 OR EventCode=500)
| stats coun by instance_ID 
| where count>2

Bye.
Giuseppe

Sukisen1981
Champion

Hi,

When you do not specify a join type, by default it takes an inner join . so the results you are getting are from the common fields of instance_id...read more here, specifically the Venn diagram http://docs.splunk.com/Documentation/SplunkCloud/6.6.1/SearchReference/Join
And yes, looks like we can avoid the join what exactly is your requirement? no reason why we need a join from same index/ sourcetypes....we can probably do it better and faster using stats

lfedak_splunk
Splunk Employee
Splunk Employee

Hey @stwong, if they solved your problem, please don't forget to accept an answer! You can upvote posts as well. (Karma points will be awarded for either action.) Happy Splunking!

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...