NAME

Kernel::System::Ticket::TicketSearch – Ticket search engine

PUBLIC INTERFACE

ATTRIBUTES

has TicketObject

Instantiated ticket object.

has DateRestrictionMap

Provides a map of sortable ticket keys to related database column names.

METHODS

TicketSearch()

Searches the database for tickets matching the search criteria.

All parameters are optional unless otherwise specified.

    my $Result = $TicketObject->TicketSearch(

        #
        # Parameters to influence result in things like form, length and order.
        #

        # Result type.
        # Valid options are 'ARRAY', 'COUNT', 'CHANGE' and 'HASH
        # 'ATTRIBUTE' is a supported, but special result type, that will be automatically
        # Added by method 'TicketCountByAttribute'.
        Result => 'HASH'    # defaults to 'HASH'

        # Permits searches for all tickets without filter conditions / WHERE clauses (default undef).
        # An error will be raised, if no filter conditions are added and this flag is false.
        AllowEmptyFilter => 1,

        # Maximum amount of results (unless 'COUNT' was specified as result)
        Limit => 100,       # defaults to 10

        # Time in seconds to cache results for the exact same query.
        # Searches involving full text or article related searches are always cached (default: 4 minutes)
        CacheTTL => 900,

        # Sort order (SortBy) and direction (OrderBy).
        # Can be specified by string or array parameters (in the sense of 'sort by X, then by Y, then by Z').
        # Valid options for SortBy are 'Age', 'Changed', 'Created', 'CustomerID', 'EscalationResponseTime',
        #   'EscalationSolutionTime', 'EscalationTime', 'EscalationUpdateTime', 'ID', 'Lock', 'Owner', 'PendingTime',
        #   'Priority', 'Queue', 'Responsible', 'Service', 'SLA', 'State', 'Ticket', 'TicketEscalation',
        #   'TicketNumber', 'Title', 'Type'
        #    and 'DynamicField_<FieldName>' (FieldName must refer to a valid ticket or article dynamic field).
        # Valid options for OrderBy are 'Down' (descending) and 'Up' (ascending).
        # Number of elements for SortBy and OrderBy must be equal.
        SortBy  => 'Age',   # defaults to 'Age'
        OrderBy => 'Down',  # defaults to 'Down'
        # or
        SortBy  => [ 'Age', 'Owner', ... ],
        OrderBy => [ 'Up',  'Down',  ... ],

        #
        # Parameters to restrict tickets by agent or customer user content permissions.
        #

        # Exactly one of UserID or CustomerUserID has to be provided.

        # Search in UserID (agent) context.
        # Searches for UserID 1 don't add any permission restrictions.
        UserID => 1,

        # Search in CustomerUserID (customer user) context.
        CustomerUserID => 'login',

        # Required ticket permissions for specified user.
        Permission => 'ro', # defaults to 'ro'

        #
        # All other parameters are used to specify the search result.
        # Different parameters are connected using 'AND' conditions (all parameters have to match),
        #   whereas values within a parameter (ID/Name e.g. SLAs and SLAIDs are treated as a single parameter)
        #   are connected using 'OR' conditions (any value has to match).
        #

        # Search for tickets with involvement by current user (checked via ticket history).
        # This option only makes sense if a UserID is provided.
        AgentInvolved => 1,

        # Search archived tickets.
        # If 'Ticket::ArchiveSystem' is enabled, the search will be restricted by archive flag.
        # 'y' = search for archived tickets, 'n' = search for non archived tickets. To find all tickets, specify both options.
        ArchiveFlags => [ 'y', 'n' ],    # defaults to 'n'

        # Search specified ticket(s).
        TicketID => 1,
        # or
        TicketID => [ 1, 2, ... ],

        # Exclude specified ticket(s) from search.
        ExcludeTicketID => 1,
        # or
        ExcludeTicketID => [ 1, 2, ... ],

        # Search specified ticket number(s) ('*/%' wildcards allowed).
        TicketNumber => '2020%',
        # or
        TicketNumber => [ '2020%', '2019*017*', ... ],

        # Search tickets with specified title(s) ('*/%' wildcards allowed).
        Title => '2020%',
        # or
        Title => [ '2020%', '2019*017*', ... ],

        # Search tickets with specified state type(s) or state type id(s).
        # NOTE: 'Open' and 'Closed' are virtual state types. They can be specified for compatibility.
        StateType => 'Open',    # All states which are grouped as open (new, open, pending, ...)
        # or
        StateType => 'Closed',  # All states which are grouped as closed (closed successful, closed unsuccessful, ...)
        # or
        StateType => [ 'open', 'new', ... ],

        # Search for specified general ticket properties.
        Locks          => [ 'unlock', 'tmp_lock', ... ],
        LockIDs        => [ 1, 2, ... ],
        OwnerIDs       => [ 1, 2, ... ],
        Priorities     => [ '1 very low', '2 low', ... ],
        PriorityIDs    => [ 1, 2, ... ],
        ResponsibleIDs => [ 1, 2, ... ],
        Queues         => [ 'raw', 'misc', ... ],
        QueueIDs       => [ 1, 2, ... ],
        Services       => [ 'Service A', 'Service B', ... ],
        ServiceIDs     => [ 1, 2, ... ],
        SLAs           => [ 'SLA A', 'SLA B', ... ],
        SLAIDs         => [ 1, 2, ... ],
        States         => [ 'new', 'open', ... ],
        StateIDs       => [ 1, 2, ... ],
        StateTypeIDs   => [ 1, 2, ... ],
        Types          => [ 'incident', 'change', ... ],
        TypeIDs        => [ 1, 2, ... ],
        WatchUserIDs   => [ 1, 2, ... ],

        # Include or exclude sub queues in search.
        # 1 = search tickets in specified queues including all of their subqueues
        # 0 = search tickets only in specified queues
        UseSubQueues => 0,

        # Search tickets with specified CustomerUserLogin(s).
        # CustomerUserLoginRaw uses values literally and takes precedence.
        # CustomerUserLogin allows use of '*/%' wildcards and logic operators (e.g. 'AND', 'OR', '!' ).
        CustomerUserLogin => '( logon% OR ( login* AND ! *disabled ) )',
        # or
        CustomerUserLogin => [ 'logon%', 'login*enabl*', ... ],
        # or
        CustomerUserLoginRaw => 'login1enabled',
        # or
        CustomerUserLoginRaw => [ '123', 'ABC', '123 && 456', ... ],

        # Search tickets with specified CustomerID(s).
        # CustomerIDRaw uses values literally and takes precedence.
        # CustomerID allows use of '*/%' wildcards and logic operators (e.g. 'AND', 'OR', '!' ).
        CustomerID => '( Exampl% OR ( Anothe* AND ! Thi* ) )',
        # or
        CustomerID => [ 'Exampl%', 'Exampl*Comp*', ... ],
        # or
        CustomerIDRaw => 'The Asterisk* Company',
        # or
        CustomerIDRaw => [ '123', 'ABC', '123 && 456', ... ],

        # Search for specified ticket properties on ticket creation.
        CreatedPriorities  => [ '1 very low', '2 low', ... ],
        CreatedPriorityIDs => [ 1, 2, ... ],
        CreatedQueues      => [ 'raw', 'misc', ... ],
        CreatedQueueIDs    => [ 1, 2, ... ],
        CreatedStates      => [ 'new', 'open', ... ],
        CreatedStateIDs    => [ 1, 2, ... ],
        CreatedTypes       => [ 'incident', 'change', ... ],
        CreatedTypeIDs     => [ 1, 2, ... ],
        CreatedUserIDs     => [ 1, 2, ... ],

        # Search for tickets containing specified ticket flag(s) and value(s).
        # Searches flags for TicketFlagUserID if specified, otherwise for UserID.
        TicketFlagUserID => 1,
        TicketFlag => {
            Seen => 1,
            ...
        }

        # Search for tickets not containing specified ticket flag(s) or different value(s).
        # Searches flags for TicketFlagUserID if specified, otherwise for UserID.
        TicketFlagUserID => 1,
        NotTicketFlag => {
            Seen => 1,
            ...
        }

        # Search for tickets with articles containing specified articles flag(s) and value(s).
        # Searches flags for ArticleFlagUserID if specified, otherwise for UserID.
        ArticleFlagUserID => 1,
        ArticleFlag => {
            Important => 1,
            ...
        }


        # Search for ticket and article dynamic fields.
        # There are different possible operators for searching.
        # At least one operator must be specified. If more operators are specified,
        #   they are connected using 'AND' conditions (all operators have to match).
        # Multiple values can be specified for any operator except 'Empty'. They are connected using 'OR' conditions
        #   (any value has to match).
        DynamicField_MyDynamicFieldName => {

            # Search for fields by presence of a value.
            # 1 = search fields witout a value
            # 0 = search fields with a/any value
            Empty => 1,

            # Literal search.
            Equals => '!great!',

            # Wildcard search.
            Like => [ '*grea*', 'fantast%', ... ],

            # Comparative search (only useful for numbers and datetime values).
            GreaterThan       => '101',
            GreaterThanEquals => '100',
            SmallerThan       => '2002-02-02 02:02:02',
            SmallerThanEquals => '2002-02-02 02:02:02',
        }

        # Search for article field(s).
        # Any searchable article backend field can be used, e.g.
        MIMEBase_From           => 'spam@example.com',
        MIMEBase_To             => 'service@example.com',
        MIMEBase_Cc             => 'client@example.com',
        MIMEBase_Subject        => 'VIRUS 32',
        MIMEBase_Body           => 'VIRUS 32',
        MIMEBase_AttachmentName => 'anyfile.txt',
        ...

        # Select if article field search parameters are connected using
        #   'AND' conditions (all parameters have to match)
        #   or 'OR' conditions (any parameter has to match).
        ContentSearch => 'AND', # or 'OR', defaults to 'AND'

        # Search article fields (see above) using a fixed prefix and or postfix.
        ContentSearchPrefix => '*',
        ContentSearchSuffix => '*',

        # Article field searches allow '*/%' wildcards by default.
        # Use this parameter if use of logic operators (e.g. 'AND', 'OR', '!' ) should be allowed.
        # This will also enable '*/%' wildcards and logic operators for 'Title' parameter.
        ConditionInline => 1,

        # This parameter in its original explanation is said to control whether to use full article text index.
        # In actuality, it just enables query caching if article table is joined based on article parameters.
        FullTextIndex => 1,

        # The following parameters are used to add relative and fixed date/time based restrictions. Relative time specifications take precedence.
        # Each parameter comes in four types which can be used in combination:
        # - <Param>OlderMinutes = event occured at least specified number of minutes ago
        # - <Param>NewerMinutes = event occured at most specified number of minutes ago
        # - <Param>OlderDate    = event occured at or before specified timestamp
        # - <Param>NewerDate    = event occured at or after specified timestamp
        #
        # Possible parameters in this context are:
        # - TicketCreateTime<Type> = ticket creation date/time
        # - TicketChangeTime<Type> = date/time of a history related ticket change
        # - TicketLastChangeTime<Type> = last ticket modification date/time
        # - TicketCloseTime<Type> = date/time at which ticket has been set to a closed state
        # - TicketLastCloseTime<Type> = last date/time at which ticket has been set to a closed state
        # - TicketPendingTime<Type> = ticket pending date/time (only matches if ticket is in a pending state)
        # - TicketEscalationTime<Type> = ticket escalation target date/time
        # - TicketEscalationUpdateTime<Type> = ticket update escalation target date/time
        # - TicketEscalationResponseTime<Type> = ticket response escalation target date/time
        # - TicketEscalationSolutionTime<Type> = ticket solution escalation target date/time
        # - ArticleCreateTime<Type> = creation date/time of any of ticket's article
        #
        # Examples:
        # - search tickets created less than an hour ago:
        TicketCreateTimeNewerMinutes => 60,
        # - search tickets last closed more than a week ago:
        TicketLastCloseTimeOlderMinutes => 60 * 24 * 7,
        # - search tickets with at least one article created in (or after) year 2020
        ArticleCreateTimeNewerDate => '2020-01-01 00:00:00',
        # - search tickets escalated before year 2020
        TicketEscalationTimeOlderDate => '2019-12-31 23:59:59',
    );

