Removing string values and then matching field values?

[ichesla1111]

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??

[skramp]

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

[ichesla1111]

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?

[skramp · Accepted solution]

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 😉

[ichesla1111]

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?

[skramp]

| rex field=CellserialNumber "978499-5-(?<testnumber>.*)"

[ichesla1111]

Thank you!!!!

[ichesla1111]

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.