okay we are going to get started um good morning good afternoon uh good evening to everyone that has joined thank you again for joining uh office hours um today we got a really great topic with adam adam is going to present some great stuff so with that i am your host thomas davis uh i'm an outbound product manager and adam stout is the speaker a funny thing about this slide he and i uh almost don't even have that beard anymore i just got to get a recent picture that i can show so and adam's is a little bit different but i thought that was kind of funny uh so you know performance analytics office hours are for you uh these this this meeting is to show you new things or existing things that are out there give you ideas uh we base this a lot off of the questions that come in from the community and any use cases that we hear of so we try to gear our office hours around uh that so um you know take the time to really um you know digest yourself into this and really pick up on some great tips and tricks and some features of the platform and pa and reporting that that you can get uh this recording is uh this session is recording so you will be able to have access to it uh after we're done here uh like with every other office hours we will put this into um a blog with the recording and you'll be able to access it there for later as a reference or if you just want to go through it again for uh for whatever reason so uh q a we definitely want to hear from you during this so please ask your questions in the q a section and we will uh answer those as best we can if it's something that um should be answered by adam after he is done then we will definitely uh direct that to him once we are done uh if it's something that myself and also another teammate david who is on as well can answer it we will answer that why uh while the presentation is actually going so feel free to listen in uh you are muted uh but once we get to the q a if we need for you to uh clarify more with your question we may unmute you so um again this is for you so please get everything out of it that you possibly can there we go so upcoming session so in our community there is an office hours uh button on the main page of the performance analytics and reporting uh community page so you can actually go there and you can see uh anything that's upcoming and then you can also see anything that we have already done uh and this link again this this slide will actually be downloaded to pdf and it will be attached to the blog uh as well so you'll be able to access that and get those things but again hopefully all of you are familiar with our community but make sure that you go to in our community and click on office hours button and you can see an existing or any new ones for the future so uh as we keep saying during every office hours and we will keep reiterating this uh k20 uh was free this year so all of the labs that were there for pa and reporting are out there so please take advantage of that and learn as much as you possibly can and um you know those will be out there i think for the distant future so um i wouldn't wait too long but you never know so definitely get out there and get as much of that training as you possibly can so um here's something new that we added and it is really about uh making sure that you understand the best way to ask questions inside the community so here's some tips that we came up with and you know if you could kind of follow these the more information that you can give if you can give screenshots we understand that some information is uh private so if you can you know blur that out or block that out that's fine but a lot of times screenshots really help with the questions that are being asked so uh pay attention to this and you know this this is out in the community as well and if you can try to adhere to this as much as possible i think it will help you know get your answer your question answered uh probably faster in more detail for you um so to help you out quite a bit so with that i'm going to pass it over to adam so he can give us an incredible office hours about strategies to remove performance analytics scripts so with that uh adam i'm gonna unshare and give it to you thanks thomas so i should be sure i should have taken over now um so so today we're going to talk about performance analytics scripts so a lot of us use them a lot of us overuse them um and we wanted to talk about when you should use them and when you shouldn't use them so let's get started we'll dig right into it scripts are bad all right if anybody has any questions go ahead and post those in the q a um and we'll answer those okay well we'll go through a little bit more a little bit more detail in there but why why are they bad and they are they certainly have their use but we overuse them when i when i talk to customers they're they i see overuse of scripts over reliance on scripts and and the reasons why i don't like using scripts one there's a performance cost so pa scripts run every for every row in your indicator source so if you have 10 000 incidents you're looking at the script has to run 10 000 times one row at a time um that's not that's not free right there is a calculation that goes in there and that's absolutely needed in some cases because when i run it it matters but there's a lot of other cases where that data doesn't change and i end up calculating the same thing over and over and over and over and over and over again and it's just not helpful for us the second reason is and i think this is really the one that gets me the most is that it's only available in performance analytics when i use a performance analytics script i'm only getting access to that calculation in performance analytics during that run it means it's not available in reporting it's not available in predictive intelligence it's i cannot use it for interactive filters there's a lot more on this there's a post i wrote that goes into a little bit more depth about this but we're trying to drive behaviors in the process not just report on them so rather than just looking at you know where where are we at i want to make sure that i have that calculation if it's possible and it's not time-based that i have this calculation available for reporting for predictive intelligence everywhere else in the platform not just in performance analytics and then the last reason that that i i don't like them is maintenance so a lot of times we end up putting business logic in those scripts and now that business logic is often replicated in the in the application somewhere and if it's in a manual breakdown and there's new choices i have to maintain that manual breakdown if i have a bucket group i need to maintain that that that group um i just have more logic to put out and and particularly in some of these use cases that it's worse than others if i'm calculating the number of days uh that an incident's been open well there is no maintenance there right it's a calculation i need to run it every time and it ha and it's fast so in that case this you know these things don't matter but we're going to go through a bunch of cases where we have we have a performance hit uh i'm i'm blocking myself from using it elsewhere in the platform and i have to maintain it so i'm really getting the worst of all worlds let's go to a little bit more detailed examples so scripts can be very expensive performance wise uh and they just don't need to be so i i was i was working with a customer and they had a script that took six choices they had a choice field with six six choices in it and the way they wanted to report on it was that they had three groups so that those six mapped into three solar groups it was one was one to one one was two to one one was three to one something to that effect and they used a manual breakdown to do and manual breakdowns are in the platform you can you configure it and then you run a script to look up the manual breakdown but they were running on hundreds of thousands of records hundreds of thousands of rows which meant hundreds of thousands of round trips to the database during every job run so i ran my performance analytics uh my job it had to get the data get out get all the rows get the say 200 000 rows and then it had to go back and forth to the database to look up that manual breakdown the job was taking about eight hours to run i personally i i get a little cringy on a daily job that takes more than maybe half an hour and i like it in single digit minutes this job is running for eight hours we took we took the uh the job and we used one of these techniques we'll talk about a little bit later but we went from eight hours to 20 minutes and i believe that he did a little bit more tweaking and got it down into single digit minutes but eight hours down to 20 minutes by by applying one of these strategies so we'll talk about it a lot more detail but it can be very expensive and if you see jobs in the hours it's because you're running a script if that's that is my go-to any job that's taking more than than maybe half an hour unless you're unless you're running a historical job if i'm looking at a single day's worth of data and you're running more than the double digits of data it's a job or it's a it's a script in your job and especially multiple scripts can be problematic so this is a lot of the y um so we'll talk about that one more specific now scripts are there they're there for a reason right so let's talk about why i should be using a script and and why i use a script period so scripts are absolutely required for date calculations i only want to see scripts that have score start or score end on them those are two very those are special variables that come into play in performance analytics hopefully you're familiar with them but they are the start of the period and the end of the period on a daily indicator it's uh midnight in the morning down to uh 11 59 59 in the evening right the whole day when did the score start and end on a monthly it's from midnight on the first to uh midnight on the last day and when i'm calculating how long has something been opened to date i need to use the scorehead and so score starts acceptable too because that changes every collection period the only way you can do it is calculating it when you're collecting right and again it is special it's not now but it is when the period collected so if i'm if i have my job running for yesterday then it's going to be up through midnight this morning up through 11 59 59 last night so those are fine leave them um they're also very fast i i don't i don't have any major logic in here we're just doing a date calculation um it's you know something in milliseconds even if i have to run this 100 000 times it's very fast and it is different for every record effectively right the sys updated on is different for every record it's different for every run this is how i have to run it this is why scripts exist but let's talk about a bunch of other use cases that we see so man and this is the breakdown we're going to go through each and every one of these and again you'll have this deck accessible to you to go back and back and forth through it so uh manual breakdowns this was the eight hours to 20 minutes the performance of manual breakdown is poor because we are going just doing so many back and forth to the to the database there are there are alternatives to this general recommendation is don't use a manual lookup but we're going to talk about how to solve it the main all of these are there for we see them there for a reason we did them and if you have to do it you have to do it particularly in a proof of concept phase i need to get something out but we're going to talk about what's a better way to approach the problem grouping buckets uh they perform fine there are alternatives i wouldn't use them because there are alternatives and it has to do with maintenance but they're okay anything that that goes through that's performance is good i'm gonna have a hard time telling you not to use it um other than i think there's there's generally a better way hierarchies is a really special one the performance is can be poor because we are doing lots of back and forth with the uh with the database in some cases are alternatives in some cases there are not and so if you need it you need it then we're going to go through simple transformations uh date calculations that again that calculations are why we have scripts so certainly there's no alternatives for it and you're going to use them uh and then a couple of the other big ones we see are non-reference joins aggregates those those are both uh poor performance because they use queries the general performance issue is if you have a glide record or glide aggregate in your script itself it's bad um coalescing and and then dot walking aggregates performance is good uh there are there are some alternatives but if you need to use them you need to use them so let's get into these let's walk through what these look like um and you might have you might have a script and might not understand which ones of these are so we're going to go through an example and how you work around each one of these key thing to remember as we go through this that you can write pa scripts so you do write pa scripts that's what we all do i love scripts i love them and hate them it's a very complicated relationship but we got to remember analytics is a team sport we need to work together so if i'm i'm working as an analytics developer i'm i'm used to whether it's creating bi reports somewhere or i'm i'm in charge of pa and i create the indicators and i do the scripts and i do everything we cannot solve all the problems ourselves right we can we can do a pa script and solve the problem right now but i'm not solving the problem for my organization so we need to make sure we're working with our admins our app developers the process owners to apply these solutions as we go through them i there's a challenge here and that i'm going to take something that you can do by yourself right now and i'm going to ask you to work with other people to make sure we solve it better for our organization okay i understand that challenge but that is the challenge i'm putting in front of you all right let's go through some detail about what we're going to see so this is in the same order we had before manual breakdowns i have a couple examples here that we see with manual breakdowns and then we'll go through what it looks like so hopefully you can kind of go oh yeah that's my script and we'll talk about how you how do you resolve it so sys class sys class doing a manual breakdown on that what we see is this is the script and we'll see the examples of the scripts that i have a breakdown this is my breakdown i have a hard-coded sys id um i am getting the value of cis class name from my record from task from cmdb and then i have to query manual breakdowns table for the for the with the for that breakdown for that value what do i get what's the society i get it's a pretty simple standard script i'm sure we all see it somewhere in our instances the way i want to approach this though is instead of doing that query one at a time we're going to create a database view um and we'll see that with cmdb class i think this is out of the box now and what you get is cmdb joined to cm to assistdb object sysdb object while in the rec this is class name is a string which we can't break down by we need a sys id there is a sys id in every instance that gives me one sys id for every sys class name and so by joining in the database cisco the ci cis class name to database object name i now can get a one-to-one sys id for every record i want doing this the the on the performance side what's happening is we're doing this all in one query that takes slightly longer you know instead of being five seconds it's now let's say it's six seconds but what i'm cutting out is every query now doing one at a time right we're able to do one query instead of potentially hundreds of thousands of queries this is the use case that we took a job from eight hours to 20 minutes eight hours to 20 minutes it's it can be massive um as we go through this just to walk through this one is really the most complicated one to implement after the fact but we need to go through um there's again a few steps in here but it's in a sub prod environment delete all your scores you can't in this case we're going to change the indicator source from example from cmdbci to a view and you can't change a table with there's if there's scores there there's a ui blocker that's doing it but in a sub prod we can go in delete all the scores and i'm in a sub prod so that's okay you know make sure you're in a sub-prod then we're going to open an update set we're going to go in and go through the steps of create the new view because i want that in the update set we're going to add the new breakdown source uh to sysdb object and we're going to switch the breakdown we're going to switch the indicator source and then we're going to have the mappings but we're going to add the mappings from the breakdown source to the indicator to that to that new view so we have to configure all of that and then we can close the update set um and then uh and then we're going to push that update set to another sub prod and validate it works uh it should just move over quickly the ui will not let you change the control the uh the table uh for the or the breakdown source table but as an update set it will apply it but you want to validate that in a separate sub-prod that everything looks good all the data is there and then you need to run a historical collection because we're going to move manu manual breakdowns have their own system unique to themselves and now we're switching to a standard one we are changing those societies the history is lost if we didn't do the historical collection so it's up to you whether you actually need to do that or not um if you don't that's fine and if you do you'd have to run a historical collection to get the new breakdown societies um a question came in which is related about using the database view for interactive filters on on the ci class um i believe if your reports are working on there it should work if you built the breakdown on that it should be fine but that's relying on the report being on the view as well because the report is where that ci that that database view lives uh so there's there's some things you can do with the interactive filter some you can't but in this case the performance gain is massive um so it's something that we'd certainly want to look at being able to do and the note again the historical data in this technique we are changing the cis ids of the breakdown so they're lost and you'd have to do that historical collection all right let's talk about a couple more manual breakdown examples that we have so as we go through alternative groupings this is one we that we talked about where uh i have a categorization i have the six fields and i want to take it down to three it's just a manual grouping uh and we're doing it we're doing it in the script uh we're doing it on the fly that i i it is one of these six choices but i wanna make it one of these three the issue i have with this is that i can't use that in interactive filters i can't use it in reporting because it only exists at collection time in pa and so we want to look at the the same same design pattern that we can use with buckets we can use with groupings so this one's reusable in different cases but let's talk about what the script looks like and in the script today what i see is very similar to the cis class name that i have to go in and look up what i'm looking for and then what i'm trying to do is is grab um grab the manual value but based on in this case what's my my assignment group my assignment group has either front office network back office network front office hardware back office hardware i just group them in the front office and back office and then i get the manual breakdown for that instead of doing this right and i'm putting this logic in my script that i have to evaluate over and over and over again even though the group a group any given group is either front office or back office or unknown what i want to do instead is to add a new choice on the on in this case it would probably be on cis cis user group on sis user group i would just add the choice that i want i want to consider this group to be front office or back office i might put a business rule in to say if if it has the name front that i use it um that i put it as front office if i the choice front if it says the word back i do that or i cannot use a business rule just manually set it and require it when you create a new field when you when you create a new group you set where you want to put this in reporting wise right so it doesn't have to have the name front or back you can have whatever i want i can i don't have to worry about typos and by putting it on cis user group i now can use that anywhere i can report on cis user group i can use an interactive filters i can use it predictive intelligence i can use it in pa and it's faster so it's faster as part of my process i can make sure all new groups are bucketed into front office or back office and i can use it or elsewhere in the system this is a complete easy no-brainer win uh the one caveat is because we're moving from front from manual breakdowns that have their own unique sys ids to something else we have the same historical issues um so the sooner you can do it the better but uh it is something that that we'd want to do right at the end of the day i don't want to build any new any new manual manual breakdowns like that i want to solve the problem where it lives right this is an issue with how i categorize and how i report on assignment groups in this case let's put let's make sure that there is a field on assignment groups that clearly shows me what i'm going to report on and i will just use that in pa not try to mask it in pa okay and the last type of manual breakdown we see is a breakdown sandwich um it's a breakdown sandwich and the breakdown sandwich it's very much the same as everything we had before so let's take you region this is really common that i see this in location we add a region field as a string we had a a field especially in common location that's a string and we can't break down by strings so the way we solve it is a breakdown sandwich where we create a new manual breakdown and then we have a job that looks for all the strings and creates entries and manual breakdowns why right this is because in terms of p i was able to do it i either maintain that list manually or i have to write a job that admin has to run to populate it and the way we're going to solve this is incredibly simple we're just going to make that region field in the example the example here it's u region the region field on location we're just gonna make it a choice list when i import when we when we import the data going through an import set there's an option that just says if you see a new choice create a new value in the choice list so it's actually a lot less work for everybody involved if we just get the right people involved to say hey make this a choice list and then when you when you're importing it or creating it just check the box to automatically create it reference might be appropriate too whether it's a choice or a reference doesn't matter from the pa perspective um doesn't matter in most cases but if there's attributes then i might create a new table for my regions table um yeah but it's is very simple it is actually a simple exercise than what we did before uh using a breakdown sandwich but you have to leverage the platform to do it you have to you have to work with your admins work with your app developers to get it done correctly for all of these i don't want to see any more manual breakdowns it is there again it's it's really useful for a quick poc but it's going to hurt you manual breakdowns will hurt you because they require scripts to use them right and require the worst kind of script which has got a glide record in it okay so we're talking about annual breakdowns we're going to go through a few other examples so hierarchies if you've taken i think dan kane is is on the line if you've taken uh the k20 lab on hierarchies we talk about visualizing the data and hierarchies for for pa specifically we use a hierarchy script and what the hierarchy script does is it gets it says for current user so in this case assignment group manager i want to get that manager's manager and the manager's manager manager the manager manager manager manager manager manager manager however many there are uh super useful for reporting right we need we need to have it this is one where there are sometimes alternatives sometimes there aren't but this is the use case we see we see for it uh the script this is a very simplistic version of the script there's a little bit more to catch especially for some data errors but i highly recommend the the k20 session on hierarchy you'll see this example but what we want to do with this and we can do it in some cases and again maybe not in every but we can do it here for user well managers don't change all the time they don't change second to second so what we want to do is create a field we want to materialize the hierarchy as a list field which can handle n levels of values a list field on the base table so on the user every user record actually has a list of sys ids of all their managers for locations the location i'm in a city which is in a province which is in a region which is in a country which is on the earth all good stuff um but those don't change cities do not change uh states very often states don't change countries very often so we can just materialize that list in the location and now we don't have to have a script and calculate what country is new york in right what's what state is new york and what country is new york in we don't have to do that over and over and over again and if you think about it again if i had a hundred thousand incidents i'm looking at do you really need to look that up a hundred thousand times and and worse off is it's not a lot looking up once but you have to look up what's the parent what what state am i in what what region am i in what country i'm in it's four queries and if i'm doing a manager hierarchy and i have five levels of hierarchy six levels of hierarchy for a hundred thousand incidents i'm now doing five or six hundred thousand queries for that single indicator source every day it's an amazing amount of work when we can just go now just take we'll just put a business rule in that just says when a manager changes recalculate the managers and specifically for user hierarchy which is very common most of us have this in here um hr systems generally already have this so when i'm loading my data in from ldap when i'm loading in from workday when i'm loading in from wherever it's if it's already there let's just convert it to sys ids maybe i'll put a little transformation script to make sure it does the lookup on load but this might come for free for you um so make sure you look and see if you're already already doing it i i am there's a lot of customers i see are already doing this it may be not it may be broken out by like username or or the some other system id but you can convert that to cis id we do need it to be the service now systies for those users but you can do that on load once and now i can just use it and there's no script i just say breakdown by list pa natively handles breaking down lists which are what these things are you get all the power and and just none of the cost so make sure you look at this if you need hierarchies if you can materialize and materialize it the example where you can't do it might be see it would be cmdb uh if you have a hundred thousand records and there's different paths to go up and there's not a defined parent that that this is not a good fit for that um then we'd want to look at using the query builder that we talked about uh a couple sessions ago but again blue user location group hierarchies we want to materialize it the performance is staggering how much better we get right this is one of those ones that goes from multiple hours to to single digit minutes all right so we'll get into a couple of simpler ones and and the simple transformations all i want to call it is something like get format uh get hour of day that's what we're trying to do here um i think this is not about a box example um it just says i want the hour of day that the incidents were broken and broken up by again um it's this is going to be fixed by your time zone uh this is going to be it's derived right it doesn't change but if i'm if i'm evaluating my incidence every time i evaluate it in pa whether it's for uh open incidents uh sla breach cl close incidents right different sources i'm calculating the same thing over and over again this is very fast the performance is not big but the way i want to look at this or at least consider it would be to just add a new field on that table and have a br that comes in and sets it so if i want to know the hour of a day that the incident came in on once the incident is created that will never change so why do i want to recalculate it over and over again there is a balancing point here we don't add 300 fields to incident for every slice of day here at every time zone so there's some subjectivity in this and again the performance is fine but with the performance i still can't use in reporting i can't use it in a pivot if hour of day matters to me by putting that on the field i now can use it in a pivot chart i now can use it as an interactive filter so i get more flexibility from it there's some subjectivity here again you got to use some reason but i want to look at do i want to materialize that instead of just using my pa script all right up next is date calculations okay they're fine right date calculations that's why we have scripts my note here is that this is a bad one why why is it bad because there's no no start no score start and score end if this is actually what i'm doing and this is common right how long did it take to resolve time to resolve well that can never change without open dat or start or resolve that changing right and open that's not going to change but resolve that can change if this is really what i'm looking for then why am i calculating that over and over again why not put a resolved time as a resolve duration and hopefully i'm using slas and i already actually already get that and i get business time and all that fun stuff but if for some reason i'm not if this was what i was doing i would put this on the field and just go how many hours did it take to resolve this this task but that's not what we're looking for right if we're really looking for how long did it take my current backlog how old average age of current backlog i don't want to have this script i want to have something that looks more like this that handles if the resolve that isn't empty put in the score end if it was not empty by if it was not resolved it's not resolved now then we're going to com we're going to compute it as it not being resolved by midnight last night um when when the job uh ran right or the job ran early this morning for last night if there is a resolve that then use the result at but if there's not we're going to use score n so this script is perfect the way it is it's just giving me the number of days it took to resolve this from when it opened at and if it's not resolved i'm calculating the age if you don't use score end here and you just do resolve that then you won't know how old something is until it's done and we want to be looking at backlog so again score end is here there's no glide record this is good my general rule is if i'm not using score start and score end i don't want to use that that script right score start and score end is what i am looking for that's why scripts exist they're fine outside of that i i'm i'm really gonna scrutinize why we're using this script okay a couple more use cases we have we're wrapping up pretty soon with this uh non-ref non-reference joints so what i mean by this is i i see this most often if i'm looking at cis created by cis updated by what users are are updating this record and the way we query i was instead of you employ this should be cisuser so in cisuser i'm in a query by the created by so every record what's happening if i'm looking through um my hundred uh i'm looking through all my prv's i'm looking through 20 000 prbs what i'm getting what i'm getting from this is i want to get the department's executive that sponsors it some kind of random thing but created by is not a join it's it's period not not a joint right it's just a string i have to query it but if i'm looking at ten thousand prbs i now have to run this query ten thousand times the way i'm going to solve this we've seen this approach before and you'll see that there's recurring themes i i got i have two choices so one i'm gonna fix the data model to be a reference if this is user created then just fix it to be a reference right why have a string and join right we see this a lot with with applications or tables created by people newer to the the servicenow ecosystem they don't really understand references and they think you can join on anything it's just a normal database so the thing i'm going to go for for anything custom is i'm going to just can i fix it so that it is a reference right have this be a table have it be a reference to the table um in the example we saw it was cis created by well cis created by as a string for reason what i'm going to do is create a view i'm going to create a view that does the join between cis between my table whatever table that is created by and cis user joined on username and again instead of doing a query per row i now do one query that takes marginally longer very small amount of time longer and i skip all those steps i do not want to have glide record or glide aggregate in my scripts okay and now aggregates so aggregates come in where i want to count the number of tasks that are assigned to me all right i want the number of open tasks open change tasks for every change uh example the example i actually have in here is the the total allocation time the total amount of time you have allocated for every day in my resource plans um and again what happens is for every row i need to query i need to run a glide aggregate to go get this data everyone i have to go get it one at a time and so if i'm looking at um 10 000 users i have to run this query ten thousand times a glide i read it ten thousand times now every one might be a second second's not long right until you start multiplying it by ten thousand and what other work is going on so the way i wanna approach this is generally i wanna use a job to pre-aggregate it instead of doing one job one query per row i'm going to run one query with a group by taking one query with a group by anybody with the sql background knows that's going to be a lot faster i will write it to a table and then performance analytics can just use that table and then i don't need to have a pa indicator i just have my my table that i can query it's nice because in a lot of cases i can report on that table as well i can do pivots on that table if it helps me and whether i run the job to pre-aggregate or i'm just maintaining that aggregate table in via a business rule that's up to your process that's up to a lot of other things you want to look at but ostensibly i don't want to just do it in the pa indicator to get that data right now but i want one at a time but i want to either do it in a batch which is a lot more efficient or just keep it up to date and then leverage that for reporting as well okay so i might have said this before i don't recall but no glide aggregates and no glide records in your scripts and coalescing so i'll use coalescing a lot as well so my use case here is i want my incident i want to report on my incident the broke down by company but not every incident has a company so if it doesn't then i want to look at the user's company right so look here and if this field's empty then get it from here and we see this in the real world there's not there i don't have perfect complete data everywhere i want to go so i have this little javascript function it's really simple that just says get it from here or get it from there the performance of this there's no glide aggregate there's no um there's no glide aggregate there's no glide record performance is okay if you want to use this i'm not going to yell and scream at you but i am going to tell you don't do it because what i want to do instead because i want to be able to report on it as well is i'm going to create a field a third field or another field i might if we were using incident company i would do incident company resolved and what that's going to be is a business rule that when the user changes or when the company changes on the incident it'll do that coalesce for me and put that in a field so i can just report on reporting company right or effective company or something to that effect there's some holes with this in that if users change companies a lot you know i may not update it with some function fields you might be able to do it um but it is something that you want to look at being able being able to do this uh not every case will do it so you may end up with your coalescing script and again performance is okay but look and see if you can do that because now i can do my interactive filter on the effective company i can create a pivot by effective company there's so much that you can get from this it's so much more usage in the platform that we want to take a look at it okay and i think we're coming up to the last one here dot walked aggregates so this one the script super simple just um if i if i have a script if i if i'm trying to count distinct managers from the assign to managers the ui won't let you pick that right now um so i create a script that's assigned to that manager if you do this one i am not going to kick and screen um because you can use this in reporting that's fine right because you can dot walk in other places we just can't do it right here it's very efficient so it's okay if you want to do this but just in the land of i don't like using scripts they don't have score start and score end what i might do is just create a database view that joins this in for me um and then i can report on that uh this one for me specifically it's kind of hit and miss whether i do it it's i believe it is in the idea portal it's something we're working on to allow you to not have to use a script or create a database view but if i'm doing it today and i'm going to be pure about it i probably would use a database view so i don't have any scripts right every script i have if i don't have score start and score end i don't actually need that script um again this one is you got a little bit of leeway in here because it performs fine you can use it everywhere else in the system and there's no maintenance it's just that i don't like it okay so let's wrap up with a couple a couple questions we'll we will answer um but a couple of key things in here we went through a lot of use cases and again you'll you'll have the deck as an example to walk through um so not all scripts are the same absolutely right they all do kind of unique things each and every every one of us is unique just like everybody else but there's really similar ones so as you saw as we went through it we used views to do a lot of things right we made that heavy lift into the single view rather than into the query into the one at a time we want to stay away from one at a time take a look at what you're trying to do and go what's the best strategy to remove it right these this is a big list of strategies this is not every script hopefully it covers it covers most of them um but i i the com the more complex it gets to be in your pa script the more of a use case you have the more of a reason you have to put that into your process the second part is make sure you're leveraging the now platform right you pa is not a standalone application it is part part of servicenow part of the now platform just because you can doesn't mean you should um and and i always want to look at where else can i leverage this data if it's important enough to report on why am i not if it's important enough to put to trend it to put in performance analytics why am i not being able to put that in a report and pivot by it and use an interactive filter by it and have routing rules based off of it and use it for predictive intelligence right don't steal the data from the rest of the platform put it in the process let people see it make sure that it's right expose it right transparency is key for us and the reason for all of this is we're trying to we're focusing on improving the process i do not care if we are you know what we're reporting on my goal is not to report better it's to have a better process we're using pa to help us improve that process so don't mask the issue and i know this is this is hard but when you do it in a pa script you're masking it right you're showing it one little crack of it they're showing just a little bit of the glimmer of light but there's so much more we can do with the now platform we are trying to drive behaviors not just report on them right that that's performance analytics that is servicenow that's what we're trying to do here okay we're going to answer your questions so let's go through it um if we don't get to them and we have some we have some good time here to talk about these or talk about other questions that come up go ahead and submit those in the q a but if we don't get to it then just post it in the uh analytics intelligence and reporting forum in the community um feel free to tag me and thomas and david that are that are on the line um we do all look at these but man there's just some great feed great contributors out there i love it because i'm not answering everything anymore i appreciate a lot of you on the phone answer a lot of things so if you have questions you know post there ask questions um and let's get to a couple of questions that we have today so adam before you get to that one there was a question from greg prior to this one where he was basically asking uh if someone did not have um you know experience in javascript uh what would be a good place to recommend start learning so i'd actually send in the links to a couple of the uh now learning courses uh for you know scripting inside of servicenow and then he had a follow-up question that maybe you can answer that we'll uh go a little bit further with that uh sure so so with servicenow it's it is just javascript so there's plenty of sites out there to go through in a basic syntax of what goes on um the classes and now learning are awesome they they're i try to get through one of the short ones every week or two because there's just so much out there and such great examples i've i've never actually worked for a company that had such great training and a lot of it both that stuff's free now uh you go through that you go through the coding one of the biggest things i would say with with the scripting is while javascript syntax is fine servicenow has a a lot of powerful apis that do what you're trying to do there are we have a huge user community and we're all trying to do things so something seems complicated um one you search google for you know servicenow example of what you're looking for and you'll see lots of stuff out there but use the developer site as well particularly when you're writing some code you're looking for apis um examples so things like what do i do with glide record or glide query a for loop is standard javascript but all the glide apis you're going to want to look at developer search the community because the developer community has got probably 10 times the sorry the platform community has probably 10 times the volume that that our analytics community has but just there's a ask questions um take so take some of the now learning classes look on the internet to get familiar with javascript in general and then ask questions is there what's the best way to do this and you will you will be amazed at how how helpful the community is um and the scripting academy there yeah okay um did i drop out uh it was kind of all of a sudden so maybe you did hope but you're back now so good good um and then a question is is p scripting with npa this great the same as servicenow yes whether there's a couple of variables like score start and score n that are only available in scripts um and that happens in per in other places like does current exist or not in a business rule it does in pa it does in a job it doesn't um a couple minor minor things like that but javascript is what we use in pa scripts it's what we use in business rules it's what we use in jobs it's what we use on the client side so there's minor differences between client side and server side but the scripting is essentially the same so find an example that does something close and go after it um so a lot there there's just an amazing amount of resources for what you want to do for for learning service now okay uh i think we have a follow-up in here uh the follow-up in here uh about using i think this is about using the cis class name as an interactive filter um that gets to be a little tricky as we go through um uh so uh when you are you when you are using uh yeah to get cmdb tables actually is probably a pretty good example of it um if you have a question about how do i if you have a question about specifically implementing one of these i posted the community to get some feedback uh these were pretty generic examples that come through but they're all they were all real at some point um so if you have a question about how do i get this to work post in the community and get some more details on it uh it's it'll be hard to explain or talk about some of the coding uh here so go on from there um so i talked just to somebody had a question about clarifying that i talked about creating views for data and i i am specifically talking about creating database views in the platform so their views are something slightly different and data sources are something slightly different but we're going to create database views which we then will use as a report source or a database source um then i clarify every when i'm talking about it here i'm i'm specifically talking about a database view and the database view we talked about that a couple sessions ago too if you if you missed that one we did an office hour specifically on when to use database views and that allows us to join data and join data about as effectively as we can do it for not having a dot walk all right i think i've gotten through all the questions that have been submitted um we're getting closer to the end of time um but you can feel free to submit any more questions about this or anything else so yes we just got one more command there adam um david are you on the line i am yeah so a question came in your metrics expert um a question came in and i'm curious your opinion on this uh so for reporting on metrics and metric instance um it's secured right it's blocked it's blocked from general users i guess do you have any concerns about granting itil users read access to metric instance uh no but i would say what's the what's the the reason is more important right so um i would mine it's like a raw data uh capturing mechanism right the the metrics and on top of that you either build your reports and those you make visible to the various groups um without having even that can be done even without having access to the underlying records or you do it even better through performance analytics so unless you want to basically expose the the metric instance records themselves for example in the in the forms through related lists uh because they they then show the data there um yeah i see no reason why you would want to do it so if you could basically add to the to the to the question uh maybe a follow up with with extra reasoning then that would be interesting to to to basically have the discussion uh and i think with that it's i think the most not all the metric instances it's an option but i think everybody can see them in the metric timeline if you just wanted to view them but it really does matter what are you trying to show there is there would be a minor security concern um specifically uh because you're capturing data in there i i would only i would generally restrict the metric instance data to only if you can see the incident can you see the metrics for it from all my itil users i i'm not super concerned about this in most things when i'm talking about state and priority and assignment group um but particularly if i were getting into the world of hr there may be cases where i do not want to expose that data right just even the aggregates are sensitive yeah obviously you could just add additional acl rules um to prevent that from happening but um yeah read-only rules it's fine right it is just a table and even though it's a it's a system table sort of um there there is no reason why you would not do it it's not like you're going to run into upgrade issues right right it's it's fine uh i do i i actually i let lots of people see it it is great data right i think the big thing is don't hide it right make sure you're telling the story you're explaining it but without capturing the data it would be even a bigger problem to not expose the data simply because there is no out of the box default view rule on it that would be like uh detrimental to the to the goal that their truck that you're trying to achieve with the metrics and and to add what you just said about the view rule um that i i generally don't expose metric instance but i'm going to expose uh a view for it so in incident metric i think is what it is if i needed it on something that didn't have it already you know i'd create defect metric uh create a view that joins the table the the base table to that metric to metric instance with metric you'll see the examples incident metric is out of the box as a view but if you use that view then they're able to dot walk like show me my incidents they're not just querying everything raw but they're able to see uh you know just the metrics for theirs or their group or however they want to report on it's a lot easier to report on if you leverage it with a view that's even the better uh practice uh adam because then you you can you only only also don't have to worry um for for example the hr records that you just mentioned because the basically the the contains it all table will still not be that visible to your end users unless they really know where to go yeah so make sure you're telling the right story that is uh but it's fine generally um and then be sure uh the question about creating new metric definitions um that's the metric admin role i believe needs you need for that um and i wouldn't let my users go crazy um but you know if if it makes sense to do then to do uh and be careful when you're creating new metrics make sure you're looking at does it make more sense to have an s is this something that actually should be sla-based um that you want to report on but it gets a little iffy where i have sl and we talked about this in a couple sessions ago where i have metric sla breakdowns um and sla reporting in some cases is or is better if it's available compared to just metric definitions but metric definitions are fine if you don't have slash on them okay well i think we got through the questions uh thomas you want to wrap it up for us sure uh i did see that um and and i do not want to pronounce the name wrong but baharia had um their hand up so i don't know if they have a question to ask if they want to type it in the q and a maybe give them a few seconds if they are let me go back and share again are you seeing my desktop you see powerpoint okay good all right um okay no question all right so uh again uh thank you adam uh i i know just from looking at the community uh daily that there are always a lot of questions about scripting when when not where where not things like that so i think that this is going to be um really powerful for a lot of people out there and a great reference tool going forward so a great job adam so again i'll reiterate some of the things that i said uh earlier um make sure that you go to our community page uh and go to the office hours tab uh or button and you'll actually be able to see the existing ones and anything that's coming um in the future and it gives you the ability to go ahead and register for those so make sure that you do that so you don't miss out um what's next so at our next office hours in two weeks we're actually going to have um an office hours that's going to talk about and give an overview of the new out of about out of the box content that will be coming with uh paris as it relates to performance analytics and reporting so uh if you have not uh seen or heard or read or anything of that nature as it relates to this content uh you definitely want to be at this so you can see the new things that are coming uh in paris so again uh we need your help so uh there is an article that also is out in the community that is asking you know what do you all want the office hours to cover these are for you so at the end of the day we want to make sure that we're giving you content that uh is is giving you direction and the ability to do more things uh inside of your instance for your organization so if there's something that um you need and something that you'd like to see us to go over within office hours please go out there and uh let us know if you can't find that then you know you can definitely you know just ask a question or anything inside the community and you can definitely let us know that way so um again to reiterate until next time make sure that you use the community for the questions and make sure that you're asking those questions based on the previous slide that i showed you so we're making sure we're getting all the information that we can from you to answer the question as thoroughly as possible again you can catch up on previous topics of office hours uh by going to that tab they're actually listed in a few places so there's quite a few places that you can actually find the existing ones um if there's an idea if there's something that you would like to see in future releases if it's something that you know is not out there um and you can confirm that it's not out there then uh put an idea in the ida portal and hopefully the uh the developers can get on that and have that hopefully in a future release um we don't necessarily know what you need until you ask so uh please uh you know put as many ideas as you can out there uh training so uh like adam was saying uh before there's uh there's so much training and there's really great content as it relates to training uh not only what's in now learning but also the k20 labs that uh i talked about earlier so you know that you really are in a position to get very familiar with performance analytics uh and reporting and even other things obviously within the platform as well by utilizing the training that is out there so you know with the fact that k20 was free take advantage of that if you've already been doing that then kudos to you if you've not done that yet uh you really are doing yourself a disservice by not going out there and actually looking at uh those those k-20 labs or some of them the ones that really pique your interest and then maybe some of the ones that don't so definitely go out there and take advantage of that as well so um with that um that is all we have uh questions going once twice looks like that's it so thank you again for joining us this week and we'll see you in two weeks thank you