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

[mcomfurf]

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.

[richgalloway · Accepted solution]

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 | ...