Kaseya Community

SQL Query Help

This question is answered

We use Custom fields in SD which are stored in XML data typs in the view/DB. I'm trying to run a parser on it so I can build a report showing tickets and correlations to specific listed custom field types. I'm having trouble with my query though:

--Example query to test xml parsing against Kaseya DB

Select Top(1000) t.field.query('.')
from kasadmin.vbo_SDIncidents_General p
Cross Apply p.incident_custom_fields.nodes('/customFields/Field') as t(Field)

I'm trying to get something in the format of:

Ticket Number, CustomField, Category, Sub-Category

CSN349344       Automation      Patching     Fail to install

Verified Answer
  • Hi danrche,

    The SQL query for this data set is as follows:

    SELECT * FROM kasadmin.vSDTicket v

    LEFT JOIN KSD.CustomFieldsValues cfv WITH (NOLOCK) ON cfv.entityId = v.id

    LEFT JOIN KSD.CustomFieldsValuesBlob cfvb WITH (NOLOCK) ON cfvb.entityId = v.id

    Hope this information helps.

    Matt

All Replies
  • Hi danrche,

    If this is Kaseya 6.3, you should have a Service Desk data set called "Ticket-<deskName>" under the Info Center->Report Parts function when you look in the Report Parts cabinet under the Service Desk folder.  <deskName> will be the Name on your Desk.  This data set will have all the built in and custom fields for that desk listed as separate fields and you should be able to generate the report from there using a Table part.  Hope this helps,

    Matt Warburton

    Kaseya Professional Services

  • it's 6.2 and we're trying to get the query setup for an external portal for the customers. The portal is for our client contacts to view the ticket reports for their internal BI.

    I have a query that works for everything except the custom fields, was a new addition to the ticketing. I've never used xml data types in sql before so I'm feeling a bit lost.

    Thank you for the reply though. I'm sure anyone using 6.3 will appreciate that.

  • Hi danrche,

    The SQL query for this data set is as follows:

    SELECT * FROM kasadmin.vSDTicket v

    LEFT JOIN KSD.CustomFieldsValues cfv WITH (NOLOCK) ON cfv.entityId = v.id

    LEFT JOIN KSD.CustomFieldsValuesBlob cfvb WITH (NOLOCK) ON cfvb.entityId = v.id

    Hope this information helps.

    Matt

  • huh, didn't even see that table...... Thanks Matt.