Getting Data In

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

adabud6267
Explorer

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!

0 Karma

woodcock
Esteemed Legend

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
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...