topic Re: Grouping within multivalued fields in Splunk Search

Grouping within multivalued fields

chaday00 — Mon, 17 May 2021 16:12:53 GMT

I have built a query that exports data by a date range and based on a scan or source. Currently I'm grouping them into a multivalued field so that I can have 1 row per source and can easily see all the impacted data by date range (see below):

Source	Within 30 Days	30-60 Days	60-90 Days	90+ Days
scan1.csv	Low SSL/TLS Diffie-Hellman Modulus <= 1024 Bits (Logjam) 192.168.0.1	Medium SSL Certificate Cannot Be Trusted 192.168.0.1		Medium SMB Signing not required 192.168.0.15
	Low SSL/TLS Diffie-Hellman Modulus <= 1024 Bits (Logjam) 192.168.0.15	Medium SSL Self-Signed Certificate 192.168.0.1
	Medium SMB Signing not required 192.168.0.1
	Medium SSL Certificate Cannot Be Trusted 192.168.0.15

As you can see above with the Logjam finding, I'm repeating the 'Severity' and 'Plugin Name' respectively with a unique IP address. I'd like, however, to only see the new IP address in this instance (see below) but have been unable to find a way to group by 'severity' and 'plugin_name' within the multivalued group.

desired output

Using the test data below, you can use this search string to reproduce the 1st table above:

| inputlookup test.csv | eval epochTime=now()-firstSeen | eval dateRange=case(epochTime <= (86400*30), "Within 30 Days" , epochTime >= (86400*31) AND epochTime <= (86400*60), "30-60 Days", epochTime >= (86400*61) AND epochTime <= (86400*90), "60-90 Days", epochTime > (86400*91), "90+ Days") | dedup severity plugin_name ipv4 | sort severity plugin_name ipv4 | eval findings30days=case(dateRange=="Within 30 Days", mvappend(severity, plugin_name, ipv4)) | eval findings3060days=case(dateRange=="30-60 Days", mvappend(severity, plugin_name, ipv4)) | eval findings6090days=case(dateRange=="60-90 Days", mvappend(severity, plugin_name, ipv4)) | eval findingsOver90days=case(dateRange== "90+ Days", mvappend(severity, plugin_name, ipv4)) | rename source AS Scan, severity AS Severity, plugin_name AS "Plugin Name", dateRange AS Age, ipv4 AS IP | stats list(findings30days) AS "Within 30 Days" list(findings3060days) AS "30-60 Days" list(findings6090days) AS "60-90 Days" list(findingsOver90days) AS "90+ Days" by Scan

Below is some sample data that may be useful:

source severity ipv4 plugin_name firstSeen

scan1.csv Medium 192.168.0.1 SSL Certificate Cannot Be Trusted 1617212174

scan1.csv Medium 192.168.0.15 SSL Certificate Cannot Be Trusted 1617212174

scan1.csv Medium 192.168.0.1 SSL Self-Signed Certificate 1619459159

scan1.csv Medium 192.168.0.1 SSL Certificate with Wrong Hostname 1619459159

scan1.csv Medium 192.168.0.15 SSL Self-Signed Certificate 1617212174

scan1.csv Medium 192.168.0.15 SSL Certificate with Wrong Hostname 1617212174

scan1.csv Medium 192.168.0.1 TLS Version 1.0 Protocol Detection 1619459159

scan1.csv Medium 192.168.0.15 TLS Version 1.0 Protocol Detection 1619459159

scan2.csv Medium 192.168.0.2 TLS Version 1.0 Protocol Detection 1619459159

scan2.csv Medium 192.168.0.2 SSL Certificate with Wrong Hostname 1619459159

scan2.csv Medium 192.168.0.2 SSL Certificate Cannot Be Trusted 1619459159

scan2.csv Medium 192.168.0.3 TLS Version 1.0 Protocol Detection 1619459159

scan2.csv Medium 192.168.0.3 SSL Certificate with Wrong Hostname 1620053764

scan2.csv Medium 192.168.0.3 SSL Certificate Cannot Be Trusted 1620053764

scan1.csv Medium 192.168.0.1 SSL RC4 Cipher Suites Supported (Bar Mitzvah) 1620053764

scan1.csv Medium 192.168.0.1 SSL Medium Strength Cipher Suites Supported (SWEET32) 1620053764

