topic Re: How would a lookup to combine two values to one OUTPUT field? in Splunk Search

How would a lookup to combine two values to one OUTPUT field?

robdanl — Tue, 29 Sep 2020 14:17:18 GMT

I'm looking at firewall logs which typically have (among other details) a source address and a destination address. I'm attempting to use a lookup table to determine the organizational group that each IP belongs to. The lookup table is a fairly basic format:

Subnet,OrgGroup
192.168.0.0/24,Group1
192.168.1.0/24,Group2

This was how I was originally going to do it:

sourcetype=cisco:asa | lookup ip_to_group Subnet AS src_ip OUTPUT OrgGroup as src_group  | lookup ip_to_group Subnet AS dest_ip OUTPUT OrgGroup as dest_group

This gives me: src_group=Group1, dest_group=Group2
For business reasons, we don't want to have two output fields (src_group, dest_group) but one ("org_group"):

sourcetype=cisco:asa | lookup ip_to_group Subnet AS src_ip OUTPUT OrgGroup as org_group  | lookup ip_to_group Subnet AS dest_ip OUTPUT OrgGroup as org_group

How would I blend those lookups into both using "org_group" without overwriting? e.g. org_group=Group1,Group2

Re: How would a lookup to combine two values to one OUTPUT field?

anthonymelita — Tue, 29 Sep 2020 14:16:58 GMT

You could concatenate the results using an eval statement

| eval org_group= src_group+",".dest_group,You could concatenate the two fields using an eval statement

| eval org_group= src_group+",".dest_group

Re: How would a lookup to combine two values to one OUTPUT field?

MuS — Tue, 30 May 2017 20:15:58 GMT

Hi robdanl,

make the lookup an automatic lookup http://docs.splunk.com/Documentation/Splunk/latest/Knowledge/Makeyourlookupautomatic so it will perform the lookup automatically for you and add a calculated field http://docs.splunk.com/Documentation/Splunk/latest/Knowledge/definecalcfields to your soucetype that concatenates the fields src_group and dest_group like this in props.conf:

EVAL-org_group = src_group .":". dest_group

or as multi value field (which is a bit tricky and nasty for post processing AND if your really want it this way) in search time:

your search here | eval org_group = "" | foreach *_group [eval org_group='<<FIELD>>' + "," + org_group]| makemv delim="," org_group

Maybe you need to adapt this, because I haven't tested it 😉

Hope this helps to get you started ...

cheers, MuS

Re: How would a lookup to combine two values to one OUTPUT field?

robdanl — Tue, 30 May 2017 20:37:28 GMT

Both of your suggestions work. It's a shame there doesn't seem to be a way to create a multi-value field as part of a lookup the way I described. That would be ideal as - you're right - doing a loop through fields and concatenating them doesn't feel ideal when we need to have it included in every single search performed.

Re: How would a lookup to combine two values to one OUTPUT field?

woodcock — Tue, 30 May 2017 21:54:39 GMT

Like this:

sourcetype=cisco:asa
| lookup ip_to_group Subnet AS src_ip OUTPUT OrgGroup as src_group 
| lookup ip_to_group Subnet AS dest_ip OUTPUT OrgGroup as dest_group
| nomv src_grp
| nomv dest_grp
| eval combined = src_grp . " " . dest_grp
| makemv org_group
| eval org_group= mvdedup(org_group)

Re: How would a lookup to combine two values to one OUTPUT field?

robdanl — Thu, 01 Jun 2017 18:30:38 GMT

On your second option (with the foreach), how would that be included in every search? While I would be able to copy/paste that string or add it into a macro, not every user could or would want to do that.

Re: How would a lookup to combine two values to one OUTPUT field?

robdanl — Thu, 01 Jun 2017 18:45:02 GMT

It also appears I can also do it this way:

eval org_group=mvappend(src_group,dest_group)

Now I just need to figure out how to make every search include this behind the scenes.

Re: How would a lookup to combine two values to one OUTPUT field?

anthonymelita — Thu, 01 Jun 2017 20:28:38 GMT

Create a calculated field from your eval statement.
Settings > Fields > Caluclated fields

it won't auto append on your searches, but it will be a nice shortcut

Re: How would a lookup to combine two values to one OUTPUT field?

woodcock — Thu, 01 Jun 2017 20:31:09 GMT

Yes, that is better, but it might still need mvdedup.

Re: How would a lookup to combine two values to one OUTPUT field?

MuS — Thu, 01 Jun 2017 20:38:07 GMT

Yes, that's the downside of the second option. Therefore the automatic lookup and the calculated field as in option one is preferred. Or use the mvappend() as you figured out, for the calculated field. Still post processing this new field will be tricky....

cheers, MuS

Re: How would a lookup to combine two values to one OUTPUT field?

robdanl — Mon, 05 Jun 2017 14:20:35 GMT

Unfortunately, it doesn't look like this will work per the documentation on props.conf:

Splunk processes calculated fields after field extraction and field
aliasing but before lookups. This means that:
- You can use a field alias in the eval statement for a calculated
field.
- You cannot use a field added through a lookup in an eval statement for a
calculated field.

Re: How would a lookup to combine two values to one OUTPUT field?

robdanl — Mon, 05 Jun 2017 14:22:33 GMT

It doesn't appear I can do as you were suggesting as calculated fields are processed before lookups per your initial link as well as some documention on props.conf:

Calculated fields come fifth in the search-time operations sequence, after field aliasing but before lookups

Re: How would a lookup to combine two values to one OUTPUT field?

MuS — Mon, 05 Jun 2017 17:52:51 GMT

Splunk search time order of operations strikes again - sorry for that 😕

But you could try using a eval based macro http://docs.splunk.com/Documentation/Splunk/latest/Knowledge/Definesearchmacros#Eval_expressions_in_macro_definitions for this