How to combine common fields from search results into one column?

[daishih]

I have three devices, each with its own sourcetype. I am trying to combine the fields src with src_ip and dst with dest_ip into new table fields called Source IP and Destination IP from the results of the search below:

blocked OR deny | search src="$IP$" OR src_ip="$IP$" | table _time, eventtype, sourcetype, Source IP, Destination IP

I've been unable to locate how to do this in the Splunk docs, and none of the questions on this site easily explain how this accomplished. Most of the answers just provide code to the person asking without much explanation of what they are doing. I'd like to know how the concept works, not just some code I can copy/paste. My searches will be done over several time frames, so performance is very important as this search will be used to regularly troubleshoot connection issues with the devices.

Once I get the search above to display results correctly, I'd like to take the results of the string below and do the same thing:

blocked OR deny [search sourcetype=pan:traffic | where user="mydomain\\$userName$" | rename src_ip AS src | fields src]  | table _time, eventtype, sourcetype, Source IP, Destination IP, dest_port, url, application, category, src_zone, dest_zone, policy_id, action

Thank you in advance 🙂

[sundareshr · Accepted solution]

I think you may want to read up on Splunk Common Information Model. That will allow you to normalize the fields across multiple sources, such that src_ip will be src_ip (or whatever you map it to) across all sourcetypes making it much easier in your searches to find the right information.

http://docs.splunk.com/Documentation/CIM/4.5.0/User/Overview

In the meantime, you could use the coalesce command. Something like this eval src=coalesce(src, src_ip) What this does is, the src is populated with the non-null value from src and src_ip. So if one event has value in src_ip, that that value is used in field called src So your first search will look something like this

blocked OR deny | search src="$IP$" OR src_ip="$IP$" | eval "Source IP" = coalesce(src, src_ip) | eval "Destination IP"=coalesce(dst, dest_ip) | table _time, eventtype, sourcetype, "Source IP", "Destination IP"

Hope this helps. Let me know if you need further clarifications/assitance.

[daishih]

This information is exactly what I was looking for thank you so much! I wish I found that page earlier today as it would have saved me a lot of time. Also this answer is perfect, works and easy to understand! Very much appreciate your time as I am still a novice at splunk 🙂

[daishih]

When I try to combine this with my sub-string search I receive an error stating "Error in 'table' command: Invalid argument: 'scr:1.1.1.1'

blocked OR deny | eval "Source IP" = coalesce(src, src_ip, src_host) 
| eval "Destination IP" = coalesce(dst, dst_ip, dest_ip) 
| eval "Destination Port" = coalesce(dst_port, dest_port) 
| eval "Application" = coalesce(service, appname, application) 
| eval "Destination Zone" = coalesce(dst_zone, dest_zone)
| table _time, sourcetype,  "Source IP", "Destination IP", "Destination Port", url, "Application", category, src_zone, "Destination Zone", policy_id, action 
[search sourcetype=pan:traffic 
| where user="mydomain\\$userName$" 
| rename src_ip AS src | fields src]

Any ideas on how to correct this?

[sundareshr]

What is the reason for the subsearch?

Before you subsearch, the results only has the columns that are specified in your table command ("Source IP"). If you want to filter by src, you will need to make the following changes.

 ... | search [ search sourcetype=pan:traffic | where user=.... | rename src_ip AS "Source IP" | fields "Source IP"]

This will translate to something like this (look for litsearch in job inspector to verify)
... | search ("Source IP"="some IP here" OR "Source IP"="some other IP here") etc

[daishih]

The sub-search searches the pan:traffic log to get the IP associated with the username entered. Once it has that it will search the other two sources for that IP since they do not know a user by user name only IP. I will try your recommendation and let you know how it goes. It seems to be working now but I am always interested in improving it.

[daishih]

The evals must be placed after the sub-string search:

blocked OR deny
[search sourcetype=pan:traffic 
| where user="psrsdom\\$userName$" 
| rename src_ip AS src | fields src]
| eval "Source IP" = coalesce(src, src_ip, src_host) 
| eval "Destination IP" = coalesce(dst, dst_ip, dest_ip) 
| eval "Destination Port" = coalesce(dst_port, dest_port) 
| eval "Application" = coalesce(service, appname, application) 
| eval "Destination Zone" = coalesce(dst_zone, dest_zone)
| table _time, sourcetype,  "Source IP", "Destination IP", "Destination Port", url, "Application", category, src_zone, "Destination Zone", policy_id, action

[Raschko]

You can just do an eval to get what you want.

eval "Source IP"=coalesce(src,src_ip)

The eval command coalesce will put the first value into "Source IP", that is not null.

[daishih]

The coalesce command is exactly what I needed. All the other questions similar to mine were using joins and lookups tables, stuff I didn't need. Thank you for helping me find the name of it. What happens if it is null will it not work?

[Raschko]

If all values are null nothing is added. You can do something like this:

eval "Source IP"=coalesce(src,src_ip,"Not Available")

If there are no src or src_ip "Not Available" is added.

[daishih]

Ah, good to know, thank you 🙂