Kaseya Community

Usable variables in KSD

This question is answered

Is there a published list of which KSD variables can be used in SQL Queries executed through the KSD procedure step executeSqlQuery() ?

I am having a real hit and miss time with them. Mostly miss. And the KSD documentation does not seem to be entirely accurate.

Verified Answer
  • You can use ANY of the variables whether they are a Kaseya variable or one you create as a SD step yourself

    I guess the one you use obviously needs to make sense in terms of the SQL query .. but as long as it does you can pass any of them off to the SQL query

    This link is to the help page that lists all the variables

    help.kaseya.com/.../9030000

    And this one is to another set of variables also available to use

    help.kaseya.com/.../9030000

All Replies
  • Anyone?

  • Hi  

    I do not believe there is a published list for this -- unless I am misunderstanding the request.

    Can you provide us an example variable you are using in the SQLExecute step?

    Or perhaps show us what is working versus not working?

  • Hi Nicolas, thanks for replying,

    I am currently working on this with Allen (ticket #157269).

  • You can use ANY of the variables whether they are a Kaseya variable or one you create as a SD step yourself

    I guess the one you use obviously needs to make sense in terms of the SQL query .. but as long as it does you can pass any of them off to the SQL query

    This link is to the help page that lists all the variables

    help.kaseya.com/.../9030000

    And this one is to another set of variables also available to use

    help.kaseya.com/.../9030000

  • JB1975

    Hi Nicolas, thanks for replying,

    I am currently working on this with Allen (ticket #157269).

    Allen updated the ticket with her recent tests which seem to work, if it still continues to have problems, please let her know.

    If you need any assistance from my side of things -- feel free to send me a PM.

  • Hi all, sorry for the delay in replying.

    We are still not having any luck with this one. An example of what we are trying to do is to use a SQL query that can get the ticket number of the ticket by using the [$TicketId$] variable. This would be very useful for us to use in a range of SQL queries and KSD procedures.

    We have followed the docuementation and used the information posted earlier by Paul, but have not had any luck. Allen has helped and has also struggled, but was able to make this work on a test server using the below query:-

    SELECT ref from kasadmin.sdincident where ref = '[$TicketId$]'

    We have tried and cannot get this query to produce any output for us. We are using it in a basic 2 step procedure as proof of concept - the first step runs the query and writes the result to a variable, the second step adds a note to the ticket and writes the contents of the variable to the note.

    We have tried the following variations when referencing the table:

    kasadmin.SDIncident

    [kasadmin].[sdincident]

    [kasadmin].[SDIncident]

    ksubscribers.kasadmin.sdincident

    [ksubscribers].[kasadmin].[sdincident]

    [ksubscribers].[kasadmin].[SDIncident]

  • Hi JB1975,

    I am coming in a bit late on this, so my apologies if I am not following everything.

    What stage is this procedure being used?  

    Are you trying to dedup the tickets.  If so Is the isDuplicateRequest not handling the duplication correct?

    Feel free to pass me the procedure and sql, and I will look into getting what you need.

    Or you can give me a simplified explanation of what you are trying to accomplish.  Like I would like to dedup tickets that are xxx...

    Thanks

    - Charles

  • Be careful with any SQL query that could return more than 1 row

    I always use Top 1 in my queries

    e.g.

    SELECT TOP 1 ref from kasadmin.sdincident where ref = '[$TicketId$]'

    2nd is why are you trying to query a value you already know

    e.g. Ref will be the same value as [$TicketId$]  .. so you already know the Ref value

    3. Watch for Case . Sometime the Kaseya Examples have the wrong Case ... e.g. there example for ticket ref used to be [$TicketID$] .. note the Capital D which was wrong

    4. I have also found in the past that sometimes the Ticket doesn't know the value of a variable during ticket creation. If I recall correctly the MachineID isn't available until AFTER the ticket has been created. So sometimes trying to get a variable value will fail as the Ticket has not yet assigned the value.

    5. Can you psot an export of your actual SD Procedure so we can look at the actual XML

  • Hi , we were using the procedure in a ticket entry stage. We have since tried using it in a ticket change stage and it works successfully. We needed it to run as soon as possible following ticket creation, so I guess we could add some tests to the entry stage to look for certain conditions and if they match, set the escalation time on the ticket to 1 minute, then add the procedure we want to run to the ticket change stage, and this should run when the escalation time expires.



    typo
    [edited by: JB1975 at 2:03 AM (GMT -8) on Nov 16, 2016]
  • HI JB1975,

    If you get a chance, please send me the procedure, I can look at it.  I am still not following what business logic you are trying to accomplish.  As Paul pointed out, if you already have the ref, why are you querying for it?

    It would help to know in layman terms, what the procedure you are writing is trying to accomplish.

    Thanks,

    Charles