Help with a join command

[stwong]

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

[stwong]

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

[oda · Accepted solution]

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]

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]

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]

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!