SQL to query tickets (service cases) in CRM

How to query Microsoft CRM 2011 service cases (tickets) using sql.

This information is based on querying Microsoft CRM 2011. It may or may not work with other versions of Microsoft CRM.

To query all tickets after a certain date:

select * from Incidentbase

where CreatedOn >='2011-05-01 00:00:00.000'

order by Createdon desc

Incidentbase has a field called statecode that tells you the status of the service case/ticket:

0 = active

1= resolved or completed

2 = cancelled

When a ticket/service case is resolved in CRM, a special activity called a "ticket resolution" is created.

To query activities in CRM, there are various tables that contain all the different activities in them (emails, phone calls, ticket resolutions, etc...). One of the key activity tables is called "ActivityPointerBase". There is a key field in ActivityPointerBase called ActivityTypeCode that tells you what type of activity the record is.

4202 is an email

4212 is a task

4206 is a ticket resolution

So, to see ticket resolutions on service cases:

select * from ActivityPointerBase where ActivityTypeCode = 4206

Keep in mind that ActivityPointerBase also has a "statecode" field that tells you whether the activity was open, completed or cancelled. Ticketresolutions can only be completed or cancelled (a few of the activities in CRM, such as emails and ticket resolutions, cannot have an open status). So for ticketresolutions:

1 = completed

2 = cancelled

(i.e. if you are wondering, open activities are a 0)

To combine ticket information with ticket resolution information:

select a.* from IncidentBase as a

left join ActivityPointerBase as b

ona.incidentid = b.RegardingObjectId