topic Re: How to fill a non existing field from other source in Splunk Search

How to fill a non existing field from other source

jrballesteros05 — Fri, 24 Feb 2017 14:39:34 GMT

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.

Re: How to fill a non existing field from other source

nickhills — Fri, 24 Feb 2017 14:45:38 GMT

(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

Re: How to fill a non existing field from other source

jrballesteros05 — Fri, 24 Feb 2017 14:59:31 GMT

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

Re: How to fill a non existing field from other source

nickhills — Fri, 24 Feb 2017 15:26:53 GMT

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

Re: How to fill a non existing field from other source

nickhills — Fri, 24 Feb 2017 15:47:11 GMT

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

 | 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

Re: How to fill a non existing field from other source

jrballesteros05 — Fri, 24 Feb 2017 16:06:04 GMT

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.

Re: How to fill a non existing field from other source

jrballesteros05 — Fri, 24 Feb 2017 16:35:51 GMT

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

Thanks for your reply.

Re: How to fill a non existing field from other source

jrballesteros05 — Fri, 24 Feb 2017 19:23:34 GMT

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.

Re: How to fill a non existing field from other source

woodcock — Wed, 01 Mar 2017 01:23:29 GMT

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

Re: How to fill a non existing field from other source

DalJeanis — Wed, 01 Mar 2017 05:18:46 GMT

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.

Re: How to fill a non existing field from other source

jrballesteros05 — Thu, 02 Mar 2017 18:24:11 GMT

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.

Re: How to fill a non existing field from other source

jrballesteros05 — Thu, 02 Mar 2017 18:26:33 GMT

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.

Re: How to fill a non existing field from other source

DalJeanis — Thu, 02 Mar 2017 18:57:36 GMT

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.

Re: How to fill a non existing field from other source

DalJeanis — Thu, 02 Mar 2017 18:57:52 GMT

Great. Best wishes!

Re: How to fill a non existing field from other source

jrballesteros05 — Fri, 03 Mar 2017 16:50:07 GMT

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.

Re: How to fill a non existing field from other source

DalJeanis — Fri, 03 Mar 2017 17:31:17 GMT

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