Kaseya Community

Need Help Querying Custom SQL Views

This question is not answered

We have configured a link server in SQL Management Studio and created a view in the ksubscribers database which references the link servers tables. We are able to retrieve the data with a SQL query in Server Management Studio, but when we run a procedure with that same query store in Kaseya's XML file repository we get the error:

FAILED in processing THEN step 1, Get Variable, with error Warning: call did not return any result data (Line 2)

The procedure is just 3 lines:

1) IF True

2) SQLread to variable

3) Send Email with variable to admins email

All Replies
  • I am wondering if anyone one else is doing anything like this and how they got it to work

  • Yep .. do this all the time .. can you post the sqlread line from your xml file so we can check it's syntax .

    and also the full view name you created in Ksubscribers i.e. ksubscribers.dbo.customviewname

  • <?xml version="1.0" encoding="utf-8" ?>

    <queryList>

    <queryDef label="TEST Get Company Name" sql="Select TOP 1 [company_Name] FROM [ksubscribers].[dbo].[v_remote_info]" />

    </queryList>

    We got it to work via stored procedure to a table, but we would rather real time data via a view

  • I've not tried what you're doing but could the problem be a permissions issue?

  • Are you using the SQLRead function?

    Use the GetVariable(() and use the SQL View option and call the view from there.

  • One thing I forgot to add is you need to ensure you have this column in your query:

    Machine_GroupID

    Without this all GetVariable view reads will fail.

    Example you can use to test your "view"

    --------------------------------------------------------------------------------------

    CREATE VIEW vtest AS

    SELECT m.machName+'.'+m.groupName AS Machine_GroupID, u.password AS Passwd from MachNameTab m

    LEFT JOIN users u ON m.agentguid=u.agentguid

    --------------------------------------------------------------------------------------

    From here, modify your procedure to pull the GetVariable as such:

    GetVariable("SQL View Data", "vTest/[Passwd]", "test", "All Operating Systems", "Halt on Fail")

    Then write the procedure log to see what you get:

    writeProcedureLogEntry("#test#", "All Operating Systems", "Halt on Fail")

    Run the procedure and see check your logs.

  • I thought you could just call custom views directly? I use #viewName.columnName# with no getVariable() or sqlRead() steps. Or did this change?

  • Also, flavio is absolutely correct about the agent details needing to be in the view. At procedure run time, the machine ID of the target is assumed to have a record in the table queried. If it doesn't, you'll get the error you are asking about.



    Corrected "agent GUID" to "machine ID"
    [edited by: SMason at 12:12 PM (GMT -7) on Aug 1, 2014]
  • We actually tested this 4 ways... to eliminate issues, using the exact same syntax just replaced the selected field and the view/table

    1) querying the linked database directly, no go (as expected)

    2) querying a custom view pulled from the linked database, empty response

    3) querying a custom view pulled from ksubscribers, OK

    4) quering a custom table populated with the custom views data via stored procedure, OK

    We did not reference the Machine_GroupID in any of them.

    "Are you using the SQLRead function?

    Use the GetVariable(() and use the SQL View option and call the view from there." - are you saying make the SQL query the variable... that is clever, great for testing if that actually works...

    This test was supposed to be a proof of concept to then implement in Info Center to be able to blend data from the other database into reports... which works... but not the way we were hoping... what I really need is to make a custom Report Part and not a Name Value as I didn't fully understand what those were... turned out to be less the I hoped for... but this was to be more at the Org level then the machine level.

  • Agent guid is NOT the predicate in which the query is doing the filtering.

    If try this out and look at the agent procedure log and you will see that the WHERE clause is referencing the full machine ID of the agent.

    Utilizing SQLRead requires you to create an xml file and import this to the KServer directly (help.kaseya.com/.../index.asp)

    GetVariable can reference any view already created and IMO has been easier to reference.

    The SQLRead would be easier if you are making specific "queries" available to other techs to reference in their APs.

    But like I stated before the ONLY requirement is that you ensure you follow the proper predicate GetVariable utilizes.

  • I have the SQLread xml file etc, I posted it at earlier in the thread.  But we can forget about the SQL read I am using this in query in Info Center with Name Value Parts.  I can query a custom view with data native to ksubscribers fine. I am not sure everyone is clear that I am attempting to query another applications database that we have linked to Kaseya's db server. I think the there is some delaying in fetching the data or something resulting in the query against the view coming back as blank from Kaseya... if we make the data in the view into an actual table the query gets results... I am guessing because the data is actually already there and not fetched at execution time.

  • "I am attempting to query another applications database that we have linked to Kaseya's db sever"

    You mean the second database resides within the same SQL Instance as Ksubscribers?

  • No, it is a Linked Server, completely separate SQL Instance.

  • The queries against data pulled into a view created in ksubscribers work in SQL Management Studio, but through Kaseya they come back empty. If I pull the same data into a table in ksubscribers the query works in both Management Studio and Kaseya... which makes me think there is some kind of delay during which Kaseya thinks the view is empty... that is my best guess.

  • My guess is your view does not have the proper execution permissions to execute the query as the "kaseyavsaXXXXXXX" SQL user account.

    Run:

    GRANT SELECT ON [viewname] to [kaseya SQL user]

    Then attempt the report again. If this still fails then you need to check the remote db to ensure cross SQL access to the Kaseya SQL user is configured right. My guess is you're attempting to query from SQL management studio with SA and not experiencing any issues.. May be different when the system runs it as the Kaseya SQL user