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:
IP
value from the http_full
against the extracted field dest_ip
Cipher_Suite
value from the second lookup against extracted filed "cipher_suite" 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!
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