Splunk Search

How to fill a non existing field from other source

jrballesteros05
Communicator

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.

0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

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.

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

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.

View solution in original post

jrballesteros05
Communicator

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.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Great. Best wishes!

0 Karma

jrballesteros05
Communicator

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.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

0 Karma

woodcock
Esteemed Legend

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

jrballesteros05
Communicator

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.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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.

0 Karma

nickhills
Ultra Champion

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
If my comment helps, please give it a thumbs up!
0 Karma

jrballesteros05
Communicator

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

Thanks for your reply.

0 Karma

jrballesteros05
Communicator

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.

0 Karma

nickhills
Ultra Champion
(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

If my comment helps, please give it a thumbs up!
0 Karma

jrballesteros05
Communicator

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".

0 Karma

nickhills
Ultra Champion

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

If my comment helps, please give it a thumbs up!
0 Karma

jrballesteros05
Communicator

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.

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!