ticket search using SQL+TicketSearch

English! place to talk about OTRS development, programming and coding

Moderator: tto

Post Reply
User avatar
rvaldomir
OTRS wizard
Posts: 155
Joined: 04 Aug 2014, 23:59
OTRS Version?: 3.3.8
Real Name: Roberto A. Valdomir

ticket search using SQL+TicketSearch

Post by rvaldomir » 01 Dec 2015, 19:46

Hi,

I want to show ticket in a widget that matches with some conditions, example no service, no ticket type or no dynamic field is filled.
a.png
now I can use the ticketSearch but does not provide the "negative" to search.

It is possible to use a sql query combined with ticket search ?

my %TicketSearchSummary = (
"Uncategorized Tickets"=> {
OwnerIDs => [ $Self->{UserID}, ],
Locks => undef,
StateType => [ 'new', 'open', 'pending reminder', 'pending auto' ],
# SQL HERE TO FILTER SOME OTHER CONDITION

},
);
You do not have the required permissions to view the files attached to this post.
​Roberto A. Valdomir
Mobile: (+598) 93 868 147 | Skype: roberto.a.valdomir
@roberto.valdomir | re.vu/robertovaldomir

----------------------------------------------------------------------------------------
OTRS 3.3.8 | RHEL 6 x64 | Oracle 11g | Custom Development by OTRS365

User avatar
tto
Moderator
Posts: 315
Joined: 09 Jan 2007, 15:24
OTRS Version?: OTRS 5.0.x
Real Name: Torsten
Company: c.a.p.e. IT GmbH
Location: Chemnitz
Contact:

Re: ticket search using SQL+TicketSearch

Post by tto » 01 Dec 2015, 19:58

Hi,
rvaldomir wrote: I want to show ticket in a widget that matches with some conditions, example no service, no ticket type or no dynamic field is filled.
...indeed a "NOT"- or "EMPTY"-TicketSearch option would be quite useful.
rvaldomir wrote:
a.png
...cool, looks nice.

Why not using some default ticket type or -service instead for all tickets created by mail. All others could get their default value bei mandatory input or generic agents. Then you could reconfigure a ticket dashlet which shows you these tickets or use KIX4OTRS which allows to assign ticket search templates to ticket dashlets.
rvaldomir wrote: now I can use the ticketSearch but does not provide the "negative" to search.
It is possible to use a sql query combined with ticket search ?
...nope. Unless someone develops it. I'd prefer the NOT-Option in TicketSearch rather than some "wild" SQL-appendix.

regards, T.
--
KIX 17.x (fork of OTRS)
Professional KIX-, or OTRS-integration, development and consulting by c.a.p.e. IT - http://www.cape-it.de
For questions and hints regarding KIX(4OTRS) please go to https://forum.kixdesk.com/
Bei Fragen und Hinweisen zu KIX(4OTRS) bitte an https://forum.kixdesk.com/ wenden.

User avatar
rvaldomir
OTRS wizard
Posts: 155
Joined: 04 Aug 2014, 23:59
OTRS Version?: 3.3.8
Real Name: Roberto A. Valdomir

Re: ticket search using SQL+TicketSearch

Post by rvaldomir » 01 Dec 2015, 20:33

Yes could be really useful to have the NOT or EMPTY, for DynamicField a LIKE exists, probably a NOT LIKE will be great too. Would be a great API extension.

--------------------
Why not using some default ticket type or -service instead for all tickets created by mail. All others could get their default value bei mandatory input or generic agents. Then you could reconfigure a ticket dashlet which shows you these tickets or use KIX4OTRS which allows to assign ticket search templates to ticket dashlets.

rvaldomir wrote:
now I can use the ticketSearch but does not provide the "negative" to search.
It is possible to use a sql query combined with ticket search ?


...nope. Unless someone develops it. I'd prefer the NOT-Option in TicketSearch rather than some "wild" SQL-appendix.
at this point we are forcing categorization (dynamic fields) on ticket closure. But we want to keep an eye on this so we want to deliver this kind of report. Setting a default value for this probably will over ride our idea on ticket closure (we check if empty from sysconfig).

I already have a view (in the database) from where I can query the data.

Code: Select all

SELECT TN FROM otrs_dashboards WHERE (ENVIRONMENT IS NULL OR CATEGORY IS NULL OR OS IS NULL OR PROD_VERSION IS NULL OR PRIMARY_COMPONENT IS NULL OR SUB_COMPONENT IS NULL OR TIER IS NULL OR TYPE LIKE 'Unclassified' OR TASK IS NULL OR SERVICE_REQUEST IS NULL) AND (QUEUE NOT LIKE 'INTERNAL') AND RT_TICKET IS NULL AND (EXTRACT (month from create_time) = EXTRACT (MONTH FROM SYSDATE)) AND (STATUS NOT LIKE 'Opened by Error' AND STATUS NOT LIKE 'Merged' AND STATUS NOT LIKE 'Auto-Hard Closed' AND STATUS NOT LIKE 'Closed Successful' AND STATUS NOT LIKE 'Pending Auto Close'
​Roberto A. Valdomir
Mobile: (+598) 93 868 147 | Skype: roberto.a.valdomir
@roberto.valdomir | re.vu/robertovaldomir

----------------------------------------------------------------------------------------
OTRS 3.3.8 | RHEL 6 x64 | Oracle 11g | Custom Development by OTRS365

Post Reply