How do you make a search that checks two lookup tables and optimizes results?

[adabud6267]

Hello Splunk friends!

I have two lookup tables.
The first http_full (http_full.csv) looks like this:

status,IP,URL,code,city
200,x.x.x.x, https://domain.com,Fr,France
301,y.y.y.y,https://domain2.com,Br,Berlin
...
...

The second lookup cipher-suite_lookup (cipher-suite_lookup.csv) looks as follows:

Cipher Suite,Inline,Passive-Tap
TLS_RSA_WITH_RC4_128_MD5 ,Yes,Yes
TLS_DHE_RSA_WITH_AES_256_CBC_SHA,Yes,No
...
...
...

So. in steps, this is what I'd like to achieve:

1. Narrow down the results based on the destination and source IP
2. Lookup and match the IP value from the http_full against the extracted field dest_ip
3. Next, match the Cipher_Suitevalue from the second lookup against extracted filed "cipher_suite"
4. And count what can be decrypted in Inline vs Passive mode (Inline - you can decrypt always all in passive mode you can decrypt only where is says yes).

here's my current search:

index=sec_ssl  host="ssl-1" OR host="ssl-2" AND (destination_ip=x.x.x.x/yy OR destination_ip=z.z.z.z/cc) NOT (src_ip=a.a.a.a/bb OR src_ip=e.e.e.e/ff OR src_ip=k.k.k.k/nn)
| lookup https_full IP as dest_ip OUTPUT URL, IP, "city abbrev"
| search URL=*, IP=*
| stats count as "Connections" by URL, IP, "city abbrev", cipher_suite
| lookup ssl_cipher-suite_lookup.csv "Cipher Suite" as cipher_suite OUTPUT Inline, Passive-Tap 
| rename Passive-Tap as PassiveTap
| eval InlineCount=if(Inline=="Yes",Connections,0)
| eval PassiveCount=if(PassiveTap=="Yes",Connections,0)
| stats sum(Connections) as sumConnections sum(InlineCount) as sumInline sum(PassiveCount) as sumPassive values(IP) as "IP" by URL
| eval PercentageInline=sumInline/sumConnections*100(percent,2)
| eval PercentagePassive=sumPassive/sumConnections*100(percent,2)
| fields - sumInline, - sumPassive 
| table URL, IP, sumConnections, PercentageInline, PercentagePassive
| sort - sumConnections
| stats values(URL) as URL, values(sumConnections) as Connections, values(PercentageInline) as Inline%, values(PercentagePassive) as Passive% by IP
| sort - Connections

Is there any way to:

1) optimize the search
2) round up the % values to two one digit after comma ?

Thank you in advance!

[woodcock]

Like this:

index=sec_ssl  host="ssl-1" OR host="ssl-2" IP="*" AND (destination_ip=x.x.x.x/yy OR destination_ip=z.z.z.z/cc) NOT (src_ip=a.a.a.a/bb OR src_ip=e.e.e.e/ff OR src_ip=k.k.k.k/nn)
| lookup https_full IP AS dest_ip OUTPUT URL, IP, "city abbrev"
| stats count AS Connections BY URL, IP, "city abbrev", cipher_suite
| lookup ssl_cipher-suite_lookup.csv "Cipher Suite" as cipher_suite OUTPUT Inline, Passive-Tap AS PassiveTap
| stats sum(Connections) AS sumConnections sum(eval(Inline=="Yes",Connections,0)) AS sumInline sum(eval(PassiveTap=="Yes",Connections,0)) AS sumPassive values(IP) AS "IP" BY URL
| eval PercentageInline=round(100 * sumInline/sumConnections, 2)
| eval PercentagePassive=round(100 * sumPassive/sumConnections, 2)
| stats values(URL) as URL, values(sumConnections) AS Connections, values(PercentageInline) AS Inline%, values(PercentagePassive) AS Passive% BY IP
| sort 0 - Connections