An Exercise in Sensibility - Reversing Encoded Query Strings

[jbauguess]

Have you ever needed the opposite of something you queried for in ServiceNow?   I recently stumbled into that problem, while working on GRC, and found a fairly interesting solution.

The GRC Problem

I guess I should start by explaining why GRC was the cause for this code.   The problem to solve with GRC was relating controls to CIs.   Using "collect supporting data", you can create conditions that show what CIs are related to what control test definitions, and by extension, what controls.   However, this information is dumped into a temporary table (grc_control_test_sample_data), so it's not very useful for reporting.   It's also reliant on the control test definition having been executed at least once, and there are times where old data hasn't cleared, and duplicate values remain in the table.   So if you want to know what controls are related to what CIs, you'd have to do an awful lot of filtering.   In other words, it's just not practical.

The solution was simple on the surface: every time a control test definition was updated with supporting data criteria, or a configuration item was updated, get the query from the control test definition, query the table in question, gather the results, and write them in a custom table that stores control test definition, control, and configuration item.   This can now also be linked to policy via the m2m_policy_control table, so now we can see what policies and risks relate to our CIs.

But what about when the supporting data configuration changes?   You don't want old, irrelevant data in the custom table.   My first run at the problem was deleting the old records related to the control test definition to start, then creating new ones.   After all, they are simply report data, and their sys_ids don't matter.   However, as this table grows, deleting massive amounts of records at a time could get expensive in terms of speed.

I have an encoded query, so I know what I DO need, but what about the records I DON'T need?   If I could just reverse the query...

Encoded Query Reversal

Oh, wait, I can do that!

The bulk of the work on this was finding out exactly how ServiceNow creates the encoded query string based on the filter text.   I mapped that out, then created some logic to go through an encoded query, reversing each condition.   Now if you're looking for server CIs that are Windows servers in your encoded query, you can easily reverse that via code to get server CIs that aren't Windows servers.

The trickiest part of this code were two date conditions: Between and Relative On.   They don't have explicit opposites in the system like "=" and "!=" or "LIKE" and "NOT LIKE".   Between, as best I reasoned, was after the first parameter of the query (>) and before the second parameter of the query (<), then joining those with OR.   Relative On's opposite meant taking the "ago" or "ahead" and reversing them.

In practice so far, it seems to work slightly better.   The original work is in QA as we speak, scheduled for production next week if all goes well.   This refactoring probably won't make it into production for another month, based on how our priorities line up.

I don't know how useful this is to other people outside of the small purpose I gave it, but if you can use it, feel free.   If you can improve it, let me know.