Can you help me with the following query using the join command?

[MaximeMoreau]

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

[cmerriman · Accepted solution]

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.

[MaximeMoreau]

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.

[cmerriman]

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)

[MaximeMoreau]

Perfect, thank you again.

Normally it will be ok with this code.

Have a nice day !

[cmerriman]

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!

[somesoni2]

What's your requirement here??

[Vijeta]

Any reason you using like and not = ?