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.
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
},
);
ticket search using SQL+TicketSearch
-
- Znuny expert
- Posts: 155
- Joined: 04 Aug 2014, 23:59
- Znuny Version: 3.3.8
- Real Name: Roberto A. Valdomir
ticket search using SQL+TicketSearch
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
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
-
- Znuny wizard
- Posts: 315
- Joined: 09 Jan 2007, 15:24
- Znuny Version: OTRS 5.0.x
- Real Name: Torsten
- Company: c.a.p.e. IT GmbH
- Location: Chemnitz
- Contact:
Re: ticket search using SQL+TicketSearch
Hi,
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.
regards, T.
...indeed a "NOT"- or "EMPTY"-TicketSearch option would be quite useful.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.
...cool, looks nice.rvaldomir wrote:
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.
...nope. Unless someone develops it. I'd prefer the NOT-Option in TicketSearch rather than some "wild" SQL-appendix.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 ?
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.
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.
-
- Znuny expert
- Posts: 155
- Joined: 04 Aug 2014, 23:59
- Znuny Version: 3.3.8
- Real Name: Roberto A. Valdomir
Re: ticket search using SQL+TicketSearch
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.
--------------------
I already have a view (in the database) from where I can query the data.
--------------------
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).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.
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
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