Hello, I am trying to match values in two different columns to see if both data sets contain the same serial number for a cellphone part.
My search:
index=.. my search..... CellNumber="978499-" |dedup CellSerialNumber |table CellNumber CellSerialNumber
|appendcols [search ......CellNumber="978499-ALL" |dedup CellSerial |table CellNumber CellSerial]
| eval result=if(match(CellSerial,"%".CellSerialNumber."%"),"Contained", "Not Contained")
Results:
Looking deeper into the data I see there is CellSerialNumber values with their last 6 digits (-6digits) equal to the six digit CellSerial number, yet they are given a "Not contained" value.
Why is this??
I think you should do the following:
write a search - best without an appendcols, join, dedup, etc - where you get events where the field CellSerialNumber OR CellSerial has a value. After this, you create a new field like "testnumber" which will be filled with the last digits of CellSerialNumber OR with CellSerial, depending which the event has. Then you can create a chart values(CellSerialNumber) count(CellSerialNumber) values(CellSerial) count(CellSerial) by testnumber, so you'll get your needed information aggregated. Then you can check if both counters are greater than 1 and if so it is containing. You could also do a fillnull and search for 0 and say it es not containing 😉
if they are from different indexes (I suppose this is ment by different data sets?) you can also do something like "index=a OR index=b", so you'll get events out of both indexes
Perfect! I can do that, but each cell serial number starts with "978499-5-****" where the "978499-5- is constant for every CellSerialNumber where the **** is the unique identifier which would be equal to the Cell serial value if they are in both data sets.
I am trying the trim command: | eval CellserialNumber=ltrim(CellserialNumber," 978499-5-"), yet when doing this, it cut's of some of the numerical values when it returns the answer. Would there be a way to do this with the rex command?
I think you should do the following:
write a search - best without an appendcols, join, dedup, etc - where you get events where the field CellSerialNumber OR CellSerial has a value. After this, you create a new field like "testnumber" which will be filled with the last digits of CellSerialNumber OR with CellSerial, depending which the event has. Then you can create a chart values(CellSerialNumber) count(CellSerialNumber) values(CellSerial) count(CellSerial) by testnumber, so you'll get your needed information aggregated. Then you can check if both counters are greater than 1 and if so it is containing. You could also do a fillnull and search for 0 and say it es not containing 😉
I am trying your appraoch for the values and stats column chart, but how would I use rex to cutt of the first part of the CellserialNumber? Right now each cell serial number starts with "978499-5-****" where the "978499-5- is constant for every CellserialNumber where the **** is the unique identifier which would be equal to the Cell serial value if they are in both data sets.
I am trying the trim command: | eval CellserialNumber=ltrim(CellserialNumber," 978499-5-"), yet when doing this, it cut's of some of the numerical values when it returns the answer. Would there be a way to do this with the rex command?
| rex field=CellserialNumber "978499-5-(?<testnumber>.*)"
Thank you!!!!
Thank you for getting back to me! Unfortunately, I have to do an appendcols command for the CellSerialNumber and CellSerial field values are from different data sets.