topic Re: Alert if csv entry exists but not returned in search when comparing values in Splunk Search

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

greekleo89 — Fri, 09 Dec 2022 19:49:33 GMT

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

Re: Alert if csv entry exists but not returned in search when comparing values

gcusello — Thu, 08 Dec 2022 10:26:11 GMT

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

Re: Alert if csv entry exists but not returned in search when comparing values

greekleo89 — Thu, 08 Dec 2022 11:19:28 GMT

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

Re: Alert if csv entry exists but not returned in search when comparing values

greekleo89 — Fri, 09 Dec 2022 08:45:54 GMT

@gcusello any updates?