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

[DBattisto]

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:

DNS	QID	First Discovered	SLA Status
Server-1 Server-2 Server-3	Q-3333	1-1-2021	Overdue
Server-4 Server-5	Q-3333	2-1-2021	OK

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:

DNS	QID	First Discovered	SLA Status
Server-1 Server-2 Server-3 Server-4 Server-5	Q-3333	1-1-2021	Overdue

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!

[richgalloway]

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

[DBattisto]

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?

[richgalloway]

Try this

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

[DBattisto · Accepted solution]

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 🙂