Splunk Search

Grouping within multivalued fields

chaday00
Path Finder

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):

SourceWithin 30 Days30-60 Days60-90 Days90+ 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 outputdesired 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 outputcurrent output

Labels (4)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

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

chaday00
Path Finder

I think I make this work! Thank you 😀

0 Karma
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 ...