Returns list or count of matching tickets, depending on 'Result' parameter.

    my $TicketCount = $TicketObject->TicketSearch(
        Result => 'COUNT',
        ...
    );

    $TicketCount = 0;

or

    my @TicketIDs = $TicketObject->TicketSearch(
        Result => 'ARRAY',
        ...
    );

    @TicketDIDs = ( 1, 2, 3, ...);

or

    my %Tickets = $TicketObject->TicketSearch(
        Result => 'HASH',
        ...
    );

    %Tickets = (
        1 => '20200101700001',
        3 => '20200101700002',
        2 => '20200101700003',
        ...
    );

or

    my @Tickets = $TicketObject->TicketSearch(
        Result => 'CHANGE',
        ...
    );

    @Tickets = (
        {
            TicketID     => 1,
            TicketNumber => '20200101700001',
            Changed      => '2020-01-01 00:00:00', # TicketLastChangeTime
        },
        ...
    );

TicketCountByAttribute()

Returns count of tickets per value for a specific attribute.

    my $TicketCount = $TicketObject->TicketCountByAttribute(
        Attribute => 'ServiceID',
        TicketIDs => [ 1, 2, 3 ],
        Limit     => 1000,  # optional, default: 10_000
    );

Returns:

    $TicketCount = {
        Attribute_Value_1 => 1,
        Attribute_Value_2 => 3,
        ...
    };

