Query Guide: AttributeValues Table

The AttributeValues Table contains the custom fields information that is associated with a work item. For example, it would indicate that ticket 12345 has “Blue” selected for the Eye Color attribute. This may require joining from AttributeValues to AttributeChoices on choice ID to get the choice name.

Query Examples

You may use the following examples as a starter template.

Please note that the example does not cover every possible column to select, join, filter, or order by. You may need to modify this query to suit your needs. Refer to the related Getting Started Guide for Database Queries article for how to identify your @BEIDInt or @BEID values (tenant ID).

Text-Based Attributes

These would be attributes like textboxes, text areas, date pickers and date/time pickers.

Select
    Top 10
    av.AV_ID,
    av.AV_ATTID,
    av.AV_ItemComponentID,
    cmpnt.CMPNT_Name,
    av.AV_ItemID,
    av.AV_ValueText,
    av.AV_ValueInt,
    av.AV_ValueDecimal,
    av.AV_ValueDateTime
    /* Add or change selected columns as necessary. */
From
    dbo.AttributeValues av With (NOLOCK)
    Inner Join dbo.Attributes att With (NOLOCK) On av.AV_ATTID = att.Att_ID
    Inner Join dbo.Components cmpnt With (NOLOCK) On av.AV_ItemComponentID = cmpnt.CMPNT_ID
Where
    att.Att_BEID = @BEID
    /* To filter for all saved values for a specific attribute ID, use the below line. */
    And av.AV_ATTID = ATTRIBUTE_ID_HERE
    /* To filter to all saved value for a specific item, use the below lines. 
       Refer to the Components table for valid values. For example, use 9 for tickets. 
       Use AV_ItemID to match the specific item's ID, such as a ticket ID. */
    -- And av.AV_ItemComponentID = COMPONENT_ID_HERE
    -- And av.AV_ItemID = ITEM_ID_HERE

Single-Choice Attributes

These would be attributes like dropdowns (including yes/no), radio button lists and color fields.

Select
    Top 10
    av.AV_ID,
    av.AV_ATTID,
    av.AV_ItemComponentID,
    cmpnt.CMPNT_Name,
    av.AV_ItemID,
    av.AV_Value,
    av.AV_ValueText,
    attc.Attc_Name
    /* Add or change selected columns as necessary. */
From
    dbo.AttributeValues av With (NOLOCK)
    Inner Join dbo.Attributes att With (NOLOCK) On av.AV_ATTID = att.Att_ID
    Inner Join dbo.Components cmpnt With (NOLOCK) On av.AV_ItemComponentID = cmpnt.CMPNT_ID
    Left Outer Join dbo.AttributeChoices attc With (NOLOCK) On av.AV_ATTID = attc.ATTC_ATTID And av.AV_Value = attc.ATTC_ID
Where
    att.Att_BEID = @BEID
    /* To filter for all saved values for a specific attribute ID, use the below line. */
    And av.AV_ATTID = ATTRIBUTE_ID_HERE
    /* To filter to all saved value for a specific item, use the below lines. 
       Refer to the Components table for valid values. For example, use 9 for tickets. 
       Use AV_ItemID to match the specific item's ID, such as a ticket ID. */
    -- And av.AV_ItemComponentID = COMPONENT_ID_HERE
    -- And av.AV_ItemID = ITEM_ID_HERE

Multiple-Choice Attributes

These would be attributes like multiselects and checkbox lists. Note that these fields will have a row for each saved value selected for the item ID/attribute ID combination. For example, if attribute 789 is a multi-select and has two choices saved on ticket 12345, there will be two rows (one for each selected choice) in AttributeValues.

Select
    Top 10
    av.AV_ID,
    av.AV_ATTID,
    av.AV_ItemComponentID,
    cmpnt.CMPNT_Name,
    av.AV_ItemID,
    av.AV_Value,
    av.AV_ValueText,
    attc.Attc_Name
    /* Add or change selected columns as necessary. */
From
    dbo.AttributeValues av With (NOLOCK)
    Inner Join dbo.Attributes att With (NOLOCK) On av.AV_ATTID = att.Att_ID
    Inner Join dbo.Components cmpnt With (NOLOCK) On av.AV_ItemComponentID = cmpnt.CMPNT_ID
    Left Outer Join dbo.AttributeChoices attc With (NOLOCK) On av.AV_ATTID = attc.ATTC_ATTID And av.AV_Value = attc.ATTC_ID
Where
    att.Att_BEID = @BEID
    /* To filter for all saved values for a specific attribute ID, use the below line. */
    And av.AV_ATTID = ATTRIBUTE_ID_HERE
    /* To filter to all saved value for a specific item, use the below lines. 
       Refer to the Components table for valid values. For example, use 9 for tickets. 
       Use AV_ItemID to match the specific item's ID, such as a ticket ID. */
    -- And av.AV_ItemComponentID = COMPONENT_ID_HERE
    -- And av.AV_ItemID = ITEM_ID_HERE

