topic Join not working in Splunk Search

Join not working

kavyamohan — Wed, 30 Sep 2020 02:21:04 GMT

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.

Re: Join not working

gcusello — Fri, 27 Sep 2019 12:31:58 GMT

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

Re: Join not working

wmyersas — Fri, 27 Sep 2019 15:04:31 GMT

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")

Re: Join not working

kavyamohan — Fri, 04 Oct 2019 04:51:13 GMT

Thank you so much it worked:)