PRIVATE INTERFACE

ATTRIBUTES

has _Parameters

Parameter object containing the prepared ticket search parameters.

has _SQL

SQL object that constructs and executes the database query and takes care of the result- and cache-handling.

has _DynamicFieldLookup

Lookup attribute for dynamic fields, that is used to cache dynamic fields during the lifetime of the TicketSearch object.

has _AttributeToDatabase

Provides database column names for certain attributes. This is used via method TicketCountByAttribute.

METHODS

has _DynamicFieldGet

Retrieve and cache dynamic field objects (in-memory in attribute _DynamicFieldLookup).

_HandleResult()

Handle columns selects based on the result type.

_HandleLimit()

Handle result limit, based on the provided or default parameters.

_HandleArchiveFlags()

Restrict by archive status.

_HandleTicketID()

Restrict to individual ticket(s). Used by GenericAgent to filter for events on single tickets using the job's ticket filter.

_HandleExcludeTicketID()

Exclude individual ticket(s).

_HandleOwnerIDs()

Restrict by owner identifiers.

_HandleResponsibleIDs()

Restrict by responsible identifiers.

_HandleTypes()

Restrict by types (union with possible type ids).

_HandleLocks()

Restrict by lock (union with possible lock ids).

_HandlePriorities()

Restrict by priority (union with possible priority ids).

