Merge the data on index

[simo]

Hi All,

I'm in this situation

index a		index b
id	neme		id	neme
1	simone		1	simone
3	francesco		2	marco
4	luca

I have a scheduled search that extracts data from index a and writes it to index b. as you can see in both index a and b there is id = 1. my search currently duplicates the result this way

index b
id	neme
1	simone
2	marco
1	simone
3	francesco
4	luca

is there a function merge type sql? the expected result is the following

index b
id	neme
1	simone
2	marco
3	francesco
4	luca

can you help me? thanks for any answer

Best Regards,

Simone

[aasabatini]

Ciao Simo,

intendi unire i due indici?

in splunk puoi utilizzare la funzione OR

es: index a OR index b

oppure utilizzare le funzioni join come in SQL ma perdono di performance

Saluti

[simo]

ciao,

no io vorrei scrivere all interno dell indice B solo le righe dell indicie A che non sono preseti in B.

avevo pensato di escludere gli id presenti in  B ma su miliardi di righe ci mettre troppo per farlo 

Simone

[aasabatini]

Ciao @simo 

la search per collezionare i log dentro l'indice B dovrebbe essere così:

the search for collecting logs inside index B should look like this:

index=a OR idex=b | stats dc(index) AS dc_index values(index) AS index BY name, ID
| eval Status=if(dc_index=2,"Both",index)
| search NOT status="a"
|table name ID
| collect index=b

[gcusello]

Hi guys, Ciao a tutti

please write in english for the other people of Community!

To have a complete situation, you could run something like this.

index=indexA OR index=indexB
| stats dc(index) AS dc_index values(index) AS index BY name
| eval Status=if(dc_index=2,"Both Indexes",index)
| table name Status

then you can choose the name to take.

It's not clear for me if you want the id from IndexA or if you want to generate a new id.

Ciao.

Giuseppe

P.S.: there's an Italian Splunk User Group that you can find at https://community.splunk.com/t5/Roma-User-Group/gh-p/ug-roma

[simo]

hi @gcusello ,

I would like to write on index B only the new ids present on index A. it's possible?

Simone

[gcusello]

Hi @simo,

let me understand:

• the object you're calling index B is a summary index or a lookup?
• you spoke about IDs, where they come from: index A (but they could be replicated) or it's generated at report level?

If it's a summary index, you should run something like this:

index=indexB NOT [ search index=indexB | dedup neme | fields neme ]
| table id neme
| collect index=indexB

If indexB it's a lookup, try something like this:

index=indexB NOT [ | inputlookup indexB | dedup neme | fields neme ]
| table id neme
| outputlookup indexB append=true

in both the solutions there's the problem of ID that you could manage at report level.

Ciao.

Giuseppe