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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...