- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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)
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Perfect, thank you again.
Normally it will be ok with this code.
Have a nice day !
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

What's your requirement here??
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Any reason you using like and not = ?
