Fun With Queries

[sabell2012]

So, I wanted to create a glide query that would give me a count of all records with sys_updated_on or sys_created_on between two dates.

Table: cmdb_ci_computer
Query type: Aggregate - COUNT

There are a couple of approaches that can be used here:

1. GlideAggregate query
2. Encoded query

I really, really wanted to do this in a single GlideAggregate query. Something fancy. So began my experimentation.


1. Design the query in SQL.

NOTE: With GlideRecord or GlideAggregate you get a built-in benefit in that all inherited table's fields are present for execution of the query. With gs.sql queries you don't get that nicety and have to do the join yourself. Thus my models required a join to cmdb_ci to pull in the sys_updated_on and sys_created_on dates.

First my preferred model:

SELECT COUNT(*)
FROM cmdb_ci_computer co
JOIN cmdb_ci ci ON co.sys_id = ci.sys_id
WHERE (ci.sys_updated_on BETWEEN '2013-02-04 19:00:00' AND '2013-02-05 19:00:00')
OR (ci.sys_created_on BETWEEN '2013-02-04 19:00:00' AND '2013-02-05 19:00:00')

Second, an expanded model since GlideRecord nor GlideAggregate cannot do BETWEEN:

SELECT COUNT(*)
FROM cmdb_ci_computer co
JOIN cmdb_ci ci ON co.sys_id = ci.sys_id
WHERE (ci.sys_updated_on >= '2013-02-04 19:00:00' AND ci.sys_updated_on <= '2013-02-05 19:00:00')
OR (ci.sys_created_on >= '2013-02-04 19:00:00' AND ci.sys_created_on <= '2013-02-05 19:00:00')


2. Now off to model the query in System Definition / Scripts - Background

This is the best place to play with queries (or just about any script). You get immediate feedback and if you spend the extra time the code can be literally cut-and-paste into your final solution location.

3. First I tried to get everything into a GlideAggregate. I was shooting for an elegant solution...right?

Try 1:

var startDate = "2013-02-04 19:00:00";
var endDate = "2013-02-05 19:00:00";

var grComp = new GlideAggregate("cmdb_ci_computer");
grComp.addQuery("sys_updated_on", ">=", startDate);
grComp.addQuery("sys_updated_on", "<=", endDate);

// Now add in the second part of the query
var grORComp = grComp.addORQuery("sys_created_on", ">=", startDate);
grORComp.addCondition("sys_created_on", "<=", endDate);

grComp.addAggregate('COUNT');
grComp.query();

if (grComp.next()) {
gs.print("Count=" + grComp.getAggregate('COUNT'));
}


Viewing the results:

SELECT count(*) AS recordcount FROM (cmdb_ci_computer cmdb_ci_computer0 INNER JOIN cmdb_ci cmdb_ci0 ON cmdb_ci_computer0.`sys_id` = cmdb_ci0.`sys_id` ) WHERE cmdb_ci0.`sys_updated_on` >= '2013-02-04 19:00:00' AND cmdb_ci0.`sys_updated_on` <= '2013-02-05 19:00:00'

*** Script: Count=3

So, nuts, it ignored the addOrQuery altogether! Next I substituted an addQuery for the addCondition and that was ignored as well.


Okay, so try 2:

When in doubt break it apart (brute force...there goes the elegence):

var startDate = '2013-02-04 19:00:00';
var endDate = '2013-02-05 19:00:00';

var count = new GlideAggregate('cmdb_ci_computer');
count.addQuery('sys_updated_on', ">=", startDate);
count.addQuery('sys_updated_on', "<=", endDate);
count.addAggregate('COUNT');
count.query();

var countCreated = new GlideAggregate('cmdb_ci_computer');
countCreated.addQuery('sys_created_on', ">=", startDate);
countCreated.addQuery('sys_created_on', "<=", endDate);
countCreated.addAggregate('COUNT');
countCreated.query();

var recordCount = 0;
if (count.next()) {
gs.print("sys_updated_on=" + count.getAggregate('COUNT'));
recordCount += parseInt(count.getAggregate('COUNT'));
}

if (countCreated.next()) {
gs.print("sys_created_on=" + countCreated.getAggregate('COUNT'));
recordCount += parseInt(countCreated.getAggregate('COUNT'));
}

gs.print("TOTAL COUNT=" + recordCount);

Results:

SELECT count(*) AS recordcount FROM (cmdb_ci_computer cmdb_ci_computer0 INNER JOIN cmdb_ci cmdb_ci0 ON cmdb_ci_computer0.`sys_id` = cmdb_ci0.`sys_id` ) WHERE cmdb_ci0.`sys_updated_on` >= '2013-02-04 19:00:00' AND cmdb_ci0.`sys_updated_on` <= '2013-02-05 19:00:00'

