• State Not Answered
  • Locked Locked
  • Replies 19 replies
  • Subscribers 59 subscribers
  • Views 13295 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?

  • 0 over 6 years ago
    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?
  • 0 over 6 years ago
    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.
  • 0 over 6 years ago

    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:

  • 0 over 6 years ago

    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.

  • 0 over 6 years ago
    A couple things to change. You'll need to delete the UDC - so have the query copied. Then re-create it, but provide a column name (login_count), type should be double and unit of measurement should be count.
  • 0 over 6 years ago
    Thank you Darren! Finally, a working UDC. Now I am waiting for it to generate an alarm. Are there any good tutorials on how to drag and drop a UDC into a dashboard?
  • 0 over 6 years ago

    They're buried pretty deep in the data model.. I couldn't show it all on one screen, but this will get you down to the collection, and then the metrics (eg. login_count) that you named when you setup the UDC will be draggable to a custom dashboard/report.

     

  • 0 over 6 years ago
    I don't see the data tab on the Actions window. Do I need to set something to display it?