I’ve had to strip this request of all potentially identifiable data but hopefully my requirement is still clear enough to understand.
I have a long list of logs that I need to filter based on the contents of 1 field (properties.statement) that contains a huge string of SQL code.
I want to retain all the logs where the SQL code can change a database or permission.
My first attempt was using this:
where(properties.statement ICONTAINS-ANY [Update,insert,upsert,create,drop,delete,alter,grant,revoke,truncate])
Unfortunately, (and quite obviously) This will retain whenever those words appear even when they appear within variables. This ends up retaining a log which statement is completely innocuous for example, SELECT * from Anywhere WHERE TheFieldUpdate = 1
This is undesirable as the word update in this case doesn’t indicate that the statement is updating anything.
I initially thought I’d include a NOT like this:
NOT properties.statement ICONTAINS-ANY [TheFieldUpdate,TheUpdateDB]
I’m happy to keep adding to this filter over time as we find occasions where variables have created false positives. But what I think is happening is that if a log includes one of those NOT words, it’s removed from the results even if it also contains a word we’re looking for. For example:
UPDATE something SET TheFieldUpdate = 0 WHERE TheFieldUpdate = 1
I need this log to be retained, as it changing the database using an UPDATE, even though it has TheFieldUpdate in it too.
I’m open to pretty much any solution to this including if there is a better way of ingesting the logs in the first place!
Thanks in advance for anyone that can give me some ideas!