How do I use join to add a field to results?

[bstreber]

I am working on a dashboard that shows the results based off of a MAC address. However, the address I need is on a different device in another index.

Both sets of data use an account ID, and I am attempting to use join to bring the two entries together I get a value of 00:00:00:00:00:00

index="ngv"  (NOT device.firmwareVersion=*XID* NOT device.firmwareVersion=*XI5*) device.ecmMacAddress=*
|join type=outer device.accountSourceId [search index=np_ngv device.accountSourceId AND device.ecmMacAddress=*] 
|search device.firmwareVersion="*XI6*"
|table device.firmwareVersion, device.ecmMacAddress

The results are a table of
arris_XI6_*.*p3s1 00:00:00:00:00:00

and, when looking at the data, everything is in tact except this.

[MuS · Accepted solution]

Hi bstreber,

give this a try:

index="ngv"  (NOT device.firmwareVersion=*XID* NOT device.firmwareVersion=*XI5*)  
OR ( index=np_ngv device.accountSourceId AND device.ecmMacAddress=* ) device.firmwareVersion="*XI6*" device.ecmMacAddress=*
| stats values(device.firmwareVersion) AS device.firmwareVersion values(device.ecmMacAddress) AS device.ecmMacAddress by device.accountSourceId

You might need to adapt the search a bit to match your actual events, but it give you an idea how it cane be done.
Another good source on this topic is this answer here https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo...

Hope this helps ...

cheers, MuS

[bstreber]

Hey MuS
Thanks for the reply. I forgot to mention that the record we are matching with it is the parent device not like for like. So this still shows results of 00's.

I am sorry for leaving that detail out.

[MuS]

Can you please provide 2 samples of each record ? Please make sure to remove sensitive data when doing so 😉

[bstreber]

The child device that generates the error log

{ [-]
assetClass: VOD

assetPosition: 482000
device: { [-]
account: #############

accountSourceId: ##################
billingAccountId: null

deviceId: ##########################
deviceSourceId: ##:##:##:##:##:##

deviceType: IpStb

ecmMacAddress: 00:00:00:00:00:00 (This is naturally 00::00)
firmwareVersion: arris_#####.2p10s1
ipAddress: #####################

macAddress: ##:##:##:##:##:##

make: Arris

model: AX061AEI

partner: shaw

postalCode: T4B3A4
receiverId: 2d3d2f2a-d3d7-4241-8af6-df0518646c49

utcOffset: null

}

eventTimestamp: 1543889330038

header: { [-]
hostname: tvxxre-c4-dt-d2u.x1.xcal.tv

money: null

timestamp: 1543889543807

uuid: fe6bbb18-338e-4f4f-9acf-63115a9882f9

}

kinesisRecordArrivalTime: Tue Dec 04 02:12:34 UTC 2018

playbackRate: 1

streamName: trickplay

trickplayName: PLAY

}

The Parent device that we need the ecmMacAddress from

{ [-]
attributes: { [+]
}

device: { [-]
account: #############

accountSourceId: ##################
billingAccountId: null

deviceId: ###################

deviceSourceId: ##:##:##:##:##:##

deviceType: IpGateway

ecmMacAddress: ##:##:##:##:##:##

firmwareVersion: TG3482SHW_##_PROD_sey

ipAddress: null

macAddress: ##:##:##:##:##:##

make: ARRIS

model: TG3482G

partner: shaw

postalCode: T2V0N1
receiverId: #####-#########-#######

utcOffset: null

}

eventCode: null

eventCount: null

header: { [+]
}

kinesisRecordArrivalTime: Tue Dec 04 00:43:30 UTC 2018

streamName: RDK-B

timestamp: 1543858253000

The intent is to monitor the child device for a error but we need to tie it to a physical location, that can be done via the MAC of the parent device.

[MuS]

try this:

| makeresults 
| eval child="{ [-] 
assetClass: VOD 
assetPosition: 482000 
device: { [-] 
account: ############# 
accountSourceId: ################## 
billingAccountId: null 
deviceId: ########################## 
deviceSourceId: ##:##:##:##:##:## 
deviceType: IpStb 
ecmMacAddress: 00:00:00:00:00::01
firmwareVersion: arris_#####.2p10s1 
ipAddress: ##################### 
macAddress: ##:##:##:##:##:## 
make: Arris 
model: AX061AEI 
partner: shaw 
postalCode: T4B3A4 
receiverId: 2d3d2f2a-d3d7-4241-8af6-df0518646c49 
utcOffset: null 
} 
eventTimestamp: 1543889330038 
header: { [-] 
hostname: tvxxre-c4-dt-d2u.x1.xcal.tv 
money: null 
timestamp: 1543889543807 
uuid: fe6bbb18-338e-4f4f-9acf-63115a9882f9 
} 
kinesisRecordArrivalTime: Tue Dec 04 02:12:34 UTC 2018 
playbackRate: 1 
streamName: trickplay 
trickplayName: PLAY 
}
" 
| append 
    [| makeresults 
    | eval parent="
{ [-] 
attributes: { [+] 
} 
device: { [-] 
account: ############# 
accountSourceId: ################## 
billingAccountId: null 
deviceId: ################### 
deviceSourceId: ##:##:##:##:##:## 
deviceType: IpGateway 
ecmMacAddress: 00:00:00:00:00:01 
firmwareVersion: TG3482SHW_##_PROD_sey 
ipAddress: null 
macAddress: ##:##:##:##:##:## 
make: ARRIS 
model: TG3482G 
partner: shaw 
postalCode: T2V0N1 
receiverId: #####-#########-####### 
utcOffset: null 
} 
eventCode: null 
eventCount: null 
header: { [+] 
} 
kinesisRecordArrivalTime: Tue Dec 04 00:43:30 UTC 2018 
streamName: RDK-B 
timestamp: 1543858253000" ] 
| rex field=child "ecmMacAddress: (?<ecmMacAddress>[\d{2}:+]+\d)" 
| rex field=parent "ecmMacAddress: (?<ecmMacAddress>[\d{2}:+]+\d)" 
| rex mode=sed field=ecmMacAddress "s/::/:/g" 
| stats values(*) AS * by ecmMacAddress

the important thing here is the regex with sed to normalise the ecmMacAddress

cheers, MuS

[bstreber]

Hey MuS,
I got it working!

The end result was as you suggested, rexing out the mac from the parent record allowed it to be related correctly.

This was the final search result

index=ngv $device_token1$ (NOT device.firmwareVersion=*XID* NOT device.firmwareVersion=*XI5*) 
|join type=left overwrite=false device.accountSourceId 
    [search index=np_ngv device.deviceType=IpGateway 
    |rex field=_raw "ecmMacAddress\": \"(?<DEVICE_MAC>\w{2}:\w{2}:\w{2}:\w{2}:\w{2}:\w{2})\"" |dedup device.accountSourceId]

[MuS]

Clad it worked in the end, but you should really NOT use join for such searches 😉 Sooner or later they will break and most likely you will not even notice it.

Try to use the base search like this:

( index=ngv $device_token1$ (NOT device.firmwareVersion=*XID* NOT device.firmwareVersion=*XI5*)) OR ( index=np_ngv device.deviceType=IpGateway )

and add the remaining SPL to it.

cheers, MuS

[bstreber]

To summarize what I am attempting to do
I have two records,
1 with a blank MAC address
2 with a MAC

I am trying to add the MAC from the second record to the first one