Splunk Search

How do I sort vulnerabilities by reltime?

LoganRhamy
New Member
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

Tags (2)
0 Karma
1 Solution

elliotproebstel
Champion

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.

View solution in original post

0 Karma

elliotproebstel
Champion

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.

0 Karma

LoganRhamy
New Member

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

0 Karma

elliotproebstel
Champion

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.

elliotproebstel
Champion

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...