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