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 | Medium | Medium | |
| Low | 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.
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:
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
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
I think I make this work! Thank you 😀