Person Attributes

Select
    Top 10
    av.AV_ID,
    av.AV_ATTID,
    av.AV_ItemComponentID,
    cmpnt.CMPNT_Name,
    av.AV_ItemID,
    av.AV_Value,
    av.AV_ValueText,
    av.AV_RelatedItemGuid,
    u.[GUID],
    u.User_FullName,
    u.User_AlertEmail
    /* Add or change selected columns as necessary. */
From
    dbo.AttributeValues av With (NOLOCK)
    Inner Join dbo.Attributes att With (NOLOCK) On av.AV_ATTID = att.Att_ID
    Inner Join dbo.Components cmpnt With (NOLOCK) On av.AV_ItemComponentID = cmpnt.CMPNT_ID
    Left Outer Join dbo.Users u With (NOLOCK) On av.AV_RelatedItemGuid = u.[GUID]
Where
    att.Att_BEID = @BEID
    And av.AV_RelatedItemComponentID = 31
    /* To filter for all saved values for a specific attribute ID, use the below line. */
    And av.AV_ATTID = ATTRIBUTE_ID_HERE
    /* To filter to all saved value for a specific item, use the below lines. 
       Refer to the Components table for valid values. For example, use 9 for tickets. 
       Use AV_ItemID to match the specific item's ID, such as a ticket ID. */
    -- And av.AV_ItemComponentID = 31
    -- And av.AV_ItemID = ITEM_ID_HERE

Asset Attributes

Select
    Top 10
    av.AV_ID,
    av.AV_ATTID,
    av.AV_ItemComponentID,
    cmpnt.CMPNT_Name,
    av.AV_ItemID,
    av.AV_Value,
    av.AV_ValueText,
    av.AV_RelatedItemID,
    a.AS_ID,
    a.AS_Name,
    a.AS_AppID,
    a.AS_SerialNumber,
    a.AS_Tag
    /* Add or change selected columns as necessary. */
From
    dbo.AttributeValues av With (NOLOCK)
    Inner Join dbo.Attributes att With (NOLOCK) On av.AV_ATTID = att.Att_ID
    Inner Join dbo.Components cmpnt With (NOLOCK) On av.AV_ItemComponentID = cmpnt.CMPNT_ID
    Left Outer Join dbo.Assets a With (NOLOCK) On av.AV_RelatedItemID = a.AS_ID
Where
    att.Att_BEID = @BEID
    And av.AV_RelatedItemComponentID = 27
    /* To filter for all saved values for a specific attribute ID, use the below line. */
    And av.AV_ATTID = ATTRIBUTE_ID_HERE
    /* To filter to all saved value for a specific item, use the below lines. 
       Refer to the Components table for valid values. For example, use 9 for tickets. 
       Use AV_ItemID to match the specific item's ID, such as a ticket ID. */
    -- And av.AV_ItemComponentID = 27
    -- And av.AV_ItemID = ITEM_ID_HERE

Configuration Item Attributes

Select
    Top 10
    av.AV_ID,
    av.AV_ATTID,
    av.AV_ItemComponentID,
    cmpnt.CMPNT_Name,
    av.AV_ItemID,
    av.AV_Value,
    av.AV_ValueText,
    av.AV_RelatedItemID,
    ci.CI_ID,
    ci.CI_Name,
    ci.CI_AppID,
    ci.CI_Description,
    ci.CI_ExternalID
    /* Add or change selected columns as necessary. */
From
    dbo.AttributeValues av With (NOLOCK)
    Inner Join dbo.Attributes att With (NOLOCK) On av.AV_ATTID = att.Att_ID
    Inner Join dbo.Components cmpnt With (NOLOCK) On av.AV_ItemComponentID = cmpnt.CMPNT_ID
    Left Outer Join dbo.ConfigurationItems ci With (NOLOCK) On av.AV_RelatedItemID = ci.CI_ID
Where
    att.Att_BEID = @BEID
    And av.AV_RelatedItemComponentID = 63
    /* To filter for all saved values for a specific attribute ID, use the below line. */
    And av.AV_ATTID = ATTRIBUTE_ID_HERE
    /* To filter to all saved value for a specific item, use the below lines. 
       Refer to the Components table for valid values. For example, use 9 for tickets. 
       Use AV_ItemID to match the specific item's ID, such as a ticket ID. */
    -- And av.AV_ItemComponentID = 63
    -- And av.AV_ItemID = ITEM_ID_HERE

Location Attributes

Select
    Top 10
    av.AV_ID,
    av.AV_ATTID,
    av.AV_ItemComponentID,
    cmpnt.CMPNT_Name,
    av.AV_ItemID,
    av.AV_Value,
    av.AV_ValueText,
    av.AV_RelatedItemID,
    l.Loc_ID,
    l.Loc_Name,
    l.Loc_Address,
    l.Loc_City,
    l.Loc_State,
    l.Loc_Zip,
    l.Loc_Country
    /* Add or change selected columns as necessary. */