SELECT count(*) AS recordcount FROM (cmdb_ci_computer cmdb_ci_computer0 INNER JOIN cmdb_ci cmdb_ci0 ON cmdb_ci_computer0.`sys_id` = cmdb_ci0.`sys_id` ) WHERE cmdb_ci0.`sys_created_on` >= '2013-02-04 19:00:00' AND cmdb_ci0.`sys_created_on` <= '2013-02-05 19:00:00'

*** Script: sys_updated_on=3
*** Script: sys_created_on=4
*** Script: TOTAL COUNT=7

And that worked ok. I got my record count, but had to play the brute force game to get it!


4. Next try an encoded query

You can hand roll this, or a much easier way is to go through a list view filter. Much simpler in execution and lines-of-code, but harder to maintain.

The following wiki article is a good starting point (See section 2. Generating Encoded Query Strings through a Filter):

https://wiki.servicenow.com/index.php?title=Embedded:Encoded_Query_Strings#Generating_Encoded_Query_Strings_through_a_Filter

After setting everything up in my list view for cmdb_ci_computer I ended up with this encoded query:

sys_updated_onBETWEENjavascript:gs.dateGenerate('2013-02-04','19:00:00')@javascript:gs.dateGenerate('2013-02-05','19:00:00')^ORsys_created_onBETWEENjavascript:gs.dateGenerate('2013-02-04','19:00:00')@javascript:gs.dateGenerate('2013-02-05','19:00:00')

So, rewriting my GlideAggregate query a bit; I end up with:

var startDate = "2013-02-04 19:00:00";
var endDate = "2013-02-05 19:00:00";

var encodedString = "sys_updated_onBETWEENjavascript:gs.dateGenerate('" + startDate + "')@javascript:gs.dateGenerate('" + endDate + "')^ORsys_created_onBETWEENjavascript:gs.dateGenerate('" + startDate + "')@javascript:gs.dateGenerate('" + endDate + "')";

gs.print(encodedString);

var grComp = new GlideAggregate("cmdb_ci_computer");
grComp.addEncodedQuery(encodedString);
grComp.addAggregate('COUNT');
grComp.query();

if (grComp.next()) {
gs.print("Count=" + grComp.getAggregate('COUNT'));
}

Results:

SELECT count(*) AS recordcount FROM (cmdb_ci_computer cmdb_ci_computer0 INNER JOIN cmdb_ci cmdb_ci0 ON cmdb_ci_computer0.`sys_id` = cmdb_ci0.`sys_id` ) WHERE ((cmdb_ci0.`sys_updated_on` >= '2013-02-05 01:00:00' AND cmdb_ci0.`sys_updated_on` <= '2013-02-06 01:00:00') OR (cmdb_ci0.`sys_created_on` >= '2013-02-05 01:00:00' AND cmdb_ci0.`sys_created_on` <= '2013-02-06 01:00:00'))

*** Script: Count=5

And what is this??? My count is off by two! Close examination of the results shows that the time was ignored and a new time was substituted! Now that is a problem. I toyed around with this for a bit trying various possibilities...all hand rolled, and found that this appears to be a bug with gs.dateGenerate! Arrrgh!

Okay, undaunted, I rolled my own encoded query and this is what I came up with:

sys_updated_on>=2013-02-04 19:00:00^2013-02-05 19:00:00^ORsys_created_on>=2013-02-04 19:00:00^2013-02-05 19:00:00

So modified my code accordingly and broke it apart (easier to maintain/read/understand):

var startDate = "2013-02-04 19:00:00";
var endDate = "2013-02-05 19:00:00";

var encodedString = "sys_updated_on>=" + startDate + "^sys_updated_on<=" + endDate;
encodedString += "^ORsys_created_on>=" + startDate + "^sys_created_on<=" + endDate;

var grComp = new GlideAggregate("cmdb_ci_computer");
grComp.addEncodedQuery(encodedString);
grComp.addAggregate('COUNT');
grComp.query();

if (grComp.next()) {
gs.print("Count=" + grComp.getAggregate('COUNT'));
}

Results:

SELECT count(*) AS recordcount FROM (cmdb_ci_computer cmdb_ci_computer0 INNER JOIN cmdb_ci cmdb_ci0 ON cmdb_ci_computer0.`sys_id` = cmdb_ci0.`sys_id` ) WHERE cmdb_ci0.`sys_updated_on` >= '2013-02-04 19:00:00' AND (cmdb_ci0.`sys_updated_on` <= '2013-02-05 19:00:00' OR cmdb_ci0.`sys_created_on` >= '2013-02-04 19:00:00') AND cmdb_ci0.`sys_created_on` <= '2013-02-05 19:00:00'

*** Script: Count=7

Woohoo! That worked! So I now had two working methods. And wonder-of-wonders the encoded version turns out to be pretty easy to maintain. Hey! I got my elegant solution!

BTW, I found a sparse number of examples on query string queries, so ended up writing my own dictionary...a start of one anyway (which I will publish after I have polished on it a bit).

Enjoy!

Steven.