_HandleServices()

Restrict by service (union with possible service ids).

_HandleSLAs()

Restrict by SLA (union with possible SLA ids).

_HandleQueues()

Restrict by queues / sub-queues (union with possible queue ids).

_HandleStates()

Restrict by state, including state type and pending time states (intersection).

_HandleStringMatches()

Restrictions by string matches.

_HandleDateRestrictions()

Restrictions by date/time.

_HandleChangeTime()

Restrict by change date/time.

_HandleCloseTime()

Restrict by close date/time.

_HandleLastCloseTime()

Restrict by last close date/time.

_HandleArticleCreateTime()

Restrict by article date/time.

_HandleAgentPermissions()

Agent permission based restrictions (Restrict to tickets in queues accessible by agent).

_HandleCustomerUserPermissions()

CustomerUser permission based restrictions (using queue table).

Restrict to tickets in groups accessible by customer user, meaning either where CustomerUserID matches or where CustomerID matches (one of the) customer id(s) of customer user. In case customer group support and extra permission context are enabled, customer users might gain access to further Group<->CustomerID combinations. In these cases restrict to tickets where any combination of 'ticket in <Queue> of <Group> accessible by <CustomerID>' is met.

_HandleCreatedTypes()

Restrict by history types (union with possible type ids).

_HandleCreatedStates()

