topic Re: Question about SPL using data from a lookup in Dashboards & Visualizations

Question about SPL using data from a lookup

PNG — Tue, 24 Aug 2021 07:42:26 GMT

We’re running Splunk 8.1.2 on RHEL 8.x and are using some dashboards that makes use of a lookup file “itsp_compliance_settings.csv” with an exemple below

host_environment,title,setting,must,value

…

Production,IP default-gateway,default_gateway,equal,1.2.3.4

Production,IP default-gateway,default_gateway,equal,5.6.7.9

…

This is an extract of the search behind the dashboard using the above lookup

index="cisco_ios_config" sourcetype="ApplianceConfigurations:Cisco:IOS"
| dedup host
| fields - tag, -_raw, - tag::eventtype
| rex field=source "\/usr\/local\/rancid\/var\/(?<host_environment>\w+)\/configs\/"
| rex field=source "\/usr\/local\/rancid\/var\/\w+\/configs\/\w+-\w+-(?<extra_host_environment_check>\w+)-"
| lookup ITSP:Compliance_Settings host_environment
| eval zip=mvzip(title, setting, "||")
| eval zip=mvzip(zip, must, "||")
| eval zip=mvzip(zip, value, "||")
| mvexpand zip
| makemv delim="||" zip
| eval title=mvindex(zip,0)
| eval setting=mvindex(zip,1)
| eval must=mvindex(zip,2)
| eval value=mvindex(zip,3)
| foreach *
[ eval field=if("<<FIELD>>"==setting,<<MATCHSTR>>,field)]
| fillnull value="Setting not found" field
| mvexpand field
| eval fail=if(trim(field)==trim(value),if(must=="equal",0,1),if(must=="equal",1,0)) | stats sum(fail) AS "Count" by title
| rename title AS "Setting"
| eval Status=if(Count > 0, "error", "ok")

Can someone please help and tell me if this is possible to adapt the search to take into account more than 1 possible values (2 default gateways are both valid) in the lookup as per the above example ?

Thanks

Re: Question about SPL using data from a lookup

manjunathmeti — Tue, 24 Aug 2021 08:45:28 GMT

Hi @PNG,

Your query will not work if one of the columns contains blank values in csv file. Try this:

index="cisco_ios_config" sourcetype="ApplianceConfigurations:Cisco:IOS" | dedup host | fields - tag, -_raw, - tag::eventtype | rex field=source "\/usr\/local\/rancid\/var\/(?<host_environment>\w+)\/configs\/" | rex field=source "\/usr\/local\/rancid\/var\/\w+\/configs\/\w+-\w+-(?<extra_host_environment_check>\w+)-" | append [ | inputlookup ITSP:Compliance_Settings] | stats latest(_time) as _time, max(*) as * by host_environment,title,setting,must,value | foreach * [ eval field=if("<<FIELD>>"==setting,<<MATCHSTR>>,field)] | fillnull value="Setting not found" field | mvexpand field | eval fail=if(trim(field)==trim(value),if(must=="equal",0,1),if(must=="equal",1,0)) | stats sum(fail) AS "Count" by title | rename title AS "Setting" | eval Status=if(Count > 0, "error", "ok")

If this reply helps you, a like would be appreciated.

Re: Question about SPL using data from a lookup

PNG — Tue, 24 Aug 2021 10:44:17 GMT

Hi @manjunathmeti

Thank you for your reply but my question was actually about how to deal with a column having multiple valid values, one value per line (so far, we've dealing with only 1 entry).

I think the "eval" should be adapted to cope with that. Can you help ?