Splunk Search

Join not working

kavyamohan
Explorer
index="event"
| rex field=Macaddress mode=sed "s/(.{2})/\1-/g s/-$//"
 | rename Macaddress as "macAddress" 
| eval SessionTime_epoch = strptime(SessionTime, "%Y-%m-%dT%H:%M:%S.%3NZ")
 | eval SessionTime = strftime(SessionTime_epoch, "%Y-%m-%d %H:%M:%S")
| join macAddress type=outer
    [inputlookup nctdata.csv]
| rename cubicleNo as "work_station" | join work_station type=outer
    [inputlookup somaster.csv]
|table Username,macAddress,Session,SessionTime,SystemName,cubliceNo,vlanNo, c_occupiedclient

My query is something like this. But i am not getting the results correctly. I want the fields as I have mentioned above. I want Username,macAddress,Session,SessionTime,SystemName from "event" file and join macAddress in nctdata and get these columns in nctdata file: cubliceNo,vlanNo and join work_station in nctdata and somaster file and take these column in somaster:c_occupiedclient.

Tags (2)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi kavyamohan,
the join command is a very slow solution and has the limit of 50,000 results because there's a subsearch.
In your case you want to do a join with a lookup, to do this you don't need of join and you can use the lookup command that's like a join.
Don't think to Splunk as a DB, it's different!

Instead, try to use a different approach:

index="event"
| rex field=Macaddress mode=sed "s/(.{2})/\1-/g s/-$//"
| rename Macaddress as "macAddress" 
| eval SessionTime_epoch = strptime(SessionTime, "%Y-%m-%dT%H:%M:%S.%3NZ"), SessionTime = strftime(SessionTime_epoch, "%Y-%m-%d %H:%M:%S")
| lookup nctdata.csv macAddress OUTPUT cubicleNo 
| rename cubicleNo as "work_station" 
| lookup somaster.csv work_station OUTPUT cubliceNo vlanNo
| table Username macAddress Session SessionTime SystemName cubliceNo vlanNo c_occupiedclient

In addition, when you use the inputlookup command you have to always use the pipe char "|".

Bye.
Giuseppe

View solution in original post

gcusello
SplunkTrust
SplunkTrust

Hi kavyamohan,
the join command is a very slow solution and has the limit of 50,000 results because there's a subsearch.
In your case you want to do a join with a lookup, to do this you don't need of join and you can use the lookup command that's like a join.
Don't think to Splunk as a DB, it's different!

Instead, try to use a different approach:

index="event"
| rex field=Macaddress mode=sed "s/(.{2})/\1-/g s/-$//"
| rename Macaddress as "macAddress" 
| eval SessionTime_epoch = strptime(SessionTime, "%Y-%m-%dT%H:%M:%S.%3NZ"), SessionTime = strftime(SessionTime_epoch, "%Y-%m-%d %H:%M:%S")
| lookup nctdata.csv macAddress OUTPUT cubicleNo 
| rename cubicleNo as "work_station" 
| lookup somaster.csv work_station OUTPUT cubliceNo vlanNo
| table Username macAddress Session SessionTime SystemName cubliceNo vlanNo c_occupiedclient

In addition, when you use the inputlookup command you have to always use the pipe char "|".

Bye.
Giuseppe

wmyersas
Builder

You can even simplify the eval further:

| eval SessionTime = (strftime(strptime(SessionTime, "%Y-%m-%dT%H:%M:%S.%3NZ"), "%Y-%m-%d %H:%M:%S")
0 Karma

kavyamohan
Explorer

Thank you so much it worked:)

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...