• State Not Answered
  • Locked Locked
  • Replies 19 replies
  • Subscribers 59 subscribers
  • Views 13042 views
  • Users 0 members are here
Options
Related
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?

randy.scott
over 6 years ago

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?

Parents
  • 0 over 6 years ago
    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
Reply
  • 0 over 6 years ago
    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
Children
No Data