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 pCross 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
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.
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,
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.
huh, didn't even see that table...... Thanks Matt.