How do I join data from two indexes on a certain field?

[Chrisla9]

Hi,

Quite new to Splunk and need some help please.

I have an event which triggers an alert in Splunk and brings back almost all the information I need .... all but one bit which is in another index (Windows Event Log)

Here is the search I'm using.

index="my_records" action=Allowed user="*@*" | rename user TO "Account Name" | join  "Account Name" [search index=wineventlog] | table time_seen, "Account Name", category, server, action, src, record_id

I was hoping this would join the two indexes on the "Account Name" field, but it returns no rows 😞

If I remove the join then 4 rows are returned.

I know there will be a lot of rows in wineventlog, so the join is a one to many, I hope to fine tune this eventually by selecting just certain eventtypes, but right now I'd just like to see some data to confirm I'm on the right track.

Thanks in anticipation of your help

Chris

[somesoni2]

See the syntax and requirement for join command here.

http://docs.splunk.com/Documentation/Splunk/6.3.3/SearchReference/Join

You need to have field with same name in both the searches. Also, look at the alternative commands for join (above link topic 2). It's better to tune from start.

[Chrisla9]

Thanks, I've been trying to sort through this and I found the field issue. If I break down the search and start with the first one:

index="my_records" gw_action=Allowed user="@" |dedup record_id | table time_seen, category, crime_server, gw_action, src, record_id

This returns the records I want but doesn't have the information from the windows event log. So, I stayed with the join and set the join on the Logon_Account because both the wineventlog and my_records have this field

index="my_records" gw_action=Allowed user="@" |dedup record_id | join type=inner "Logon_Account" [search index=wineventlog] | table time_seen, Logon_Account, Source_Workstation, category, crime_server, gw_action, src, record_id

Now I'm getting the same fields returned but the extra fields I have added from the wineventlog (Logon_Account, Source_Workstation) are not populated in the table

Any ideas why this is please?

Thanks in anticipation

[somesoni2]

I don't see a common field between those two search (from the first search no field with name "Logon_Account" is returned). To be able to apply join (or any other alternative), you need a common field with exact same name. If the name is not same but the common field exists, you can rename the common field in one of the search to match other and do the join with common named field. I would also suggest to do some aggregation before you join (in both the search. This is a run anywhere example of how join can be done.

index=_internal earliest=-4h  | stats count by index sourcetype | join type=inner index [search index=_internal source=*metrics.log group=per_index_thruput earliest=-4h | stats sum(kb) as kb by series | rename series as index ] | table index sourcetype kb

So what you need is to find a field from my_records which has same values as field Logon_Account, rename the field from my_records to Logon_Account (as that's the final name you want in your result) and then do the join.

[Chrisla9]

Thanks for your help, I should have seen the field name issue!

I've managed to sort this out using:

index="my _records" gw_action=Allowed user="*@*" |rename user TO Logon_Account | join type=inner Logon_Account [search index=wineventlog] | table time_seen, Logon_Account, Source_Workstation, category, crime_server, gw_action, src, record_id

However some checks I made suggested the join was not working correctly as running separate searches in my_records and wineventlog showed users appearing in wineventlog which didn't appear in the joined search. Further investigation showed the Logon_Account was sometimes partly in lowercase and sometimes partly in uppercase (e.g. ACCOUNT.NAME@mydomain.net).

I have tried using eval ln=lower(Logon_Account) and then renaming the field to ln like so

index="my_records" gw_action=Allowed user="*@*" |rename user TO ln| join type=inner eval (ln=lower(Logon_Account) [search index=wineventlog] | dedup record_id| table time_seen, ln, Source_Workstation, category, crime_server, gw_action, src, record_id

or

index="seculert_records" gw_action=Allowed user="*@*" |rename user TO ln| join type=inner Logon_Account [search index=wineventlog |eval ln=lower(Logon_Account)] | dedup record_id| table time_seen, ln, Source_Workstation, category, crime_server, gw_action, src, record_id

But neither of these seems to work as the Source_Workstation is not populated where as it is for the first search but only for some records.

Any ideas where I'm going wrong please?

Thanks