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

[robdanl]

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

[anthonymelita]

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

[robdanl]

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.

[robdanl]

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.

[woodcock]

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

[woodcock]

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)

[MuS · Accepted solution]

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

[robdanl]

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.

[MuS]

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

[robdanl]

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

[MuS]

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_... for this

[robdanl]

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.

[anthonymelita]

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