Splunk Search

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

MaximeMoreau
Explorer

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
Explorer

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
Explorer

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
Revered Legend

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 You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Announcing the General Availability of Splunk Enterprise Security 8.1!

We are pleased to announce the general availability of Splunk Enterprise Security 8.1. Splunk becomes the only ...

Developer Spotlight with William Searle

The Splunk Guy: A Developer’s Path from Web to Cloud William is a Splunk Professional Services Consultant with ...