How to alert if csv entry exists but not returned in search when comparing values?

[greekleo89]

Hi Guys,

I am comparing the values from a csv with those returned in a json format on a splunk search.

At the moment the search works as i want it.  But i noticed that in some instances the results from the splunk search do not bring back all the entries because simply for that customer they do not exist.

In the csv i have all the entries that should exist and match, and if one doesnt match then return it as a result, but where i am struggling is getting the search to also output and say, hold on, this entry with this value is in the csv but its not in the search.

The entries which are not returned in the search are important to us because it means something isn't turned on so we need to go to that customer and rectify it.

The search atm looks like this 




index=main sourcetype="my_stats" type="add-ons"
| spath config{}
| mvexpand config{}
| spath input=config{}
| lookup add-ons.csv "Configuration Item" as displayName OUTPUTNEW "Configuration Setting" as "default"
|stats list(type) as type list(displayName) as item list(name) as value list(default) as default list(owner) as owner by company

Thanks,
Greg

[gcusello]

Hi @greekleo89,

let me understand, you have:

• a lookup from a csv file,
• containing many fiels, one of them is "company"
• in the lookup there are some values that aren't present in the main search,

is it correct?

I didn't understand if this lookup is tha same already used in the search or another one, but it isn't relevant, please try something like this:

index=main sourcetype="my_stats" type="add-ons"
| spath config{}
| mvexpand config{}
| spath input=config{}
| lookup add-ons.csv "Configuration Item" as displayName OUTPUTNEW "Configuration Setting" as "default"
| stats list(type) as type list(displayName) as item list(name) as value list(default) as default list(owner) as owner count BY company
| append [ 
   | inputlookup  add-ons.csv 
   | rename 
      "Configuration Item" AS displayName 
      "Configuration Setting" AS "default"
   | eval count=0
   | fields displayName default count
   ]
| stats 
   list(type) AS type
   list(displayName) AS item
   list(name) AS value
   list(default) AS default
   list(owner) AS owner
   sum(count) AS total 
   BY company
| eval status=if(total=0,"Not present","Present")
| fields - total

if you want to display only non matching events, you add at the end "| where total=0".

Ciao.

Giuseppe

[greekleo89]

Hi @gcusello 

Sorry perhaps i was not being very clear - i ommited some search strings on there so apologies.

The csv is like this

Configuration Item, Configuration Setting
Boss, On
Rogue,Off
Report,Off


The results come back like this in the search:

config: [ [-]
     { [-]
       displayName: Remote Script Orchestration
       name: rso
.....

......

comapanyid:xxxx

The company ID i get to match by doing a dbxquery, please see the full search below

index=main sourcetype="_stats" type="add-ons"
| spath config{}
| mvexpand config{}
| spath input=config{}
| lookup add-ons.csv "Configuration Item" as displayName OUTPUTNEW "Configuration Setting" as "default"
|rename cust as company
|rename customerName as Customer
| join company
[| dbxquery query="SELECT * FROM systems WHERE status IN ('1') AND (sT=28) AND owner IN ('1','2')" connection="conn" ]
|stats list(type) as type list(displayName) as item list(name) as value list(default) as default list(owner) as owner by company



So if there is a mismatch form the results in the search to the csv then i would receive a result of the variables in the stats.

What i also need to do is:

lets say that in the search results 

there is no entry for Report,Off but this exists in the csv, i'd want to know about it so that i can go to that particular customer and turn it on, only then it will be visibile in the data we receive back from them.

Thanks,
Greg

[greekleo89]

@gcusello  any updates?