topic Re: How do I sort vulnerabilities by reltime? in Splunk Search

How do I sort vulnerabilities by reltime?

LoganRhamy — Tue, 13 Feb 2018 18:58:58 GMT

earliest=-100d index=nessus OR index=nessus_workstation severity_id!=0 severity_id!=1 
| dedup signature_id sortby _time 
| join signature_id 
    [ inputlookup nessusLookup.csv
    | fields signature_id assigned_person status notes]
| search assigned_person="ryan*" 
| reltime 
| join type=outer signature_id 
    [ search earliest=-4d index=nessus OR index=nessus_workstation severity_id!=0 severity_id!=1 
    | stats count(dest_mac) as TotalHosts by signature_id ] 
| sort -reltime -severity_id -TotalHosts 
| table signature signature_id severity_id assigned_person status notes reltime TotalHosts 
| rename signature as Signature signature_id as ID severity_id as Severity assigned_person as Owner status as Status notes as Notes reltime as "First Seen" TotalHosts as "Total Hosts"

The problem:
So I am wanting to sort vulnerabilities by the oldest one first. When I go through reltime Splunk sorts the string realtime returns. So instead of 28 days being smaller than 1 month it is marked as bigger. I have been fighting with this for a little bit now and I just think I am only seeing trees instead of the forest. Anyone want to poke me and send me off down the right path?

The query:
The first two lines are to get the base data where I want it to be
the first join adds my lookup table to the mix (I do not want them to be automatic lookups)
Then we filter for Ryan
Then we add reltime
the second join is so we only see the hosts most recently affected instead of getting all hosts that have ever been affected
then we sort
then we make a table
and we tidy up by renaming

Re: How do I sort vulnerabilities by reltime?

elliotproebstel — Tue, 13 Feb 2018 19:16:43 GMT

First - to address the goal of sorting vulnerabilities from oldest to newest. Replace your current sort statement with this:

| sort 0 _time -severity_id -TotalHosts

The field _time contains an epoch timestring, so you can sort by that to go from smallest (oldest) to largest (newest).

You should also replace this:

| join signature_id 
     [ inputlookup nessusLookup.csv
     | fields signature_id assigned_person status notes]

with this:

| lookup signature_id nessusLookup.csv OUTPUT signature_id assigned_person status notes

This is more efficient and less subject to silent errors.

Re: How do I sort vulnerabilities by reltime?

LoganRhamy — Tue, 13 Feb 2018 19:35:44 GMT

switched the lookup to lookup nessusLookup.csv signature_id OUTPUT signature_id assigned_person status notes

works like a charm. I knew I was missing the forest from the trees, thank you

Re: How do I sort vulnerabilities by reltime?

elliotproebstel — Tue, 13 Feb 2018 19:37:59 GMT

Oh, yes. Good catch on the syntax for the lookup. And glad to help!

Re: How do I sort vulnerabilities by reltime?

elliotproebstel — Tue, 13 Feb 2018 21:55:39 GMT

BTW, thanks for the karma points! In the future, if you want to give someone karma points without subtracting from your own pool, you can click to upvote an answer/comment. That way, your karma pool isn't reduced.