How to join one interesting field from another index?

[jhilton90]

I have Advanced Hunting logs that are being ingested into Splunk and one of the indexes is DeviceNetworkEvents, which has the following fields 

- properties.DeviceName (The name of the device)

- properties.InitiatingProcessAccountName (The name of the user's account

Now, I want to format the data into a table that displays the device name and the account name, but Account Name fields are displayed as null.

However, in other indexes like "DeviceEvents" the properties.InitiatingProcessAccountName field and properties.DeviceName are both present and have actual values in them.

So I was going to use the "join" command but not sure if it would work or not.

Hoping someone can shine some light

[gcusello]

Hi @jhilton90,

use join only if you haven't any other solution because it's a very slow command.

You have other solutions to correlate events from different data sources.

So, you should try something like this:

index=DeviceNetworkEvents OR index=DeviceEvents
| rename 
   properties.DeviceName AS DeviceName
   properties.InitiatingProcessAccountName AS InitiatingProcessAccountName
| stats values(InitiatingProcessAccountName) AS InitiatingProcessAccountName BY DeviceName

In few words, you have to put both the searches in the main search and correlate data using as key the command field (DeviceName)

Ciao.

Giuseppe

[jhilton90]

Hi Giuseppe,

Thanks for the reply, the only issue I've ran into now is that other fields like properties.LocalIP and properties.RemoteIP that are present in both indexes come in as null.

So I've modified the initial query a bit like so:

index=advanced_hunting category=AdvancedHunting-DeviceNetworkEvents OR index=advanced_hunting category=AdvancedHunting-DeviceEvents
| rename 
   properties.DeviceName AS DeviceName
   properties.InitiatingProcessAccountName AS AccountName
   properties.LocalIP AS LocalIP
   properties.Protocol AS Protocol
   properties.RemoteIP AS RemoteIP
| table AccountName, DeviceName, LocalIP, RemoteIP

The AccountName and DeviceName display values but for LocalIP and RemoteIP just display as null

[gcusello · Accepted solution]

Hi @jhilton90,

at first don't use "-" in index names because Splunk reads it as the subtraction operator and you should use quotes for the index names, use underscore "_".

then use parenthesis to divide conditions for index otherwise condirions are use in sequence.

at least, if you use table instead stats, you'll have many empty values for deviceName and duplicated  values for accountname, so use stats to group them as I hinted defining the field to use as correlation key (deviceName?):

(index=advanced_hunting category=AdvancedHunting_DeviceNetworkEvents) OR (index=advanced_hunting category=AdvancedHunting_DeviceEvents)
| rename 
   properties.DeviceName AS DeviceName
   properties.InitiatingProcessAccountName AS AccountName
   properties.LocalIP AS LocalIP
   properties.Protocol AS Protocol
   properties.RemoteIP AS RemoteIP
| stats 
   values(DeviceName) AS DeviceName
   values(LocalIP) AS LocalIP
   values(RemoteIP) AS RemoteIP
   BY AccountName

 Ciao.

Giuseppe

[jhilton90]

Wow that worked! Thanks very much!

[gcusello]

Hi @jhilton90,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