Splunk Search

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

MaximeMoreau
New Member

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

Tags (2)
0 Karma
1 Solution

cmerriman
Super Champion

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.

View solution in original post

cmerriman
Super Champion

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
New Member

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.

0 Karma

cmerriman
Super Champion

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)
0 Karma

MaximeMoreau
New Member

Perfect, thank you again.

Normally it will be ok with this code.

Have a nice day !

0 Karma

cmerriman
Super Champion

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!

0 Karma

somesoni2
SplunkTrust
SplunkTrust

What's your requirement here??

0 Karma

Vijeta
Influencer

Any reason you using like and not = ?

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...