Hi everybody,
I have a problem with a join between two indexes.
For example, I have 2 values: A and B, which are on both indexes. But, when I made my join, I saw only the value A and not B.
My code :
index="index1" | eval SERIAL_NUMBER=lower('SerialNum') | eval Index1_SN=SERIAL_NUMBER | join type=left SERIAL_NUMBER
[search index="index2" | eval SERIAL_NUMBER=lower('Serial Number') | eval Index2_SN=SERIAL_NUMBER] | eval Same_SN=if(like(Index1_SN,Index2_SN),1,0) | table Same_SN
It's returning some answers but sometimes it's putting a "0" while the value is on 2 indexes.
Thank you !
Max
Can you do this without the join command, perhaps? and as @Vijeta mentions, use =
instead of like
in the if
statement — unless they are not actually equal, in which case your join wouldn't have really worked in the first place. Some example data would be helpful. and just displaying a table of 1s and 0s might not tell you much? do you need to also display the serial number, as well?
index="index1" OR index="index2" | eval SERIAL_NUMBER=coalesce(lower('SerialNum'),lower('Serial Number')) | eval Index1_SN=if(index="index1",SERIAL_NUMBER,null()) | eval Index2_SN=if(index="index2",SERIAL_NUMBER,null()) |stats values(Index1_SN) as Index1_SN values(Index2_SN) as Index2_SN by SERIAL_NUMBER| eval Same_SN=if(Index1_SN=Index2_SN,1,0)
I also think if you're just trying to figure out if two serial numbers appear on both indexes and that's it, you could do:
index="index1" OR index="index2" | eval SERIAL_NUMBER=coalesce(lower('SerialNum'),lower('Serial Number')) |stats dc(index) as indexes by SERIAL_NUMBER
and be able to see if it's on 1 index or 2.
Can you do this without the join command, perhaps? and as @Vijeta mentions, use =
instead of like
in the if
statement — unless they are not actually equal, in which case your join wouldn't have really worked in the first place. Some example data would be helpful. and just displaying a table of 1s and 0s might not tell you much? do you need to also display the serial number, as well?
index="index1" OR index="index2" | eval SERIAL_NUMBER=coalesce(lower('SerialNum'),lower('Serial Number')) | eval Index1_SN=if(index="index1",SERIAL_NUMBER,null()) | eval Index2_SN=if(index="index2",SERIAL_NUMBER,null()) |stats values(Index1_SN) as Index1_SN values(Index2_SN) as Index2_SN by SERIAL_NUMBER| eval Same_SN=if(Index1_SN=Index2_SN,1,0)
I also think if you're just trying to figure out if two serial numbers appear on both indexes and that's it, you could do:
index="index1" OR index="index2" | eval SERIAL_NUMBER=coalesce(lower('SerialNum'),lower('Serial Number')) |stats dc(index) as indexes by SERIAL_NUMBER
and be able to see if it's on 1 index or 2.
Perfect that's work !
I have an other problem due to this modification...
If you want, I want to add the "Location" field, but sometimes I have more than 1 Location by SerialNumber (error I want to fix).
I'm trying to make a count, but it's doesn't work.
| stats count by SERIAL_NUMBER, Location | eval flag=if(Same_SN=1,1,if(Same_SN=0 and count>1,2,0))
Do you have an idea for how to count put 2 if I have more than 1 location ? Or have I to create a new "request" ?
Thanks,
Maxime.
try doing
index="index1" OR index="index2"
| eval SERIAL_NUMBER=coalesce(lower('SerialNum'),lower('Serial Number'))
| eval Index1_SN=if(index="index1",SERIAL_NUMBER,null())
| eval Index2_SN=if(index="index2",SERIAL_NUMBER,null())
|stats values(Index1_SN) as Index1_SN values(Index2_SN) as Index2_SN count dc(Location) as locations values(Location) as Location by SERIAL_NUMBER
| eval Same_SN=if(Index1_SN=Index2_SN,1,0)
|eval flag=case(Same_SN=1,1,Same_SN=0 AND locations>1,2,1=1,0)
Perfect, thank you again.
Normally it will be ok with this code.
Have a nice day !
If the answer provided solved your problem, could you please accept it so other readers can identify it is solved, award points, and essentially “close” the question, in a sense. Thanks!
What's your requirement here??
Any reason you using like and not = ?