Kaseya Community

[Agent Procedures] sqlRead not working

  • When I query my database directly with:

    DECLARE @MachineID as varchar(max) = 'machine1.datacenter1.company1'
    SELECT agentGuid FROM ksubscribers.dbo.vSystemInfoManual
    WHERE Machine_GroupID = @MachineID

    I get the Agent GUID like I ame supposed to.

    When I do the same as de pre-defined SQL statement so I can use it in the sqlRead function:

    <?xml version="1.0" encoding="utf-8" ?>
    <queryList>
    <queryDef label="Select Machine GUID" sql="SELECT agentGuid FROM dbo.vSystemInfoManual WHERE Machine_GroupID = #MachineID#" />
    </queryList>

    I get the error: FAILED in processing THEN step 3, Get Variable, with error Warning: call did not return any result data (Line 3)

    Why is a manual query working, but Kaseya's agent procedure failing on the same SQL query?

    (also created a ticket at Kaseya, but no responce as usual.. Sad )



    Typo's
    [edited by: F.A.N. van den Berg at 3:09 AM (GMT -8) on Mar 3, 2015]
  • Hi ;

    You don't need to create a sqlRead for this and I would not use that particular table for it as there are other tables that would be loads better (e.g.: dbo.vMachine or dbo.vMachineContext)

    All you need to use in your agent procedure to get the Agent GUID is the following database view #vAgentConfiguration.agentGuid#.



    typo
    [edited by: HardKnoX at 1:59 PM (GMT -8) on Mar 3, 2015]
  • I kind of do need to use that table, as we have defined custom agent attributes in that table.

    It depends on what kind of alarm is generated, what kind of alarm is given, en to who.

    I just got news from Kaseya: It seems to be a software defect and will be passed to the R&D team :(

  • I'm not aware of any software defects related to the sqlRead at the moment and if there was an issue I would expect it to be a very big deal as many of the guys in the Kaseya community that do advanced Kaseya Agent procedures use it.  

    Okay so you want to run an agent procedure on computer A to get computer B's Agent GUID based on machine B's Machine Group ID?

    I think I can make a query that will work but I need to understand a few things if you want help with this.

    The vSystemInfoManual table is used to store Custom Fields, which I think you know and it has the following  4 fields;

    - [Machine_GroupID]

    - [agentGuid]

    - [fieldName]

    - [fieldValue]

    From your query it appears that you want the [agentGuid] where the [Machine_GroupID] matches the supplied Machine Group ID, but it sounds like you want a value for a specific custom field where the Machine Group ID matches the value you are supplying rather than the agent GUID.

    Something you have to keep in mind with the vSystemInfoManual table is that it only stores the agent GUID and Machine Group ID if you fill out a custom field value for that agent. So if you had an agent that did not have any custom field values your SQL query would not return any data.

    To fix this you can make your query return a default value for in case data you are looking for does not exist. Ideally you would want to pick a value for the default value that matches the type of data (numeric or string) and at the same time that can be used in an checkVar IF statement to tell your procedure that the Custom Field you where looking for did not exist.

    Here is an example query that will give you the agent GUID if the Machine_GroupID and fieldName values matches the #MachineID# and #CFName# procedure variables;

    SELECT ISNULL((SELECT agentGuid FROM vSystemInfoManual WHERE Machine_GroupID = '#MachineID#' AND fieldName = '#CFName#'),'#DefaultValue#')

    Note that the value for the #DefaultValue# variable for this query needs to be a numeric number like 0 for the query to work.  

    Here is an example query that will give you the custom field value if the Machine_GroupID and fieldName values matches the #MachineID# and #CFName# procedure variables;

    SELECT ISNULL((SELECT fieldValue FROM vSystemInfoManual WHERE Machine_GroupID = '#MachineID#' AND fieldName = '#CFName#'),'#DefaultValue#')

    Hope that this helps you.