Kaseya Community

SQL Query

  • Hi all,

    Any SQL specialists able to assist? I want to write an agent procedure specifically for the false agent offlines which would check if the machine is actually offline or whether it's only the Kaseya agent that stopped responding. If the machine is actually offline then create a ticket but if the agent only stopped responding to send an email and at the same time clear the alarm. I need some assistance with the SQL query to change the alarm status for that alarm?

    Thank you

  • A better thought would be to check if the machine is actually offline, and if not, automatically restart the kaseya agent service from another machine on the network using sc to fix the problem with the agent not responding.

    One problem you're going to run into is that there's no way to create tickets/alarms from agent procedures -- something that I wish was implemented.

    Brain storming...

  • This is something we were looking for also and I do recall Ben (kaseya) mentioned something in a recent webinar about either a script being there for this - in his script I think it searched for other machines on the network and if they were offline also then the internet was down if not then most likely and issue with that machine this was done via SQL in an Agent procedure. Slight tangent I know but just rang a bell with me.

    In answer to your question - "which would check if the machine is actually offline or whether it's only the Kaseya agent that stopped responding"

    In either case Kaseya is going to consider it offline so how would a SQL query help you - surely what you need here is for another machine to do some tests on that machine and see if it is actually offline and then try to stop and start the agent on it.

  • Procedure KAgentSVC Remote-Restart(Prompt).zip

    @Thinus As Dan and mmartin said you don't need an SQL query to check if an agent service stopped or if the computer is offline, you use the Agent State monitoring under alert to trigger an agent procedure on on another server in the same network which attempts a remote agent service restart. I have attached one of my procedures that prompts you for the machine name or IP address, the last time I posted this for one of the guys he had trouble using it and had to modify it a bit but give it a try. If you want to automate it you will need to update the prompt variable computer name/IP line to constant and you will have duplicate and update the procedure for each machine agent you want to monitor this way.

    @Dan Have a look at this thread; community.kaseya.com/.../11303.aspx I have actually managed to get it to work now, still sucks that there is no direct method to do this. As for ticketing it can be done using fake process monitoring and agent procedures or you could look at Service Desk.



    [edited by: HardKnoX at 1:42 PM (GMT -7) on 7-6-2011] update
  • Hi guys,

    Thanx for the comments but I think you misunderstood me. The SQL query is not to determine if an agent is actually online/offline but to actually clear/remove an alarm status after determining whether an agent is actually offline/online. Thank you for the feedback though

  • You'll want to SELECT test this before you do it, but this will set the alarm to a closed state for any alarm message that has the phrase "this is a test" in it

    monitorAlarmStateId: 1=open, 2=closed, 3=deleted

    UPDATE dbo.monitorAlarm SET monitorAlarmStateId = '2' WHERE message like '%this is a test%'

    Keep in mind that many other things could be closely tied to dbo.monitorAlarm that need to be updated as well.  If I were you, I'd ask support exactly what you need to do in order to close/delete an alarm using an SQL query to make sure that every row in every table that needs to be updated gets updated.

    The above UPDATE does change it to show as a CLOSED alarm, but again, I do not know for sure if that's all you need to do in order to keep your database consistent.  Clicking the "Close" or "Open" button in the VSA could do many different things to the database.

     

  • there is also an API for alarms we use it from our customer portal to allow our clients to close alarms. We also built a custom page idea being that we pass the alarm ID to a script which then runs down on our K server this custom page passing it the alarm ID which ultimately closes the alarm. (uses the Get URL command feature)

    However Can't remember (been a while) but i think we had an issue with the alarm ID or did we sorry been ages since I looked at it.

  • all probably not much help for you but sure its good to talk.

  • Thinus, you getting a lot of "False Possitives" on your VSA?

  • Inside an agent procedure you could use EventCreate (http://support.microsoft.com/kb/324145) to create a custom event that you have Kaseya set to monitor for.

     

    -Scott

  • Thanx for this Dan, will check with Support on this as well.

  • Hi Martin,

    Sounds like interesting stuff. Would you share some more of your API info?

    Cheers

    Thinus