Hello everyone. Welcome back to learn more JavaScript
on the ServiceNow platform, we're going to focus on a powerful tool
for data analysis within ServiceNow. It's called GlideAggregate. This class is all about performing
aggregate queries like counts, sums, averages
directly onto your database. So if you're ready to crunch
some numbers, let's get started. Okay. Anyways, GlideAggregate extends
the functionality of GlideRecord and it allows us to perform complex
aggregate calculations without the need for multiple queries
or additional JavaScript processing. It's pretty efficient
and it's optimized for large datasets. So in a lot of the ways
where GlideRecord will be slow, GlideAggregate is the way you want to go
if you're handling just numbers of things. All right. so before we dive into GlideAggregate, one of the ways to count records
in ServiceNow is via GlideRecord. There is a method inside GlideRecord
where you could say getRowCount(). So in this example I have I'm saying, Hey, I want to create a new GlideRecord
on the incident table. Show me all the highest priority
incidents. run that query
and then tell me how many rows. So rows as in how many records
are on that table, do you find? So if I run the script: count of high
priority incidents, there's 27. that's a pretty simple query. And this instance is very small,
so it runs almost instantaneously. But think about if you have thousands,
ten thousands of records sitting on one table
and you start running these queries, they get bigger and bigger
and slower and slower and those can add up and slow your instance down
whenever these queries run. So whenever you do things
like want to count records or sum up the numbers on fields of records,
you want to instead use GlideAggregate. So to use GlideAggregate, it's
very similar to set up like GlideRecord. instead of GlideRecord,
you're going to say new GlideAggregate,
pretty straightforward. We're going to do it on the same table
incident. We're basically recreating that script
that we just saw about in GlideAggregate. So I can show you how that works. incidentGA is,
and then we're going to addAggregate(). So we're saying,
Hey, what do we want to do on this table? What kind of information are we
are trying to extract from the database? We want to know a count of records and then incidentGA, We're going to similarly to the GlideRecord
we want to add query. We're going to say priority is one. And then similarly to the GlideRecord, we want to make sure we say query. Okay, So now in the background ServiceNow is figuring out
how many records have the priority of one, and then it's doing a count of them
for us. In GlideRecord at this point,
we would say something like incident.next() so that we can iterate through
all of the records that are found. But in setting GlideAggregate, the thing that we're saying
let's iterate through, it's actually all the different aggregates
that we asked for, and then we're going to iterate through that. And so if I run a log statement here
now, count of high priority incidents and say our incidentGA.getAggregate() notice
that we have a addAggregate() up here. Now we're saying getAggregate() and count and let's run that and I am missing a parentheses. Let me go back. Hey, live debugging. here I forgot a quotation mark
actually for priority. Let me run that again. Oops. 27 Perfect. That's exactly the number I was expecting. We just went about it
in the GlideAggregate way. Okay, so now let's get a little bit
more complex. So say instead of just counting records, which is pretty straightforward,
now we can do some more complex calculations. This is where GlideAggregates
gets really awesome and useful. Say we want to sum up something
instead of just counting it. So I'm still going to work
on the incident table, but I'm going to change
my aggregate to the sum of of the reassignment_count. And so now I'm saying, hey,
go through all the incidents and then let me know how many times
all of my incidents has been reassigned. And that might be good data to know for. wow, a lot of our incidents
are being reassigned and what can we do to fix that? Let's change our query
to reassignment_count is not zero, just so that we can make sure it returns
valid data. Notice that I added
this does not equal to it. Same thing like GlideRecord. You can do that here and I'm going to run the query as normal. And then on the log statement,
let's make sure we log it properly and say
total reassignments on incidents. And then we want to mimic
what we're getting from our aggregates. And so instead of count, we're going
to say sum and then add a new parameter and say the sum of reassignment_count
Alright, let's run that. I don't think I missed a quotation
mark anywhere this time. Perfect. So according to my instance, my incidents have been reassigned at 26
times. Now, that would have been a lot harder
for me to do via other scripts like GlideRecord and GlideAggregate
was able to do that pretty quickly for me. And then finally, I want to show you
what you can do with being able to be more complex
about how you sift through your data. For this example,
I want to try to get the count of how many incidents are assigned to
each category that I have in my instance. So let's go back and change this back to count, to simplify things. So there's my one parameter of count
and my query. You know, let's get rid of
the query, we don't need it. We want to get all the incidents and figure out all the categories for them. But what we want to do
is add incidentGA.groupBy() and we'll say category. And now we're basically saying like, Hey,
I want to do a count. But based off of each category,
not just all of them. And so let's start over on this logging statement and say the category of this aggregate is incidentGA.category. And then the count of this is incidentGA.getAggregate(”COUNT”). Okay. I think that will work. But let's go ahead and run script
and see what happens. Okay? So I only have one row
and I know I have more categories inside of my instance,
so there's a problem. Let me go back. And here is the problem. I left it as if if we know we're going to go through multiple groupings,
then we want to make it a loop. So I change it to while. And so this little statement here is saying incident.next(), It's
going to keep going over and over again until all of my categories
have been completed. And so let me run that script again
and you'll see it ran perfectly fine. So I only have five categories
in this instance for the incident table, and it left
actually printed an extra record. that's the records
that don't have a category set. So I have four records that don't
have a category and then two records with the database category, ten records
with the hardware category, etc.. so you can see even in GlideAggregate, you're able to do a lot of cool stuff via subgroupings. You can do summing things up, you can count things based off of groupings. There's a lot of cool things
you can do with it and all of it runs much faster
if you do it via GlideAggregate. So make sure you use this if you're doing
any number crunching, cool. There's an exercise that follows this
video, so go ahead and check it out. Bye!