DX Infrastructure Management

Expand all | Collapse all

Timed SQL Query

Jump to Best Answer
  • 1.  Timed SQL Query

    Posted 11-26-2019 10:51 AM
    Hi all,

    I'm looking for a way to run a SQL script at a specific time of day.

    Since the jdbc_response probe doesn't have a scheduler I'm struggling for a way to query the UIM DB at midnight, which will total up a value over the last 24 hours.

    I've looked into a Lua script, but it's pretty labour intensive for what I need.  I could also alert on the value and use alarm.get to bring the value into a Lua script and then post it to QoS, but that seems a little over engineered.

    Any thoughts on a more elegant way to achieve this?

    Cheers,

    ------------------------------
    CA - UIM administrator
    ------------------------------


  • 2.  RE: Timed SQL Query
    Best Answer

    Posted 11-26-2019 12:40 PM

    Sam -

    One option would be to create a perl script that makes the connection to your database and executes your SQL query.  Then create a profile in the nexec probe to execute the perl script daily at midnight.  You can either have your perl script write the outputs of the SQL query to a local file, then configure a profile in the logmon probe to read that file and generate QoS from the contents or have the perl script write the outputs of the SQL query to stdout and have the nexec probe post the output of perl script as a Nimbus message with a unique subject.  You would have to create an attach queue in the hub registered to receive messages with that subject, then configure a profile in the logmon probe to read that queue and generate QoS when it processes messages from that queue.

    The other option would be to Edit the jdbc_response probe (highlight the probe in Infrastructure Manager, right-click, then select the Edit... option), and configure a Time Specification Range (you could specify a 23 hour and 50 minute range to start at midnight and end at 11:50pm) then configure your SQL query to run every 24 hours.  The probe should be stopped at 11:50pm daily then restarted at midnight.  When the probe restarts, your profile should run once.  For more information on configuring the Time Specification of a probe, please check the General Probe Management in Infrastructure Manager->Add a New Probe Definition & Time Specification Format techdoc.



    ------------------------------
    Kathy Maguire
    Technical Support Engineer 4
    Broadcom
    ------------------------------



  • 3.  RE: Timed SQL Query

    Posted 11-26-2019 11:57 PM
    Hi Sam

    The sql_response probe has a scheduler option

    https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/it-operations-management/ca-unified-infrastructure-management-probes/GA/alphabetical-probe-articles/sql-response-sql-response-monitoring/sql-response-im-configuration.html


  • 4.  RE: Timed SQL Query

    Posted 11-27-2019 10:11 AM
     Hello Sam indeed I think this option could help you



  • 5.  RE: Timed SQL Query

    Posted 11-27-2019 11:50 AM
    Thank you for all the replies everyone!

    Does the sql_response support MySQL DB's? I can't find a list of supported DB's in the docs.

    Thanks,
    Sam


    ------------------------------
    CA - UIM administrator
    ------------------------------



  • 6.  RE: Timed SQL Query

    Posted 11-27-2019 11:52 AM
    ah, scratch that. Sql_response is for windows systems only.  I'm running RHEL 7...

    ------------------------------
    CA - UIM administrator
    ------------------------------