Getting Data In

Help with a join command

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

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

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

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

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

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

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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!