Creating a script to query an external database

Hello,

We have an enviroment with Sitescope integrated with APM. I need to automate an SQL query which will check if the events in an external database are the most recent (dateTime) or if there is a connectivity issue.

I have done shell scripting in the past with OMU's schedule monitor/template to run custom scripts. But I'm new with Sitescope & Windows environment.

How should I go about addressing this?

Parents
  • Hi KG-HP,

    you can use the Database Query monitor to query the external database and create an alerting condition depending on your needs.

    Greetings
    Siggi

  • Thanks Siggi,

    I will try creating a database query monitor and update the forum.

    Cheers,

    KG

  • Hi Siggi,

    I created a SQL query with a WITH clause querying the database table for all time entries for CIs older than 60 mins compared to present dateTime. Will a DB query monitor suffice to give me a list of servers whose time difference is > 60 mins?

    Would be great if I could get a direction in which to create this monitor to get the list of defaulting servers.

     

    Regards,

    KG

  • Hi KG,

    you can easily test your query via SiteScope -> Tools -> Database Tools -> Database Connection Tool
    and proceed from there.

    This
    > querying the database table for all time entries for CIs older than 60 mins compared to present dateTime
    might easily work, however I don't know if the WITH clause or temporary entities can be used there,
    simply give it a try.

    Greetings
    Siggi

  • Thanks Siggi. I tried the query in the db tool and it worked with the WITH clause as well.

    Now, the output of the query is atleast >1 row. While setting up an alert, it limits the text characters in the email. I tried all the email templates in sitescope to see if any provides the full output of the query. But I get '. . .' after 14 entries.
    Is there a way to get the full output of the query as an alert?

    Thanks!

    KG 

  • Hi KG,

    thanks for the update and the good news.

    Regarding your follow-up question,
    can you please send me an example email so that I can see what exactly is written, and where it gets cut?

    Thanks
    Siggi

  • Hey Siggi,

     

    Below is a sample email alert for the query output. Notice the '. . .' in the result, I need to get the full output in the alert.

    -------------------------------------------

    This alert is from SiteScope at http://********/SiteScope

    Monitor: 1224586907:DB CPU check
    Tags:
    Group: BMC DB Test
    Status: 94.069 sec, 15 rows, a02353, 1436059440, 2015-07-04 20:24:00.0, 2017-01-17 10:43:31.787, 810139, CPUCpuUtil, 2.41215
    a03750, 1464792840, 2016-06-01 09:54:00.0, 2017-01-17 10:43:31.787, 331249, CPUCpuUtil, 10.6915
    a03761, ...
    Sample #: 23

    Time: 10:45 AM 17/01/17

    For documentation about alerts, go to:
    http://*********/SiteScope/sisdocs/doc_lib/index.htm?single=false&context=system_avail&topic=config_sis_alert

    Query Results:

    a02353, 1436059440, 2015-07-04 20:24:00.0, 2017-01-17 10:43:31.787, 810139, CPUCpuUtil, 2.41215
    a03750, 1464792840, 2016-06-01 09:54:00.0, 2017-01-17 10:43:31.787, 331249, CPUCpuUtil, 10.6915
    a03761, ...

    ------------------------------------------------------------------

    Cheers!

    KG

     

  • Hi KG,

    thanks for providing the sample output, this helped a lot!

    The only property which might be able to influence this behaviour is
    SiteScope -> Preferences -> Infrastructure Preferences
     Monitor Settings
      DB maximum value length (default : 200)

    This setting is used to limit the output as shown:
    ..
     if (resultBuffer.length() > DB_MAX_SUMMARY) {
        resultBuffer.setLength(DB_MAX_SUMMARY);
        resultBuffer.append("...");
    ..

    Simply increase the setting to for example 400 and give it a try.

    Greetings
    Siggi

  • Thanks Siggi.

    I did change the DB max value length to 600, but do not see any change in the email alert.

    -------------------------------------------------------
    Monitor: SiteScope\BMC DB Check\CPU check
    Link to the Monitor: http://******/SiteScope/servlet/Main?activeid=1224587090&activerighttop=dashboard&view=new&dashboard_view=Details&dashboard_model=true
    Status: 18.143 sec, 15 rows, a02353, 2015-07-04 20:24:00.0
    a03750, 2016-06-01 09:54:00.0
    a03761, 2016-06-01 09:29:00.0
    sv63243, 2017-01-09 14:39:00.0
    sv63244, 2017-01-09 14:39:00.0
    sv63245, 2017-01-09 14:39:00.0
    sv63531, 2016-04-...
    Last Monitor Run: 5:59 AM 19/01/17

    Thanks,

    KG

  • Hi KG,

    did you restart SiteScope after the change of the parameter?

    Greetings
    Siggi

  • Verified Answer

    Hey Siggi. The solution worked like a charm after the restart. Thank you for your help on this issue:)

    Cheers!

    KG

Reply Children