Joining two fields based on matching value to two other fields.

[jwinderDDS]

I am trying to create a top bandwidth users report from the RT_FLOW_SESSION_CLOSE data coming from our Juniper SRX. A sample event looks like:

Jan  8 10:03:42 fw-columbus01 1 2015-01-08T10:03:41.446-05:00 fw-columbus01 RT_FLOW - RT_FLOW_SESSION_CLOSE [junos@2636.1.1.1.2.39 reason="idle Timeout" source-address="172.16.0.10" source-port="61968" destination-address="8.8.8.8" destination-port="53" service-name="junos-dns-udp" nat-source-address="xxx.xxx.xxx.xxx" nat-source-port="4995" nat-destination-address="8.8.8.8" nat-destination-port="53" src-nat-rule-name="default" dst-nat-rule-name="None" protocol-id="17" policy-name="default-permit" source-zone-name="trust" destination-zone-name="untrust" session-id-32="23014" packets-from-client="1" bytes-from-client="72" packets-from-server="1" bytes-from-server="144" elapsed-time="4" application="UNKNOWN" nested-application="UNKNOWN" username="N/A" roles="N/A" packet-incoming-interface="ge-0/0/0.0" encrypted="UNKNOWN"]

The problem I am running into is, these event records are for both sides of the flow conversation. In order to figure out the total bytes send by 172.16.0.10, not only do I have to sum(bytes_from_client) by source-address where 172.16.0.10 is the source, but I also need to sum(bytes_from_server) by destination_address where 172.16.0.10 is the destination. Now of course I would like a table with any IP addresses seen and total bytes sent, not just a single one.

Any guidance will be greatly appreciated!

Jeremy

[lguinn2]

Try this

yoursearchhere
| stats sum(bytes_from_client) as bytes_client sum(bytes_from_server) as bytes_server by source-address destination-address

as a starting point

Then, you can do this:

yoursearchhere
| stats sum(bytes_from_client) as bytes_client by source-address 
| rename source-address as IP
| join IP [ search yoursearchhere
    | stats sum(bytes_from_server) as bytes_server by destination-address
    | rename destination-address as IP ]
| table IP bytes_client bytes_server

The second search will probably take approximately twice as long to run...

[jwinderDDS]

Iguinn,

I appreciate the response. Based on your recommendation I managed to get closer to my desired report. My current search command is:

`srx_traffic` | stats sum(bytes_from_server) as bytes_server by destination_address | rename destination_address AS IP | join type=outer IP [ search `srx_traffic` | stats sum(bytes_from_client) as bytes_client by source_address | rename source_address AS IP] | fillnull value=0 bytes_client bytes_server | eval bytes='bytes_client'+'bytes_server' |  sort -bytes | table IP bytes

The current problem is it doesn't work with realtime searches.

Thank you again!