Exported Report: When Was Database Maintenance Last Performed?

SolarWinds® Orion includes self-management features to perform database cleaning, re-indexing, and data summarisation. This function is scheduled by default to run at 2:15 each night and will take an amount of time relevant for the volume of data your platform is generating. If it starts taking longer than normal (your normal could be 24 minutes or 2 hours 24 minutes. Baselining is important; you need to know this.

We were recently asked by a customer if it was possible to generate a report to look up when the database maintenance ran and completed as this can be useful for identifying if there are any issues affecting the health and performance of the platform. Once the report was created they then wanted to automate that report to run at the start of each shift in the morning so that they could see if there were any issues the night before.

To generate this report, you are going to need to first go to manage reports and click create new reports. You will then need to select create a custom table and for the data source, you will need to select Advanced Database Query and select the SQL radial button.

You can copy and paste the below query however if you want to increase the scope of your data you will need to change this reference -24, GETDATE()) from -24 to however many hours in the past you want to look at e.g. last -120 for the last 5 days as opposed to the last 24 hours.

[code]SELECT [message],
[eventtime]
FROM [dbo].[events]
WHERE [message] LIKE ‘%Nightly Maintenance%’
AND [eventtime] >= Dateadd(hh, -24, Getdate())[/code]

Click the Preview Results button and highlighted in red confirm the timing values for when the archive started and then completed with their corresponding timestamps, here you can see that it took 1 minute and 47 seconds to complete. Now that we have confirmed that the data is right lets click Add to Layout.

Now that the query has been added to layout it will then automatically take you to the table customisation section, this is where we are going to need to select our content and format so that the data makes sense. When we wrote our query and clicked preview, Orion gave us something which works well, so we are going to want to re-create that.

To do so you need to click Add Column and we want to select both EventTime and Message and organise them so that the Message is first followed by the EventTime.

If you select the down arrow with the Advanced title under the column heading, you can change some properties of the column such as changing the display name from EventTime to just Time.

After clicking Submit you can now see that you are brought back to the layout builder screen, however, we now have an entry for the custom table with the data source populated which is the query previously input. You will need to give this report a title before moving on. Continue through the wizard to the properties section.

By default, no schedule exists, so click Schedule this Report to run regularly and click Add New Schedule if you haven’t already created a schedule previously. You now need to click add frequency and you can then begin to customise the frequency of this report.

We want ours to fire every morning during the working week (Mon – Fri). To do this you will need to click Daily in the frequency drop-down and then select the Business Day radial button followed by your time you want it to fire, you can set whatever frequency you wish, once you are happy to click add frequency.

Now that our frequency has been added we need to assign an action. At the bottom of the page, you’ll see a button for Add Action, you can select either email, print or save to disk. Select the one you want to work with and complete all the required fields on the selected action.

Click Next and you’ll be at the summary page where you can review the report and its properties when you are happy with what has been created click Submit and your report will be saved.

You now have visibility of how long the database maintenance task is taking to run and if this is taking too long that a review of the log file is needed to confirm if there are health issues with the Orion installation. This blog has been about creating a report to give you this insight; how about creating this as an Alert? Let us know if you would like to see how this can be achieved and we will do so in a future blog!