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.
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
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
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")
Thank you so much it worked:)