scan1.csv Low 192.168.0.15 SSL/TLS Diffie-Hellman Modulus <= 1024 Bits (Logjam) 1620053764

scan1.csv Low 192.168.0.1 SSL/TLS Diffie-Hellman Modulus <= 1024 Bits (Logjam) 1620053764

Current output in Splunk is below:

current output

Re: Grouping within multivalued fields

ITWhisperer — Mon, 17 May 2021 16:53:52 GMT

The bit before the blank lines reproduces the data you supplied (thanks for that - very useful). The extra two lines I inserted gather the ip addresses with the same severity and plugin name, and later join the mv field with new lines so the final stats has each part listed in the mv fields separately.

| makeresults | eval _raw="source,severity,ipv4,plugin_name,firstSeen scan1.csv,Medium,192.168.0.1,SSL Certificate Cannot Be Trusted,1617212174 scan1.csv,Medium,192.168.0.15,SSL Certificate Cannot Be Trusted,1617212174 scan1.csv,Medium,192.168.0.1,SSL Self-Signed Certificate,1619459159 scan1.csv,Medium,192.168.0.1,SSL Certificate with Wrong Hostname,1619459159 scan1.csv,Medium,192.168.0.15,SSL Self-Signed Certificate,1617212174 scan1.csv,Medium,192.168.0.15,SSL Certificate with Wrong Hostname,1617212174 scan1.csv,Medium,192.168.0.1,TLS Version 1.0 Protocol Detection,1619459159 scan1.csv,Medium,192.168.0.15,TLS Version 1.0 Protocol Detection,1619459159 scan2.csv,Medium,192.168.0.2,TLS Version 1.0 Protocol Detection,1619459159 scan2.csv,Medium,192.168.0.2,SSL Certificate with Wrong Hostname,1619459159 scan2.csv,Medium,192.168.0.2,SSL Certificate Cannot Be Trusted,1619459159 scan2.csv,Medium,192.168.0.3,TLS Version 1.0 Protocol Detection,1619459159 scan2.csv,Medium,192.168.0.3,SSL Certificate with Wrong Hostname,1620053764 scan2.csv,Medium,192.168.0.3,SSL Certificate Cannot Be Trusted,1620053764 scan1.csv,Medium,192.168.0.1,SSL RC4 Cipher Suites Supported (Bar Mitzvah),1620053764 scan1.csv,Medium,192.168.0.1,SSL Medium Strength Cipher Suites Supported (SWEET32),1620053764 scan1.csv,Low,192.168.0.15,SSL/TLS Diffie-Hellman Modulus <= 1024 Bits (Logjam),1620053764 scan1.csv,Low,192.168.0.1,SSL/TLS Diffie-Hellman Modulus <= 1024 Bits (Logjam),1620053764" | multikv forceheader=1 | fields - _* linecount | eval epochTime=now()-firstSeen | eval dateRange=case(epochTime <= (86400*30), "Within 30 Days" , epochTime >= (86400*31) AND epochTime <= (86400*60), "30-60 Days", epochTime >= (86400*61) AND epochTime <= (86400*90), "60-90 Days", epochTime > (86400*91), "90+ Days") | dedup severity plugin_name ipv4 | stats list(*) as * by dateRange source plugin_name severity | eval findings30days=case(dateRange=="Within 30 Days", mvappend(severity, plugin_name, ipv4)) | eval findings3060days=case(dateRange=="30-60 Days", mvappend(severity, plugin_name, ipv4)) | eval findings6090days=case(dateRange=="60-90 Days", mvappend(severity, plugin_name, ipv4)) | eval findingsOver90days=case(dateRange== "90+ Days", mvappend(severity, plugin_name, ipv4)) | rename source AS Scan, severity AS Severity, plugin_name AS "Plugin Name", dateRange AS Age, ipv4 AS IP | foreach findings* [| eval <<FIELD>>=mvjoin(<<FIELD>>," ")] | stats list(findings30days) AS "Within 30 Days" list(findings3060days) AS "30-60 Days" list(findings6090days) AS "60-90 Days" list(findingsOver90days) AS "90+ Days" by Scan

Re: Grouping within multivalued fields

chaday00 — Mon, 17 May 2021 17:08:24 GMT

I think I make this work! Thank you 😀