How to fill a non existing field from other source

[jrballesteros05]

Hello everyone, I have this search

(index=trans_xxx_mycountry sourcetype=trans_xxx_mycountry) OR (index=trans_yyy_all sourcetype=trans_all) OR (index=trans_zzz_all2 sourcetype=trans_all2) TIPO_MENSAJE=* TXID="*" 
| eval TXID=lower(TXID) 
| lookup Id_Capas SAT_CAPA as SAT_CAPA OUTPUT Capa 
| eval t=_time 
| stats values(SOCIEDAD) by TXID,Capa

I get this result:

TXID                                   Capa   SOCIEDAD
0457f879-2d1a-4a00-8cc0-b0abda12a04d    1   CL03
0457f879-2d1a-4a00-8cc0-b0abda12a04d    2   CL03
0457f879-2d1a-4a00-8cc0-b0abda12a04d    3   SUCL
0457f879-2d1a-4a00-8cc0-b0abda12a04d    4    

I want to fill the value "SOCIEDAD" from "Capa=1" or "Capa=2" into the SOCIEDAD from "Capa=4" because the value does not exist there or sometimes is NULL. How can I do that? I've been googling it but I didn't found something similar for me. The TXID is the same in all events but it comes from different sources. Basically, I want this:

TXID                                   Capa   SOCIEDAD
0457f879-2d1a-4a00-8cc0-b0abda12a04d    1   CL03
0457f879-2d1a-4a00-8cc0-b0abda12a04d    2   CL03
0457f879-2d1a-4a00-8cc0-b0abda12a04d    3   SUCL
0457f879-2d1a-4a00-8cc0-b0abda12a04d    4   CL03

I have a solution with a saved search that is writing an external lookup but I want to know if I there is a better way to do that.

I would appreciate any help.

[DalJeanis · Accepted solution]

Try this -

 (index=trans_xxx_mycountry sourcetype=trans_xxx_mycountry) OR (index=trans_yyy_all sourcetype=trans_all) OR (index=trans_zzz_all2 sourcetype=trans_all2) TIPO_MENSAJE=* TXID="*" 
 | eval TXID=lower(TXID) 
 | lookup Id_Capas SAT_CAPA as SAT_CAPA OUTPUT Capa 
 | eval t=_time 
 | stats values(SOCIEDAD) as SOCIEDAD by TXID,Capa
 | sort 0 capa TXID 
 | eventstats first(SOCIEDAD) as firstCapaSOCIEDAD
 | eval SOCIEDAD=coalesce(SOCIEDAD,firstCapaSOCIEDAD)
 | sort 0 TXID

That solution assumes that "Capa 1" will always sort before any other Capa, so its value for SOCIEDAD will be the first one if the records are sorted in Capa order.

[jrballesteros05]

Hello. Thanks for your reply.

I'm going to try this answer at night or tomorrow in the morning because I don't have access to the Splunk right now and I will tell you what happens.

[DalJeanis]

Great. Best wishes!

[jrballesteros05]

Hello, this is what I wanted!!!

I just changed this sentence:

| eventstats first(SOCIEDAD) as firstCapaSOCIEDAD

for this one:

| eventstats first(SOCIEDAD) as firstCapaSOCIEDAD by TXID

Thank you very much and also thanks to everybody who takes the time to reply my question.

[DalJeanis]

Ah, good catch. Missed that since there was only 1 TXID in the sample data.

[woodcock]

You need the filldown command:
https://docs.splunk.com/Documentation/Splunk/6.5.2/SearchReference/Filldown

So just add this to the end of your search:

... | filldown SOCIEDAD

[jrballesteros05]

Hi @woodcock.

Thank you for your reply.

I tried filldown in the @nickhillscpl's reply and I got a lot of falses positives with my search. I'm going to try the answer bellow and I hope that can help me.

Best regards.

[DalJeanis]

Filldown is useful, but it will give you a repeat of the last value -- the value from record 3 -- rather than a default value pulled from record 1.

