Splunk Search

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

greekleo89
Loves-to-Learn Everything

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

Labels (4)
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

greekleo89
Loves-to-Learn Everything

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[ [-]
     { [-]
       displayNameRemote Script Orchestration
       namerso

.....

......

 

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

Tags (1)
0 Karma

greekleo89
Loves-to-Learn Everything

@gcusello  any updates?

0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...