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, an upvote 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, an upvote 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 🙂

View solution in original post

.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!