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
  • In reply to Darren Mallette:

    I wanted to use a UDC for something else, but I see the limit is 100. Do you know if this can be changed?
  • In reply to randy.scott:

    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
  • In reply to John.Sakalauskas:

    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?
  • In reply to randy.scott:

    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
  • In reply to John.Sakalauskas:

    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?
  • In reply to Darren Mallette:

    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?
  • In reply to randy.scott:

    Hi Randy,

    You can create it once on the first instance, and then use the clone button to copy that UDC to additional instances.
  • In reply to Darren Mallette:

    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
  • In reply to randy.scott:

    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.
  • In reply to Darren Mallette:

    I am setting up a simple UDC in one instance based on the query:
    SELECT count(*)
    FROM sys.sql_logins
    WHERE is_policy_checked = 0 AND is_disabled = 0

    I followed your blog on setting up an alarm based on the output. My collection times are set for 60 minutes for testing purposes and the alarm has not triggered. When I look at the UDC results, there are 20 records and my check is #Login_Count# > 0. Any suggestions on how to troubleshoot why this might not be firing?
  • Hi,

    In the rule editor, severity level, condition tab, drop down the "Run Condition Query" box and paste in #Login_Count# and execute. It should return a number.

    Then try it with #Login_Count# > 0 and it should return true/false. That will help test the data/logic used in the rule.
  • In reply to Darren Mallette:

    with just #Login_Count#  I get an error

    com.quest.nitro.service.sl.interfaces.scripting.ScriptingException: com.quest.nitro.service.sl.interfaces.scripting.ScriptUndefinedObservationException: undefined observation (present but empty observed data for topology object 832d37ef-6607-48d5-943f-4bcdbcdc694f) in query: Login_Count ----script start------ #Login_Count# ---- script end ------

    with #Login_Count# > 0 I get

    com.quest.nitro.service.scripting.errors.noisy.OptionalDataMissingScriptServerException: Login_Count (script: 95030b7713a5ad5288cd6278e7283971)

    But I have a count as shown below:

  • Hi,

    Can you check the collection properties and post a screenshot?

    From the error message, it looks like it's being stored as an observation, and that "Is Key" was set to true.

  • In reply to Darren Mallette:

    It is set to False