[Music] today i'm going to be doing a how-to on how to create and utilize a database view in servicenow so you might be wondering what type of scenario would you need to do this for and pretty common scenario for database views is where you want to report on a specific type of record but the information relating to that is stored in a separate table the most common example would be relating metrics to incidents in a report so in our hypothetical situation let's say a user tries to report on longest resolve times for incidents and because they're just a user they try to do that against the regular incident table they don't get the results they want they're confused so they reach out to you and because you're the awesome administrator that knows exactly what table you need to hit for that you go ahead and you create a report for them so we'll just call this incidents with longest resolve time and remember we have not created our database view yet i need metric instance that's where that information is stored there we go and we go next we show bars we're going to group by from the metric instance records we're going to group by the incident ids we're going to sum our duration so we've run our basic starter chart from our metric instance table and one thing to notice is that it's pulled in the incidence and the problem so let's try to filter this down a little bit right let's say the definition do field table is incident okay and we'll hit the run so that narrowed it down a little bit can we filter this initial chart further by the fields that are on incident let's say the user wants incidents from specific assignment groups well because this is a document id type field that's pointing at the incidents we're not able to really dot walk back into the incidents and to filter that way so now we're starting to hit the limits of the information that we can extract from just one table and we need to look at doing some type of sql join statement in order to bring multiple records together and get more robust reporting let's go ahead and look in our database views now if you're a real superstar administrator you already know that there is an incident metric database view already created out of the box and of course you would just utilize that for your report but since this is a training exercise i'm just going to delete this and recreate it and then let that serve as our example so let's see here delete and we will create a new one okay so now that we've created our top level view which is kind of like a virtualized database table we're going to bring in the tables that make up the view so the first one will be our metric definition table we need to put a prefix a variable prefix on our view tables so that when we try to reference a database field from a script we'll add the prefix to it so that the system knows exactly which view it's supposed to pull from to get that information because let's say the tables you're pulling together if they have the same name on different tables that becomes confusing for the system so that's what the variable prefix is for so since this is metric definition we'll call that md and the where clause is basically a sql join so where the metric definition underscore table field equals incident so notice i added my prefix inside the where clause so that it will know exactly what field is supposed to pull from the one from the metric definition table and now i'm just going to repeat this process with the metric instance table i will give that a variable prefix i will specify the order because these things have to run in order and my where clause will be metric instance underscore definition equal to metric definition underscore syst id and now we're going to bring in the incident table assign it a variable prefix specify the order specify our join clause metric instance underscore id is equal to incident underscore sys id and now that we have created our database view let's run our report against our database view incidents with longest resolve time source table incident metric and we can see now that we have our incident fields being pulled in since we have them joined through our where clauses i'm going to aggregate the sum on the duration field and we have our more robust filtering now let's see here we could filter by assignment group if we wanted if our incidents have that information available so basically that's a scenario where you want to use a database view is when you want to pull in data from multiple tables into your reporting solutions you can also use a database view in a back-end script just be sure that when you use glide record against your database view you also use your variable prefixes that we talked about before your column names so that the system knows which view table to pull those from and hopefully you found this information useful and thank you for watching