Who can use this feature?
- Private Cloud Database Account users can use this information to perform queries against either the live database or a locally restored database backup.
- Available to Private Cloud customers with database access only.
This article covers some of the common questions and scenarios around database-level reporting for customers who have requested to access to their Private Cloud database.
A few things to note:
- SaaS multi-tenant customers are not able to access their database directly.
- While TeamDynamix does not support database-first reporting, we do realize there is some information that is not available with the common reporting tools built into the application.
- If you find yourself in need of information from your Private Cloud database, first ensure you have access to your database, then submit a Support Ticket.
Some database consulting or queries may involve using previously purchased Value Added Services request/VAS hours. If that is the case, we'll let you know in your request.
Is there documentation that describes the TDX database schema (or where does the database table for "x" reside)?
There is a PDF attached to the following KB that outlines the various database tables:
https://solutions.teamdynamix.com/TDClient/1965/Portal/KB/ArticleDet?ID=106443
There are also subsequent database table changes from each version of TDX attached to the KB showing how the tables have changed or been added to over time.
Can querying my "live" PC database impact performance?
Yes, both the Production and Sandbox databases for a private cloud client reside on the same server. Querying either database with heavy-hitting or large quantity queries can impact performance for both environments. For these reasons, we strongly recommend against querying your live database. Instead, any intense data retrieval that must come from the database directly should be performed against locally-restored copies of your database, which you can request from TeamDynamix.
Contact TeamDynamix Support by submitting a support ticket if this is something you'd be interested in setting up or learning more about, and we can connect you with our Infrastructure resources to provide additional information.
What kind of database server does TeamDynamix use?
Microsoft SQL Server
How can I request that mass changes to Tickets/assets/CIs/other be made at the database level?
Whenever possible, we prefer not to make mass changes at the database level as this will become increasingly difficult to deliver as the product evolves. Initially, a request should be submitted to TeamDynamix support outlining what you are looking for. A VAS request is likely going to be needed to facilitate any data modification requests, but if you do not already have a VAS agreement, we'll put you in touch with your CSX representative to discuss further. We may be able to facilitate your data modification request in a way that does not require direct database modifications to be necessary.
How can I locate Ticket Task Feed data?
The full feed information is contained in the ItemUpdates, ItemUpdateComments and ItemUpdateLikes tables.
This uses a generic model of identifying the source of the feed. ItemUpdates is the top-level feed entries and has a handful of fields to be aware of for joining:
- IU_BEID. This is your tenant ID. Use 'yourBEIDHere'. You can find your BEID from TDAdmin in the Security tab as an organization administrator.
- IU_ComponentID. This tells the app what type of item it is. It aligns with values from the Components table. For tickets, use the value 9. For ticket tasks, use the value 25.
- IU_ItemID. This is the actual ID of the item. For tickets, the ticket ID. For ticket tasks, the task ID.
- IU_PlanID. This is mostly for project plans, but is also used by ticket task feed items. For ticket tasks, IU_PlanID will house the ID of the parent ticket.
- IU_AppID. This is the application ID of the item for platformed items (tickets, assets, etc.). For tickets, use the application ID the ticket is in.
- IU_ID. This is the ID of the top-level feed item to use in ItemUpdateComments or ItemUpdateLikes tables.
Why is there inconsistent naming between GUI field names and database table names?
We unfortunately have multiple instances where our database tables are inconsistently named and/or out of date with what the GUI presents for data elements. We know that this makes your job more difficult and we apologize for this. As the software has grown exponentially, it is not possible to continually rename the database fields to align with the GUI.
What tables can be checked to find all the custom attributes on a ticket form?
- The TypeFields.TF_FormID column maps to the Forms table. The Forms table is where general metadata about the form itself is stored. This is not the list of form fields for a form, nor the field settings, default values or customizations.
- The TypeFields table contains all of the aforementioned form fields in a form, as well as their settings, default values and customizations.
- The TypeFields.TF_FieldID column maps to the Attributes table. The Attributes table is where general field metadata is stored. This would show things like the out-of-the-box field name, if it is a custom attribute, the field type, the field data type (if textbox), header text (for reports), etc.
- The TypeFields table field IDs for out-of-the-box attributes, as well as their corresponding Attribute table backing records, should not change. Once you find a form with the Responsible non-custom attribute field, you can always filter on that field ID to find other forms which have that field present. Keep in mind here that there is a single field for responsibility though. While TF_DefaultText should always show the text-based name of the choice, TF_DefaultValue is not strongly typed. It could contain a numerical group ID value or a GUID user ID value.
We are attempting to pull "Read By" data for items in our database, where is it stored?
- ItemViews is the aggregated "Read By" data for different types of items. It has one row per item with first read, last read and total read counts by item by user UID.
- ItemViewsRolling is the raw "Read By" data for every single read on items that support read by data. This table has no ReadCount column as each record equates to one "read."
So if you've read a ticket 10 times, the ItemsView record would have one row for your user UID, a read count of 10, the first and most recent times you've read the KB. ItemViewsRolling would have 10 rows for your user UID on that KB, for each of the individual reads.
ItemViewsRolling is also a "rolling" 3-month window of data or it would be huge. There is a job which runs monthly to keep data inside in that rolling 3-month window only. When the table is rolled, individual read by records over 3 months old are permanently removed.
You can tell what sort of "item" it is by looking at the ComponentID columns, matched to the Components table.
What table contains the saved reports and the owner UIDs?
You'll find this data in the Searches table. However unfortunately we don't store the actual report or search SQL in the database. The application builds its own reporting SQL from saved metadata about the report builder columns/filters/ordering/etc.
Where can I find a list of all ticketing forms that contain a default Responsible value of a group specifically?
The TypeFields table is the source of where a form's field configurations, as seen in Admin or Client Portal when managing a form, are stored.
Some things to know:
- The TypeFields.TF_FormID column maps to the Forms table. The Forms table is where general metadata about the form itself is stored. This is not the list of form fields for a form, nor the field settings, default values or customizations.
- The TypeFields table contains all of the aforementioned form fields in a form, as well as their settings, default values and customizations.
- The TypeFields.TF_FieldID column maps to the Attributes table. The Attributes table is where general field metadata is stored. This would show things like the out-of-the-box field name, if it is a custom attribute, the field type, the field data type (if textbox), header text (for reports), etc.
- The TypeFields table field IDs for out-of-the-box attributes, as well as their corresponding Attribute table backing records, should not change. Once you find a form with the Responsible non-custom attribute field, you can always filter on that field ID to find other forms which have that field present. Keep in mind here that there is a single field for responsibility though. While TF_DefaultText should always show the text-based name of the choice, TF_DefaultValue is not strongly typed. It could contain a numerical group ID value or a GUID user ID value.
How can I find the selected value for a custom CI attribute on a ticket?
This will be the same for everything where you choose data from another type of TDX data, such as:
- Asset
- Location
- Location and Room
- Person
- CI
On the AttributeValues table are columns for:
- AV_RelatedItemComponentID – 63 (Configuration Item)
- AV_RelatedItemID – Configuration Item ID#
- AV_RelatedItemGuid
- AV_RelatedItemName – Title of Configuration Item
- AV_RelatedItemParentID
- AV_RelatedItemParentName
You might get some easy info from the AV_RelatedItemName and AV_RelatedItemParentName, but you can also just use AV_RelatedItemComponentID and AV_RelatedItemID or AV_RelatedItemGuid (for people) to join to the actual table of info to get whatever info you want.
The records you are looking for are in AttributeValues, they just have no attribute choices.
- To find CIs for example:
- You'd probably have to filter
AttributeValues.AV_RelatedItemComponentID to 63 and AttributeValues.AV_RelatedItemID to ConfigurationItems.CI_ID.
- For people, similar concept:
- You'd probably have to filter
AttributeValues.AV_RelatedItemComponentID to 31 and AttributeValues.AV_RelatedItemGuid to Users.[GUID].