From
    dbo.AttributeValues av With (NOLOCK)
    Inner Join dbo.Attributes att With (NOLOCK) On av.AV_ATTID = att.Att_ID
    Inner Join dbo.Components cmpnt With (NOLOCK) On av.AV_ItemComponentID = cmpnt.CMPNT_ID
    Left Outer Join dbo.Locations l With (NOLOCK) On av.AV_RelatedItemID = l.Loc_ID
Where
    att.Att_BEID = @BEID
    And av.AV_RelatedItemComponentID = 71
    /* To filter for all saved values for a specific attribute ID, use the below line. */
    And av.AV_ATTID = ATTRIBUTE_ID_HERE
    /* To filter to all saved value for a specific item, use the below lines. 
       Refer to the Components table for valid values. For example, use 9 for tickets. 
       Use AV_ItemID to match the specific item's ID, such as a ticket ID. */
    -- And av.AV_ItemComponentID = 71
    -- And av.AV_ItemID = ITEM_ID_HERE

Location and Room Attributes

Select
    Top 10
    av.AV_ID,
    av.AV_ATTID,
    av.AV_ItemComponentID,
    cmpnt.CMPNT_Name,
    av.AV_ItemID,
    av.AV_Value,
    av.AV_ValueText,
    av.AV_RelatedItemID,
    lr.LR_ID,
    lr.LR_Name,
    lr.LR_Description,
    lr.LR_LocationID,
    lr.LR_Floor,
    lr.LR_Capacity
    /* Add or change selected columns as necessary. */
From
    dbo.AttributeValues av With (NOLOCK)
    Inner Join dbo.Attributes att With (NOLOCK) On av.AV_ATTID = att.Att_ID
    Inner Join dbo.Components cmpnt With (NOLOCK) On av.AV_ItemComponentID = cmpnt.CMPNT_ID
    Left Outer Join dbo.LocationRooms lr With (NOLOCK) On av.AV_RelatedItemID = lr.LR_ID
Where
    att.Att_BEID = @BEID
    And av.AV_RelatedItemComponentID = 80
    /* To filter for all saved values for a specific attribute ID, use the below line. */
    And av.AV_ATTID = ATTRIBUTE_ID_HERE
    /* To filter to all saved value for a specific item, use the below lines. 
       Refer to the Components table for valid values. For example, use 9 for tickets. 
       Use AV_ItemID to match the specific item's ID, such as a ticket ID. */
    -- And av.AV_ItemComponentID = 80
    -- And av.AV_ItemID = ITEM_ID_HERE

Joining Single Choice Custom Attributes to a Ticket Query

When starting a query with other types of base data tables, such as issues or projects, refer to the Components table for the appropriate AttributeValues.AV_ItemComponentID value to use in joins.

Select
    tck.Tck_ID,
    tck.Tck_AppID,
    tck.Tck_Title,
    tck.Tck_Description,
    tck.Tck_TypeID,
    tck.Tck_AccountID,
    tck.Tck_StatusID,
    tck.Tck_FormID,
    tck.Tck_ServiceID,
    tck.Tck_ServiceOfferingID,
    /* Requestor UID */
    tck.Tck_ContactUID,
    tck.Tck_CreatedByUID,
    tck.Tck_CreatedDate,
    tck.Tck_LastModifiedByUID,
    tck.Tck_LastModifiedDate,
    tck.Tck_ClosedByUID,
    tck.Tck_ClosedDate,
    /* Custom Attribute 1 information */
    av1.AV_ValueText As Attribute1Value,
    /* Custom Attribute 2 information */
    av2.AV_Value As Attribute2ChoiceID,
    attc2.ATTC_Name As Attribute2ChoiceName
    /* Add or change selected columns as necessary. */
From
    dbo.Tickets tck With (NOLOCK)
    
    /* Example Text-Based Custom Attribute 1 (text textbox) Join */
    Left Outer Join dbo.AttributeValues av1 With (NOLOCK) On 
        av1.AV_ItemComponentID = 9 
        and av1.AV_ItemID = tck.Tck_ID
        and av1.AV_ATTID = ATTRIBUTE_1_ID_HERE

    /* Example Single Choice Custom Attribute 2 (dropdown) Join */
    Left Outer Join dbo.AttributeValues av2 With (NOLOCK) On 
        av2.AV_ItemComponentID = 9 
        and av2.AV_ItemID = tck.Tck_ID
        and av2.AV_ATTID = ATTRIBUTE_2_ID_HERE
    Left Outer Join dbo.AttributeChoices attc2 With (NOLOCK) On 
        av.AV_ATTID = attc2.ATTC_ATTID 
        And av.AV_Value = attc2.ATTC_ID

Where
    tck.Tck_BEIDInt = @BEIDInt
    And tck.Tck_ID = TICKET_ID_HERE
Print Article

Related Articles (1)

Getting Started Guide for Database Queries
This article will serve to assist organizations who wish to start querying their private cloud database for extended reporting scenarios. The following information is useful to keep in mind for all query situations.