[nickhills]

I am struggling to think of a way to do this with out using mkmv.
Will either of the two ideas below get you "close" to what you need?

| eval TXID=lower(TXID) 
 | lookup Id_Capas SAT_CAPA as SAT_CAPA OUTPUT Capa 
 | eval t=_time 
 | stats values(SOCIEDAD) as SOCIEDAD by TXID,Capa
 | fillnull value=SOMEVALUE SOCIEDAD

Will give:

 TXID Capa SOCIEDAD
0457f879-2d1a-4a00-8cc0-b0abda12a04d 1 CL03
0457f879-2d1a-4a00-8cc0-b0abda12a04d 2 CL03
0457f879-2d1a-4a00-8cc0-b0abda12a04d 3 SUCL
0457f879-2d1a-4a00-8cc0-b0abda12a04d 4 SOMEVALUE

or

 | eval TXID=lower(TXID) 
 | lookup Id_Capas SAT_CAPA as SAT_CAPA OUTPUT Capa 
 | eval t=_time 
 | stats values(SOCIEDAD) as SOCIEDAD by TXID,Capa
 | filldown SOCIEDAD

results in:

TXID Capa SOCIEDAD
0457f879-2d1a-4a00-8cc0-b0abda12a04d 1 CL03
0457f879-2d1a-4a00-8cc0-b0abda12a04d 2 CL03
0457f879-2d1a-4a00-8cc0-b0abda12a04d 3 SUCL
0457f879-2d1a-4a00-8cc0-b0abda12a04d 4 SUCL

[jrballesteros05]

Hello, I will try this and I'll tell you.

Thanks for your reply.

[jrballesteros05]

Hi, I tried both and the second one is the closest to what I need. It worked well until I realised that sometimes logs from Layer 4 (Capa = 4) are indexed first than logs from other layers.

[nickhills]

(index=trans_xxx_mycountry sourcetype=trans_xxx_mycountry) OR (index=trans_yyy_all sourcetype=trans_all) OR (index=trans_zzz_all2 sourcetype=trans_all2) TIPO_MENSAJE=* TXID="*" 
 | eval TXID=lower(TXID) 
 |eval CapaFallback="SOMEVALUE"
 | lookup Id_Capas SAT_CAPA as SAT_CAPA OUTPUT CapaResult 
 | eval t=_time 
 |eval Capa=coalesce(CapaResult, CapaFallback)
 | stats values(SOCIEDAD) by TXID,Capa

Will allow you to set a default value in CapaFallback which gets used if the result of CapaResult is null

[jrballesteros05]

Hello, thanks for your reply.

Sorry if I wasn't clear or if I need to be more specific.

The field "Capa" is the field I get from the Lookup and is never NULL. The field "SOCIEDAD" when the value Capa is equal to 4 is always NULL.

Basically, I want to fill SOCIEDAD from "Capa =4" with the values of SOCIEDAD from "Capa = 1" or "Capa = 2".

[nickhills]

sorry your question was good - i misunderstood.
will the value of SOCIEDAD be unique per TXID, or could it be a static value?

[jrballesteros05]

Hello.

The field "SOCIEDAD" is different per country.

For example:

SOCIEDAD = CL* or SUCL for Chile. (Where * means any 2 digits number)
SOCIEDAD = PE* or SUPE for Peru.
SOCIEDAD = CO* or SUPROT for Colombia.

Sometimes I get this:

TXID Capa SOCIEDAD
0457f879-2d1a-4a00-8cc0-b0abda12a04d 1 CL03
0457f879-2d1a-4a00-8cc0-b0abda12a04d 2 CL03
0457f879-2d1a-4a00-8cc0-b0abda12a04d 3 SUCL CL03
0457f879-2d1a-4a00-8cc0-b0abda12a04d 4 CL03

The mean reason I want to use values from Capa 1 or 2 is because those fields always give me an unique value, on the other hands Capa 3 can give 1 or more values.