Restrict by history states (union with possible state ids).

_HandleCreatedQueues()

Restrict by history queues (union with possible queue ids).

_HandleCreatedPriorities()

Restrict by history priorities (union with possible priority ids).

_HandleCreatedUserIDs()

Restrict by history priorities (union with possible priority ids).

_HandleWatchUserIDs()

Restrict by watched tickets (using ticket_watcher table).

_HandleTicketFlag()

Restrict by ticket flags (using ticket_flag table).

_HandleNotTicketFlag()

Restrict by ticket flag negation (using ticket_flag table).

_HandleArticleFlag()

Restrict by article flag.

_HandleArticleSearchableFields()

Restrict by article attributes.

_HandleDynamicFields()

Restrict by dynamic fields (ticket and article).

_HandleSortOrder()

Process sort and order options.

_HandleCountByAttribute()

Count by ticket attribute.

_DateTimeParamCheck()

Verifies the existence of DateTime-parameters.

    my $Bool = $Self->_DateTimeParamCheck('TicketPendingTime');

_DateTimeRestrictionCalculate()

Calculates the SQL based restrictions for given DateTime-parameters.

    my $Restrictions = $Self->_DateTimeRestrictionCalculate('TicketPendingTime');

Returns

    [
        {
            Operator       => $TypeToOperator{$_},
            TargetDateTime => $TargetDateTime{$_},
        }
        ...
    ];

_IDNameIntersect()

If both options are used, only intersection of values is valid.

_IDNameUnion()

If both options are used, union of values is valid.

_ArticleJoin()

Registers a join of the article table and activates a corresponding flag, which is used, to just join the table one time.

Apart from that, if the article table is joined, we register grouping by TicketID (t.id), to prevent duplicated results.

_HistoryJoin()

Registers a join of the ticket history table and activates a corresponding flag, which is used, to just join the table one time.

Apart from that, if the ticket history table is joined, we register grouping by TicketID (t.id), to prevent duplicated results.

_HistoryTypeJoin()

Registers a join of the ticket history table using method _HistoryJoin and registers a WHERE condition on the history_type_id field for history type 'NewTicket', if not already registered.

_QueueJoin()

Registers a join of the queue table and activates a corresponding flag, which is used, to just join the table one time.

_DynamicFieldJoin()

Registers a join of a dynamic field by name (with or without 'DynamicField_' prefix).

The first argument is the dynamic field name to be used for joining. The second argument is a boolean, that indicates, if the join must be performed as LEFT JOIN.

True value means 'LEFT JOIN', while false value means 'INNER JOIN'.

    my $Field = $Self->_DynamicFieldJoin('MyDynamicField');
    my $Field = $Self->_DynamicFieldJoin( 'MyDynamicFieldWithLeftJoin', 1 );

The return value is the instantiated field object.

has _Analyser

Attribute that holds the analyzer data.

_Analyse

Adds the params to be analyzed.

    my $Self->_Analyse(
        Key => 'Value'
    );