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:
Narrow down the results based on the destination and source IP
Lookup and match the IP value from the http_full against the extracted field dest_ip
Next, match the Cipher_Suite value from the second lookup against extracted filed "cipher_suite"
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!
... View more