All right, today we're going to talk about getting creative with reports and metrics. So in my opinion anyway, DPA does a really good job of delivering what most DBAs would want and need for them to kind of focus on. But there's always this kind of, you know, I'm really comfortable with some SQL scripts that I use to show me a specific piece of data. I always love to have it, and well, it's just not included in DPA out of the box, right. So that is the reason that we made DPA more extensible to handle just this kind of request from users.
So real quick before we get started, a little housekeeping. I'm going to try to monitor both chat and Q & A, so go ahead and put any thoughts or questions or maybe if you just want to say hi. I will get to it. It may not be real time if I'm kind of doing the demo and the walkthrough and stuff. But I promise to get back to it at the end of the presentation if I don't get to it during. Okay, so let's go ahead and start with reports. There are a couple of ways in DPA to leverage the reporting to make it more flexible than it may seem on the surface. So let's go ahead and hop in and check those out. By the way, these are really just things that we've discovered on our side just by playing around some with DPA. And I know for a fact that others have come up with some quite creative reports and metrics out there in the wild. So that is just to say that this is really just limited to what we've discovered. But I know that it's being used in some pretty great ways.
Okay, so let's jump down here. I'm just in an instance of DPA here on our side, one of our QA instances here. I'm going to hop into this instance. And there's a couple way to get to reports directly, so I could've just done it from the main menu choice up here at the top, or I can go into an instance, and then when I go into reports, that instance is already selected, right. So here I'm going to go into report types and grab top SQL, go to report options. Now if people are not familiar with reports, once you come into report options, you get a couple of choices. So I can go ahead and run this report directly like right now.
So let's go ahead and display report. And that will give me the top SQL statements with all the history that we've collected for this instance. So it goes back quite a ways in time. If I want to click on show full SQL text, it'll go ahead and grab those SQL statements and show those below. And it kind of makes the chart a little bit busy, but there's a couple other things we can do from this point. With this report, I can go ahead and save it so it's defined within my environment. I can do a save as if I want to change the title of the report or the name of the report. I can edit it if it's not quite what I want, you know, maybe 50 is a little bit busy, so I can reduce that number to 25 or whatever you guys want. Or I can email it. So if I don't want to go ahead and save it and maybe schedule it to be emailed at specific intervals, I can go ahead and just email it real-time if I'm looking for a specific piece of data or trying to extract some things out of the repository that would be of interest to people, maybe a developer. So that would be the way to do that. So I'm going to click back into edit though and change this up a little bit. And this is where we start to get a little bit creative.
So instead of letting us choose the top X number of SQL statements, maybe I want to define my own, right, so I can click add, and then in the search statement here, I'm going to type something that I'm going to look for, this string, this text string within the SQL statement itself. So if I click search, and then it'll give me some choices to search in. Maybe I've renamed the actual hash value to dpa_niners. In this case, I'm going to look in the SQL text itself. Go ahead and click search. And it shows me that I've got 23 SQL statements that were found containing this string. So if I want to run a report on those, just highlight all those guys. Click OK. That puts them in my report definition. Click OK again. And now if I display the report, it's going to show me all the activity of those SQL statements over the entire history.
Now if I want to, again I can say, you know... Sorry, that was in the detail data range. But I can do all available data. I could just really, you know, maybe in the last seven days. So really I'm just concerned about the last seven days. Maybe I just want to capture the weekdays. I don't care too much about the weekends. And maybe my prime business hours are between let's say seven and six, something like that. Again, you can give the report a name. And let's do DPA_Niners. We'll just give that the title as well. And then if I want to put a description in there I can, but I don't necessarily have to. So here I can display the report again, and it gives me just the most recent work week, and just from 7 a.m. to 6 p.m. Again, if I want to show the SQL statements, I can do that or I can kind of hide that a little bit. But this is a very easy way to extract all the SQL statements that might have something in common that is discoverable.
So the reason I bring this up is that it's a great way to run reports on stored procedures or if you're on the Oracle side maybe packages or functions or procedures, right. So if I know that... Because the stored procedure, when you start to get into that logical structure of SQL and bringing that transactional part of it or the PL part of it on the Oracle side, the procedural language, really what you can have is a procedure that can run functions within it, can run maybe hundreds of SQL statements, individual SQL statements with some logic built around it. So what DPA does is it really goes down to the very granular level of tracking individual SQL statements, but if you want to get that more aggregate visibility into your stored procedures, go ahead and type in the name of it, quick search in the SQL text, and it's going to pull all the SQL statements for that specific stored procedure. So this is something that we've found quite helpful with customers out there.
Okay. So let's go ahead and edit this one. Hopefully that made sense to everyone. So instead of... Let me go ahead and remove all here. Instead of the search string dpa_miners, I would put the name of the stored proc, and then I could get all the SQL statements related to that stored proc or running from that stored proc.
Another kind of cool thing to do here would be, if I can type, to look for SQLAgent jobs. So if you want to know all the SQL that is running from SQL agents and within a particular instance, and you want to kind of get an aggregate view of the overall impact of those agent jobs, go ahead and define those. Maybe in this case I'm going to go the detailed data range, which is going to be the last 30 days. I'll keep my hour range consistent. I'll keep my days of the week consistent. And let's go ahead and display that report. And we get our SQL agent job SQL. So in this case you can see the SQL agents are very minimal impact on my instance overall. May not be the case in your specific environments. Again, if I want to show the full SQL text, I can see what's running from those SQL agent jobs.
All right, so that is a couple of ways that we can extend the functionality of reports within DPA. For me personally, we used to have a way of documenting SQL statements that we contributed, because I used to be on the development side as well. So one of those things was to go ahead and make sure the documentation on any SQL that we wrote or procedures or anything, that we tagged it with comments, right, we did documentation that was easy to figure out who did what within that code. So if that's the case or if you guys employ a similar naming convention or strategy around documenting contributions to development, then one of the things you can do is just search for that person's name as well. So I don't have anything specific running in here, so I don't think this is going to pull anything back. Yeah. But if I did and I had this tagged, like my name in a comment, then it would certainly pull that SQL back. By the way, pretty much all the activity in here is driven by like HammerDB, so it's all generic.
Yeah, so that's another way I've found that to be pretty helpful. We have a couple of these reports defined out there on THWACK. If anybody out there does not know what THWACK is, this is our community site for SolarWinds. Within here we have different segmentations of the different products. So if you click here on product forums I can do into Database Performance Analyzer. And then it's all stuff pertinent to or specific to Database Performance Analyzer. And here if I go into content, I can see stuff that either has been developed internally here and shared with the community or even contributed by the community back to DPA. So the reports that I was running are right here under SQL Server Custom Reports. And if I go to Documents where it kind of filters it a little bit instead of just the discussions in THWACK, kind of forum kind of discussions, here are the two that we just kind of ran through.
So I'm going to click on the stored proc. So I posted this out here in an attempt to just kind of give you guys a one-two-three kind of steps walk-through of how to generate this report. And again, hopefully this makes sense on your guys' side. I put some screenshots in there hopefully to make it a little more usable. Okay. So we can also do reports directly as queries against the repository. So if you're in DPA and you go to reports and we look at the different types of reports that we provide, sometimes that is not a catch-all, right. Sometimes there are reports that do not fit into some of our out-of-the-box report definitions here included with DPA. If that is the case, it might end up being something, like I mentioned, a custom query against the repository. And if it is, there are a lot of ideas out here as well that can help get you either what you need or at least get you close.
So if I look at something like Top Plan Advice, let's look at that one. So what it is is a description about what this report is. And then we give you the SQL to run specifically against the repository. So if you do this, we've got some variable substitutions. So you've got some parameterization of this. But this should give you kind of what the, hopefully [laughs], what the title indicates. And by the way, I would definitely invite everyone if you have any content out there or some cool stuff that you've done within DPA to contribute back to the community. That would be fantastic. So I definitely invite you all to do that.
Let's see. One other thing to mention here... Just trying to make sure I hit everything that I want to talk about. One other interesting thing here is there's some cool stuff happening around reporting and with the integration of DPA to the Orion Platform with this next release. So go ahead and stay tuned on that. I won't go into too much detail about that. If you want, we can go into the overview. And if look at the road map stuff, I think it was talked about. Yeah, so this one here, and that verbiage may not mean that much to a lot of you, but it is really, really cool. I've got a chance to see it in beta also while it was kind of in development and stuff. It's going to provide some really interesting looks and an ability to customize, you know, bringing what you want to see into a dashboard kind of idea. So definitely stay tuned on that with this next release.
Okay, so I mentioned in the intro we may have some SQL scripts and stuff that we just feel comfortable with, that we simply don't want to leave behind when using DPA. Maybe before we had monitoring products or a way to kind of have something else programmatically do it we had to kind of roll our own. So with custom metrics, we can bake those right into DPA for all the leverage. And the idea behind this is collect it once and then it can be used by all kind of idea. So in order to show that, let's go into... Let's see, I don't think I needed to come into here necessarily. Let's go into options and then custom metrics. And you can see that we have a couple of them defined already, which is cool. But I'm going to go ahead and create a new one.
So when I click on Create New Custom Metric, it gives me some fields that I want to select or choose from a drop-down or fill in on my own. In this case, I'm going to pick on Oracle in this one. We can go all versions because it's a very generic script. And I'm going to go ahead and title it with Oracle Temp DB Usage. Maybe I want to put it in a category of maybe disk because I don't see one here I don't think labeled table space or temp or something like that. Or I could create my own, which is going to create a new category for me and put this new metric within it. So that's kind of cool. I know because I looked at the script that it's going to come to me in megabytes, so maybe like that. Don't want to do it as a percentage. But if it was some kind of a ratio calculation and you do want to plot it as a percentage, you can certainly do that. You know, some people just multiply by 100 and don't worry about charting it as percent.
We can set some specific alarm thresholds. So if you want to define some kind of number that if it goes above it, yes flag me because I know that that's too much space being used within the temp DB, that's fine. And I should... Temp DB, gosh. I'll just call it temp usage. I'm getting platforms mixed up here. So really it's a temp table space, but yeah, so if I want to put a warning, maybe if it goes above 500 megabytes then I want to know about it. Or not, whatever, you guys can set your own thresholds, whatever makes sense in your environments. Single value. I can define... It can be a delta, it can be a rate change. This just depends on the kind of metric that you're going to kind of set up. So a delta would be like take a snapshot of the value now and compare it against the last interval, the last time that that metric was collected, and give me the diff, right. A rate would be a change in rate, so it's kind of rise over run, if you will. So it'll take basically the ratio and do the difference between the two ratios. So this is kind of going back to geometry. But if you guys know plotting on a graph with any kind of equation, then you do the rise over run and then you can calculate that rate of change. Timed query [laughs], I'd have to go back and look at what that one is. Milliseconds... Okay, there we go.
So you give it a query to run against the monitored instance, and it will track the end to end execution of that query and tell you how long it took to execute, to run. That one's kind of cool. Right now we'll keep it simple. We'll just do a single value. I'll do it a frequency of every 60 seconds, sure. And the query that I'm going to choose is actually out there on content as well. This one's under Oracle custom metric content. And we're going to go ahead and grab the SQL statement used here. Now for just example purposes here, I'm going to keep it at 60 seconds. But honestly, the space usage should not change that drastically. You know, if you have kind of a runaway query it might, but this one we advise kind of backing off a little bit and running it every 10 minutes. Time of value 50 seconds. So usually when we set up this content out here on THWACK, we try to give you a good idea of how to name it appropriately, right, and what to run it against, display name, whatever. But please just take this as, you know, a starting point or maybe a recommendation to get you pointed in the right direction. So you can call it Sally if you want. We don't care.
So let's go back to our custom resource metric and plug in the SQL statement. So we've got that. I've got the ability to run it down here or test it against some instances. And I don't know, I'm just going to choose one and say OK. So it pulled back a value of 303MB, and it also gives me an indicator like, hey for this particular instance, this metric has not exceeded any threshold that you have set. So it comes back as green. So I say OK, and then I can save it. Uh oh, ran out of disk space. Well if I was a DBA I'd go check the disk space on my repository for sure. Looks like I can't actually save this one against this repository and run it against my monitored instances. But if I could, then that goes ahead and gets included for all of my Oracle instances. So in this case I would go into, let's see, I have a live one, yeah. This guy, uh oh. Should've checked out my environment first. That must have just occurred because I clicked in before this. Apologies for that, everyone. Looks like I'm not going to be able to go into resources and show you, but suffice it to say if you guys are familiar with the resources tab within DPA, you can go ahead and click into an instance, click over to that resources tab. And within there you will see the different categories of our resource metrics that include some of the basic ones like CPU, memory, disk, sessions, things like that. So in this case there would've been a new one called temp TBS. And then I could've clicked on that tab. I would've seen this new metric that starts to plot over time, once every minute for the way I set it up. And it'll go ahead and start to plot for every Oracle instance that we are monitoring.
So again, a really cool way to bring those custom metrics or those custom SQL scripts that you guys felt comfortable with previously or anything that you kind of rolled your own and bring into to DPA. All right, so let's go back into THWACK and I just want to show you guys that under content we have lots of other content that you guys can jump into. We've got it kind of segmented by the different vendor platforms, so DB2, MySQL, Oracle, SQL Server, Sybase. Pretty much all of them have custom alerts, which I think we talked about on a prior segment or posting of Beyond the Basics. We have custom metrics. We have custom reports. So feel free to leverage any of these that you guys would like to. Hopefully this gives you guys... Oh and contribute back of course, as I mentioned earlier. But hopefully this gives you guys a little better idea of some flexibility that we have within our reporting structure to leverage the reports that we have, the ability to leverage any kind of custom reports that really are just queried against the repository itself, and then also define your own custom metrics. We do come out of the box with a lot of metrics that DBAs would want to see or would be of interest to them. But that's not to say that that's all-encompassing, right. That that is complete fulfillment on your side. So we do give the ability to you to extend the capabilities of DPA to leverage anything that is not included out of the box.
All right, so at this point I'm going to hop out of sharing. Well no let me stay in sharing. Let me bring up chat. Are there any questions? I don't see anything in chat. Let me check Q & A. Okay. So let's see. Had some questions about joining, a lot of questions about audio. Seems to always be the case. And yes, I believe there will be a link to the... Actually, here, if you guys want to take a screenshot of this or something or just jot this down, it's go.solarwinds.com/dpabeyondthebasics. So, and I can put this in the Q & A section as well. If anybody wants to come here, it's a posting of all of the episodes so far. And that might be kind of a good place, I don't know if anybody's missed some of the prior episodes and stuff and want to go back and learn a little bit, do a little deeper dive in any of these topics, you can certainly do that. But it will also be sent out in an email.
Okay, so we have another question here. How can I know if a specific user ran a query and whether that is running slow or not? Okay. Perfect, let me see. Okay. So with a specific query, and this goes a little bit outside of the scope of what we were talking about, but no worries. I might have to go into a different environment if this is not going to, well, looks like it might be working again. So if I want to know about a specific query, I have to drill into, or I would want to drill into a specific timeframe. Hopefully everybody can still see my screen here. I believe you can. I'm just going to drill into a day where I can get a list of SQL statements that ran for that day. If I want to go into the specific SQL statement itself and kind of set my context, one thing I will draw your attention to is at the very top here we've got these breadcrumbs. So I know that I'm in an instance for a specific day, and I set it for the entire day, so I want to see all activity for that entire day. I click on a specific SQL statement, and now I get not only just all the data about that SQL statement like the statistics, the execution, physical reads, rows processed, all that good stuff, but I can click into DB users as well and find out exactly who was running that guy, right. And if there were multiple users running this SQL statement, then I can see it right here.
If I want to drill down further into that, I can also click on that user. Let's say there were multiple. And then I get which database is it, hit, I can see what weights, what programs, right, so this is the application side, what machines it was running from, so if people were running from individual work stations or was it coming from the app server, all that kind of stuff. Now I also have the ability to flip around in time, right, so right now I'm in Saturday. But if I want to go back in time, I can certainly do that and check last Wednesday as well. So I can flip that with keeping the context the same up above. So in other words, I'm still looking specifically at this server on this day at this SQL statement. So it's a great way to kind of move around and navigate within DPA without losing your focus.
Okay. What if I want to search on a sprock or code comment but at the time I'm designing the report there's no query with that sprock or comment running? So the only way to really use the search is if you know a specific text string or string that's going to be used within that SQL statement or sprock that would be distinctive, right. So if you can't identify something that would be distinctive from a sprock or from a SQL statement that you're trying to find, then it is going to be a little bit problematic from the reporting aspect of it. Now if you want, there's all kinds of extensible stuff that you can do just by running a query against the repository itself. If anybody wants to understand the ERD a little bit better so that you can kind of fine tune some report SQL statements or report SQL queries or whatever, feel free to reach out to support. The reason I say that is that the ERD is not widely shared. We will certainly share things with current customers, but we don't want to post that on THWACK or make that publicly available for obvious reasons. So hopefully that answers that question. We've got how to watch the other episodes, which is awesome. Okay. I'm on version 11.0.378. You're on version 387. Worth the trouble to upgrade or wait for a next major release? I would say wait for the next release if you're not running into a specific bug that we had or undocumented feature request. There was one thing that got patched in version 387, which is why it's slightly different than the one you had. But if you are not running into that, I would say don't worry about it. Wait for the next major release, which is coming out fairly soon, fingers crossed.
Okay. I think that's all we had in the Q & A. Of course definitely feel free to reach out to us with anything that pops into your mind after the fact, and we'll do our best to get back to you and help you out. All right, thanks everyone for attending, and have a wonderful rest of your day.