DX Infrastructure Management

Expand all | Collapse all

How to create a sql checkpoint in the sqlserver probe to get list of db_names on the server the checkpoint is being applied to?

  • 1.  How to create a sql checkpoint in the sqlserver probe to get list of db_names on the server the checkpoint is being applied to?

    Posted 05-24-2018 10:39 AM

    This might be confusing so please do not hesitate to ask questions on what I mean. What I am trying to figure out is how I can get the db_name as a variable so it can be saved in the QOS so it can be used in a dashboard. So what I want to be able to do is if I was using the metrics db_backup_status returns the number of minutes since the last successfull backup. When I go to a dashboard I am able to get the actual server name but I am not able to just get a list of each database on that server and the minutes for each one, even though I know the probe and metric is getting that information.

     

    I have tried to create my own "custom" version with the exact same query as backup_status however I am not able to assign the column db_name from my query to a value in the probe. So if I cant assign it as a value that I cannot collect it and in turn in CABI or the UMP display the list of all db_names on the server and each db backup_Status minutes.

     

    So in the dashboard I want to display all the SQL servers we are monitoring, and than be able to click on a server name and it drills down to a second dashboard that lists all the databases on that server and each of there minutes since last backup.

     

    Anyone done anything like this or know how I can achieve this?



  • 2.  Re: How to create a sql checkpoint in the sqlserver probe to get list of db_names on the server the checkpoint is being applied to?

    Posted 05-24-2018 11:35 AM

    Hello,

    It might help me if you could share your query and then I can test it in my environment.

    I presume you want the source to be the instance and the databases to be the target, which should be achievable with a custom checkpoint

    cheers



  • 3.  Re: How to create a sql checkpoint in the sqlserver probe to get list of db_names on the server the checkpoint is being applied to?

    Posted 05-24-2018 11:36 AM

    Unfortunately QOS information currently is numeric only so you will not be able to put the names of a database into a qos message to be used in an alarm.

     

    I do not know off the top of my head how this might be done.

    Maybe someone else will have an idea how you might accomplish this using something other than a qos message



  • 4.  Re: How to create a sql checkpoint in the sqlserver probe to get list of db_names on the server the checkpoint is being applied to?

    Posted 05-29-2018 08:59 AM

    To help show what I am trying to do visualy on the dashboard here is a screen shot of the dashboard. Below in screenshot 1 this dashboard is Opened by clicking on a server name (in this example its an oracle db server) this is what I am trying to populate to a dashboard like the example below: When a server is choose is displays the Database names on that server in one column the backup info in another. I am able to get as I said the backup info its just displaying the list of DBs that is on the hostname selected.

     

    (In this example the dashboard is oracle I know. We are doing one for each ouf our DB flavors. Not to confuse just wanted to give an example of what im trying to do visually with the SQL probe.)

     

    *ScreenShot1



  • 5.  Re: How to create a sql checkpoint in the sqlserver probe to get list of db_names on the server the checkpoint is being applied to?

    Posted 05-29-2018 02:08 PM

    Robert,

    What I have done in the past which is possibly simpler but I think offers something similar is…

     

    Configure the sqlserver probe on each host and have a connection for each instance. (the connection name shows in the target)

    Then create a profile containing database state, database size and backup_status

    Use a List view to display the data which is dynamic and low maintenance:

    Mine looks like this

     

    The missed backup Status is set to go red when the time since last backup reaches a certain level eg 1500

     

    Would this work for you?

    No nasty sql to write and new instance monitoring will show automatically in the report

    All achievable with OOTB configuration.

    Cheers

     



  • 6.  Re: How to create a sql checkpoint in the sqlserver probe to get list of db_names on the server the checkpoint is being applied to?

    Posted 05-29-2018 02:10 PM

    Also the target value has a drilldown to a PRD chart with chart detail of all the metrics to see the historical data



  • 7.  Re: How to create a sql checkpoint in the sqlserver probe to get list of db_names on the server the checkpoint is being applied to?

    Posted 05-29-2018 02:16 PM

    I believe this might work for me however, am I correct in when I am currently seeing as I cant tie this into a dashboard in the dashboard designer? 



  • 8.  Re: How to create a sql checkpoint in the sqlserver probe to get list of db_names on the server the checkpoint is being applied to?

    Posted 05-30-2018 07:32 AM

    You can setup the same List in Dashboard Designer as well.

    The QOS metrics could be attached to other widgets as well but maintenance of this type of dashboard can be high.



  • 9.  Re: How to create a sql checkpoint in the sqlserver probe to get list of db_names on the server the checkpoint is being applied to?

    Posted 05-30-2018 07:51 AM

    And it seems this is where I have gotten stuck. I have not been able to produce in the dashboard designer a replicated "List" like you showed in the "List Designer". I am looking again right now however it seems the Dashboard Designer creates List Views a bit differently.



  • 10.  Re: How to create a sql checkpoint in the sqlserver probe to get list of db_names on the server the checkpoint is being applied to?

    Posted 05-30-2018 08:12 AM

    Yes you are right, you can't quite recreate the same target/source reversal in the dashboard designer version. Not sure why they omitted that functionality as everything else is there.



  • 11.  Re: How to create a sql checkpoint in the sqlserver probe to get list of db_names on the server the checkpoint is being applied to?

    Posted 05-30-2018 08:43 AM

    Yea not really sure why they did either. The List Designer Format is def exactly what I need because I can see the hostname and the target which shows me the tables on that DB server. Very strange.