Random line breaks everything

[LoganRhamy]

earliest=-30d index=nessus OR index=nessus_workstation severity_id!=0 severity_id!=1 
| lookup nessusLookup.csv signature_id OUTPUT assigned_person status notes 
| 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 ] 
| search assigned_person="Ryan*" 
| sort -severity_id -_time -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"

When I run this query the Total Hosts column does not populate.

When I take out | search assigned_person="Ryan*" it runs fine

I have no earthly idea why this is breaking in that way. Any thoughts internet?

[gcusello]

Hi LoganRhamy,
if you run the first two rows of your serahc

earliest=-30d index=nessus OR index=nessus_workstation severity_id!=0 severity_id!=1 
| lookup nessusLookup.csv signature_id OUTPUT assigned_person status notes 

Is there field assigned_person and is there value Ryan*?
If not, the problem in in the lookup.

Bye.
Giuseppe

[LoganRhamy]

There is -

The referenced field in the lookup csv is populated by only Ryan (last name) or Drew (last name)

[gcusello]

Hi LoganRhamy,
let me understand: what do you want to find in your join, why do you use using type=outer?

After, If you run

earliest=-30d index=nessus OR index=nessus_workstation severity_id!=0 severity_id!=1 
| lookup nessusLookup.csv  OUTPUT assigned_person status notes 
| search assigned_person="Ryan*" 

and you don't find anything, try this search

| inputlookup nessusLookup.csv 
| search assigned_person="Ryan*" 

you should have at least one result, check the value of signature_id, maybe there a space in values or differences in upper/lower case.

Bye.
Giuseppe

[LoganRhamy]

So the join type is something I have never worked with before. When I started building my query it didn't work with the default and did work with the outer. That being said there has been a lot of changes since then so I am not 100% sure if it is still required.

So I think we are focusing in on the wrong problem. I am getting results from the query without a problem. I am not getting the results from the sub search. If I remove the line | search assigned_person="Ryan*" then I will get results from the sub search but the results will not be filtered to only include Ryan's only.

I did get results from both of your above queries as expected.

[gcusello]

Hi LoganRhamy,,
assigned_person isn't a field from the subsearch, it is a field from the lookup, for this reason the first thing is to be sure that you can find results before the join command:

 earliest=-30d index=nessus OR index=nessus_workstation severity_id!=0 severity_id!=1 
 | lookup nessusLookup.csv  OUTPUT assigned_person status notes 
 | search assigned_person="Ryan*" 

When this is sure, you have to debug the join condition, for this reason I asked information about the type of join: using a different type, have you results?

After, are you sure that the signature_id of the events that match "Ryan*" are also in the join subsearch?
To check this you can use results from the first search in the second one.

Bye.
Giuseppe

[LoganRhamy]

I understand now, So changing the join type to left, outer, or inner did not make any improvements. I have also confirmed the signature_id match by copying one from the csv and adding it directly to the query.

Let me explain what I am trying to do because I am also open to other ways to get it done.

I need to produce a dashboard that will show a signature, who it is assigned too, any notes on the event, and when it was first seen. I also need a count of how many Hosts in our current environment have it.

Lines 1,2,3 7-10 complete the first task without a problem. It is when I introduce the subsearch that is supposed to tell me how many hosts currently have the issue is where we run into problems. I run the primary search for 100 days because I need to know if it is older than 90 days since the first time that signature was seen. I run the sub search at 4 days because that will capture what is required to see what hosts are currently affected.

[HiroshiSatoh]

Is there a value in ”TotalHosts” until | search assigned_person="Ryan*"?
If so, please make sure that the spelling is not wrong as the search sentence is not wrong.

[LoganRhamy]

My apologizes for not being more thorough. No value populates during the run or once the job has been completed.

The field in the lookup is populated by only Ryan (last name) or Drew (last name)