All Apps and Add-ons

Splunk DB Connect: How to compare stored database queries to detect when field (column) values are changed, added, or removed?

mcomfurf
Path Finder

We're indexing a database table that stores saved database queries, and want to alert when the stored queries are changed. We are successfully doing so, but have experienced numerous false alarms when a change is made only to the order of columns (eg SELECT Address,Postalcode FROM Customers is changed to SELECT Postalcode,Address FROM Customers)

A simple character count comparison at the Query ID level would provide an 80% solution. What I need is to detect when the field (column) values have changed, or if fields are added or removed from the saved query.

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

Perhaps this will help. The goal is to extract the column names, sort them, then compare them.

... | rex field=oldQuery "SELECT (?<oldColumns>.*) FROM" | eval oldColumnList=mvjoin(mvsort(split(oldColumns),","),",") | 
rex field=newQuery "SELECT (?<newColumns>.*) FROM" | eval newColumnList=mvjoin(mvsort(split(newColumns),","),",") | where oldColumnList<>newColumnList | ...
---
If this reply helps you, Karma would be appreciated.

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Perhaps this will help. The goal is to extract the column names, sort them, then compare them.

... | rex field=oldQuery "SELECT (?<oldColumns>.*) FROM" | eval oldColumnList=mvjoin(mvsort(split(oldColumns),","),",") | 
rex field=newQuery "SELECT (?<newColumns>.*) FROM" | eval newColumnList=mvjoin(mvsort(split(newColumns),","),",") | where oldColumnList<>newColumnList | ...
---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...

Secure Your Future: Mastering Upgrade Readiness for Splunk 10

Spotlight: The Splunk Health Assistant Add-On  The Splunk Health Assistant Add-On is your ultimate companion ...

Observability Unlocked: Kubernetes & Cloud Monitoring with Splunk IM

Ready to master Kubernetes and cloud monitoring like the pros? Join Splunk’s Growth Engineering team on ...