Kaseya Community

Database view variables in Service desk procedures

  • Can someone tell me if database view variables work in service desk procedures, and if so how to use them?

  • Yes but it is tricky.

    Here is an example of one that I have created.

     

    I wanted to be able to grab more information from the Organization Staff members table. Specifically what is entered in the Staff Function field.

     

    First create a SQL Query and save it as an XML in this specific location on the Kaseya server.

    Kaseya\xml\SDProcSQL\0\SQLQuery

     

     

     

    Here is the details of the Query : “staff_function.xml “

     

     

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

     

    <queryList>

     

       <queryDef label="staff_function" sql="SELECT StaffFunction FROM vSystemOrgStaff WHERE staffName = '[=who=]' " />

     

    </queryList>

     

     

     

    In the screenshot of the Sub-procedure I get the Variable of “who” which is the Staff name in line 1

    In line 2 I run my SQL Query with is using the variable “who” and it then returns the results into the variable “ID”

    In line 3 to test I just output the details of “ID” as a note

     

     

  • The other thing to be careful with is ensuring your SQL query used in the xml file will return only 1 single row.

    So I also was add "Select top 1"  to my queries

    e.g. as in Shawns example I'd change the select to read

    SELECT top 1 StaffFunction FROM vSystemOrgStaff WHERE staffName = '[=who=]'

    This way even if there are more than 1 rows that match staffname = '[=who=]' you will only get 1 returned.

    Paul