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
Legend

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
Legend

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!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...