Dashboards & Visualizations

stats(value) grouping but ignore a specific date/time field

DBattisto
Communicator

Hello-

I'm trying to create a report that groups DNS name with Identification Number (QID) from a vulnerability report. I'm using |stats value(dns) which works well enough, but the problem is a column 'first discovered' may have 3-4 values depending on the instance being scanned:

DNSQIDFirst DiscoveredSLA Status

Server-1
Server-2
Server-3

Q-33331-1-2021Overdue
Server-4
Server-5
Q-33332-1-2021OK

 

The problem though, is that I need Servers 4-5 to appear with Servers 1-3 under the '1-1-21' date due to SLA requirements (it doesn't matter if a vulnerability when the QID was discovered on a specific device, we're only concerned with it's first detection). Ideally it'd look like this:

DNSQIDFirst DiscoveredSLA Status
Server-1
Server-2
Server-3
Server-4
Server-5
Q-33331-1-2021Overdue


Here's relevant parts of my query:

 

 

 

%base search%
| stats values(DNS) as DNSf BY QID, POAM_ID, Controls, signature, DIAGNOSIS, dvc, OS, firstdisc, duedate, PATCHABLE

 

 

 

 

 Thanks!

Labels (1)
Tags (1)
0 Karma
1 Solution

DBattisto
Communicator

Hey @richgalloway, thanks for the responses. Still not what I'm looking for so I decided to go another way:

I created a job that runs after the vulnerability scans that queries all of the QID's and puts them into a lookup file. I initially ran the job manually, but now when it runs on schedule, there is a subsearch to only append the QID's not currently in the lookup. Looks something like this:

%base search% NOT [|inputlookup firstfound.csv | fields + QID]
| eval firstdisce=strptime(FIRST_FOUND_DATETIME, "%Y-%m-%d") 
| eval firstdisc=strftime(firstdisce, "%Y-%m-%d")
| sort firstdisc
| dedup QID
| eval firstdiscev=firstdisce
| table QID, signature, firstdisc, firstdisce, firstdiscev
| rename firstdisc as firstdiscsv 
| outputlookup firstfound.csv append=true

There are a couple extra fields in there (firstdisce, firstdisev), but they're needed in other dashboards...

Thanks again 🙂

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

Every field that follows "by" will create a new row for each unique value in that field.  If a field is to be ignored then it should appear before the "by" clause (or implied by a wildcard).

%base search%
| stats values(*) as * BY QID, POAM_ID, Controls, signature, DIAGNOSIS, dvc, OS, duedate, PATCHABLE

 

---
If this reply helps you, Karma would be appreciated.

DBattisto
Communicator

Thank you @richgalloway, that's a good start. But I still need to group all of the DNS names impacted by that QID on the 'oldest' date in that column associated with that QID.  Can this be done?

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Try this

%base search%
| stats first(firstdisc) as firstdisc, values(*) as * BY QID, POAM_ID, Controls, signature, DIAGNOSIS, dvc, OS, duedate, PATCHABLE
---
If this reply helps you, Karma would be appreciated.

DBattisto
Communicator

Hey @richgalloway, thanks for the responses. Still not what I'm looking for so I decided to go another way:

I created a job that runs after the vulnerability scans that queries all of the QID's and puts them into a lookup file. I initially ran the job manually, but now when it runs on schedule, there is a subsearch to only append the QID's not currently in the lookup. Looks something like this:

%base search% NOT [|inputlookup firstfound.csv | fields + QID]
| eval firstdisce=strptime(FIRST_FOUND_DATETIME, "%Y-%m-%d") 
| eval firstdisc=strftime(firstdisce, "%Y-%m-%d")
| sort firstdisc
| dedup QID
| eval firstdiscev=firstdisce
| table QID, signature, firstdisc, firstdisce, firstdiscev
| rename firstdisc as firstdiscsv 
| outputlookup firstfound.csv append=true

There are a couple extra fields in there (firstdisce, firstdisev), but they're needed in other dashboards...

Thanks again 🙂

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