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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...