topic Re: Can you help me with the following query using the join command? in Splunk Search

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

MaximeMoreau — Fri, 26 Oct 2018 14:50:30 GMT

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

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

Vijeta — Fri, 26 Oct 2018 19:27:37 GMT

Any reason you using like and not = ?

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

somesoni2 — Fri, 26 Oct 2018 19:35:46 GMT

What's your requirement here??

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

cmerriman — Fri, 26 Oct 2018 20:28:36 GMT

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.

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

MaximeMoreau — Mon, 29 Oct 2018 16:51:08 GMT

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.

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

cmerriman — Mon, 29 Oct 2018 21:18:23 GMT

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)

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

MaximeMoreau — Tue, 30 Oct 2018 08:28:14 GMT

Perfect, thank you again.

Normally it will be ok with this code.

Have a nice day !

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

cmerriman — Tue, 30 Oct 2018 13:13:00 GMT

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!