This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

How do you run a custom sql script in a Foglight Rule?

We want to run the following query in a custom rule:

 

SELECT name, is_disabled, is_policy_checked
FROM sys.sql_logins
WHERE is_policy_checked = 0 AND is_disabled = 0
ORDER BY name;  

We want a alarm to fire if the condition is true on any of the instances in Foglight.

 

What is the best way to run a query within a rule?  Is it possible?

  • Hi Randy,

    Please reference my blog post and the one linked within it. This is a good use case for a user-defined collection (UDC). I don't know of a way to run an external SQL query within a rule, but the outcome by following the steps in the blog will be similar.

    I would also suggest that you create 2 user-defined queries.

    1- SELECT count(*)
    FROM sys.sql_logins
    WHERE is_policy_checked = 0 AND is_disabled = 0

    The logic of your rule will be that if the count is > 0, fire the rule. Also note, no ";" or "/" is needed to terminate the query in the UDC.

    2- The data from your original query below will be retrieved by Foglight so that you would have a list of the login names to investigate:

    SELECT name, is_disabled, is_policy_checked
    FROM sys.sql_logins
    WHERE is_policy_checked = 0 AND is_disabled = 0
    ORDER BY name

    My blog to setup a rule from the result set of query #1 above, plus a link inside on setting up a UDC:

    www.quest.com/.../creating-rule-for-user-defined-queries-in-foglight
  • I wanted to use a UDC for something else, but I see the limit is 100. Do you know if this can be changed?
  • Hi Randy,

    There is a setting in the Agent Status Properties called "Maximum Rows to Submit for Custom Collection" that should allow you to change the parameter.

    Knowledgebase article 98065 has more information about this configuration setting.

    John
  • Thanks John, that's what I was looking for. When I go through the process of creating a UDC, I don't get an error when creating it, but after I hit ok, the collection cannot be found. I have checked the agent log, and I don't see an error. Any ideas?
  • Was in the about the collection not being found in the Administration area when the UDC was being created or somewhere else in the system (e.g. rule management).

    1). In Database Agent log file in the header after parameters and our collection settings, is your UDC collection listed properly under "UserDefined" and registered to Foglight?
    2). There is also a "Custom Collection Detailed" in the Agent's properties page that you can review.
    3). When you do get the error, check the FMS log immediately after to see if there is a message with the timestamp from that moment, or even set the FMS to debug and repeat the error.

    Outside of those steps, I would recommend opening a Service Request and an Engineer could help review the issue with you.

    John
  • Thank you all for the suggestions. After working with support, the UDC collection is working. Now that I have the data I need pulled into Foglight, I need to be able to reference the data within an alarm to determine which group should be notified. The data I am pulling in is in the DBSS_Cust_group_info UDC. The UDC contains the instance name, the group who supports it, and a priority level of the instance. I need to be able to reference this information in an alarm that is a copy of the Job Failures alarm. When the alarm is triggered, I need to use the data to set the support group by pulling the data from the UDC. What is the best way to accomplish this? My overall rule topology is DBSS_Agent_Job_List. How do I reference DBSS_Cust_group UDC to compare the instance for which the alarm is firing to the instance in the UDC to get the group information within the rule? Is it possible to access multiple topologies within a single rule?
  • If I understand this right, If I wanted to run this against all 1500+ instances, I would have to create a UDC in each instance?
  • Hi Randy,

    You can create it once on the first instance, and then use the clone button to copy that UDC to additional instances.
  • Ouch, so it would be 1499 clones. Do you know what the performance impact might be each time a UDC is created? Management is thinking of using this type of process for many things and we could end up with tens of thousands of UDCs
  • There would be 2 impacts -

    1- is the impact of the SQL/T-SQL in the UDC itself - the workload it places on the DB. You can specify the collection interval for the UDC to control how often it runs, but obviously "select 1" has a lot less impact than "select from and join multiple tables together"

    2- the impact on the FMS (potential topology churn). Depending on what the UDC does, it could create new object(s) on each execution. I try to keep a UDC to returning a single value if at all possible.