topic Re: Using IF statement with SUM and passing data to a timechart. in Splunk Search

Using IF statement with SUM and passing data to a timechart.

eliwasserman92 — Thu, 06 Jun 2019 14:38:45 GMT

I am interested in quantifying inbound/outbound traffic traversing an IPsec tunnel on a Palo Alto firewall and visualizing the results with a Splunk timechart.

The firewall creates a single syslog event per session, with a typical event containing the following fields:
src_interface=tunnel.44
dest_interface=ethernet1/1
bytes=3299
bytes_in=456
bytes_out=2843

In order to filter the traffic to this particular tunnel in both directions, we can create a search that includes src_interface=tunnel.44 OR dest_interface=tunnel.44. Additionally, the bytes field represents the total traffic passed during the session, so we are ultimately going to focus on bytes_inand bytes_out fields.

The splunk search would start like this:
index=sampleindex dvc_name=samplefirewall dest_interface=tunnel.44 OR src_interface=tunnel.44

The challenge will be in quantifying the data. To demonstrate the traffic for tunnel.44 we need to add the following logic:
bytes_in IF src_interface=tunnel.44 = inbound
bytes_out IF dest_interface=tunnel.44 = inbound
bytes_in IF dest_interface=tunnel.44 = outbound
bytes_out IF src_interface=tunnel.44 = outbound

I am looking to:
1. Use a statement to collect the bytes_in and bytes_out and aggregate them into inbound and outbound
2. Chart the inbound/outbound on a timechart visualization.

Re: Using IF statement with SUM and passing data to a timechart.

niketn — Wed, 30 Sep 2020 00:48:46 GMT

@eliwasserman92 try the following query

Step 1: Create sample events 5 each for src_interface="tunnel.44" and dest_interface="tunnel.44" with random bytes_in, bytes_out as per your question. PS for dummy data time difference is 5 min using duration=300 sec. So run the search for Last 30 Minute using relative time preset.

| makeresults count=5 
| eval duration=300 
| accum duration 
| eval _time=_time-duration 
| eval bytes_in=random(),bytes_out=random() 
| eval bytes_in=substr(bytes_in,1,4), bytes_out=substr(bytes_out,1,4) 
| eval src_interface="tunnel.44" 
| append 
    [| makeresults count=5 
    | eval duration=300 
    | accum duration 
    | eval _time=_time-duration 
    | eval bytes_in=random(),bytes_out=random() 
    | eval bytes_in=substr(bytes_in,1,4), bytes_out=substr(bytes_out,1,4) 
    | eval dest_interface="tunnel.44"] 
| fields - duration

Step 2: Create actual Inbound traffic bytes and Outbound traffic bytes from bytes_in and bytes_out using src_interface and dest_interface as per the use case. Original bytes_in and bytes_out in this step are retained to compare and confirm that bytes_in becomes outbound traffic bytes if dest_interface is tunnel.44 and vice versa for bytes_out which becomes inbound traffic bytes if the dest_interface is tunnel.44. For src_interface="tunnel.44", bytes_in remains as Inbound traffic bytes and bytes_out remains as Outbound traffic bytes. All as described in the use case.

| eval inbound_traffic_bytes=case(src_interface=="tunnel.44",bytes_in,
    dest_interface=="tunnel.44",bytes_out), outbound_traffic_bytes=case(src_interface=="tunnel.44",bytes_out,
    dest_interface=="tunnel.44",bytes_in)

Step 3 Apply timechart on new fields inbound_traffice_bytes and outbound_traffic_bytes.

| timechart span=5m sum(inbound_traffic_bytes) as "Inbound" sum(outbound_traffic_bytes) as "Outbound"

Following is the Run anywhere dashboard code for you to try out and confirm.

<dashboard>
  <label>Inbound Outbound Traffic Timechart</label>
  <row>
    <panel>
      <chart>
        <search>
          <query>| makeresults count=5 
| eval duration=300 
| accum duration 
| eval _time=_time-duration 
| eval bytes_in=random(),bytes_out=random() 
| eval bytes_in=substr(bytes_in,1,4), bytes_out=substr(bytes_out,1,4) 
| eval src_interface="tunnel.44" 
| append 
    [| makeresults count=5 
    | eval duration=300 
    | accum duration 
    | eval _time=_time-duration 
    | eval bytes_in=random(),bytes_out=random() 
    | eval bytes_in=substr(bytes_in,1,4), bytes_out=substr(bytes_out,1,4) 
    | eval dest_interface="tunnel.44"] 
| fields - duration

| eval inbound_traffic_bytes=case(src_interface=="tunnel.44",bytes_in,
    dest_interface=="tunnel.44",bytes_out), outbound_traffic_bytes=case(src_interface=="tunnel.44",bytes_out,
    dest_interface=="tunnel.44",bytes_in)
| timechart span=5m sum(inbound_traffic_bytes) as "Inbound" sum(outbound_traffic_bytes) as "Outbound"</query>
          <earliest>-30m@m</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
      </chart>
    </panel>
  </row>
</dashboard>

Re: Using IF statement with SUM and passing data to a timechart.

eliwasserman92 — Thu, 06 Jun 2019 19:12:04 GMT

Thank you @niketnilay for the detailed response.

My final successful search was as follows:

`index=sampleindex dvc_name=sampledevice dest_interface=tunnel.44 OR src_interface=tunnel.44
| eval inbound_traffic_bytes=case(src_interface=="tunnel.44",bytes_in,
     dest_interface=="tunnel.44",bytes_out), outbound_traffic_bytes=case(src_interface=="tunnel.44",bytes_out,
     dest_interface=="tunnel.44",bytes_in)
| eval inbound_traffic_megabytes=inbound_traffic_bytes/1024/1024
| eval outbound_traffic_megabytes=outbound_traffic_bytes/1024/1024
| timechart sum(inbound_traffic_megabytes) as "Inbound" sum(outbound_traffic_megabytes) as "Outbound" `