RT Gurus,
I have yet to figure out how to construct medium complexity queries with a mix of and/or statements using the OOB Query builder. I’ve also tried overriding and enhancing the query under Advanced with zero success. From the posts I’ve read RT restricts users and admins from writing complex queries because malicious code could be written. While I understand that…it makes creating user centric and meaningful dashboards impossible. Is there an extension or work around that will allow admins to write and publish complex queries that are multiple levels deep and use both AND & OR statements throughout. I would also like to add custom filters to dashboards (such as this week, last week, this month, last month, etc.) and can’t figure out how to. Additionally I’d like to change the layout of a dashboard from just 2 columns to something more appealing and suited for the data I was to present? How do I bridge these gaps so my users aren’t disappointed in their dashboards?
I don’t believe there is an explicit limitation on how complicated your queries can get In the query builder. Is there a query that you’re trying to construct that isn’t working in the web UI?
RT 6 allows you to customize the amount of columns in a dashboard, right now in RT 5 you can change the number of columns (Bootstrap columns) for the body and sidebar components but not the actual number of columns in the dashboard.
We’re testing RT5. I urge you to try creating a query looking at tickets with a Staus= New OR Status= In Process OR Status= On Hold AND Queue= A OR Queue= B OR Queue= C. You can’t mix And OR statements together using the OOB query builder or override the query and save in the Advanced Tab. Past posts on the same topic highlighted the same problem with no solution. This one it old, but for reference: Query Builder documentation - RT Users - Request Tracker Community Forum.
Kindly point me in the direction of where I can control this for a dashboard-" change the number of columns (Bootstrap columns) for the body and sidebar components".
Thanks,
Julie
For your example query, in the advanced tab this seems to work for me:
( Status = 'New' OR Status = 'In Process' OR Status = 'On Hold' ) AND ( Queue = 'A' OR Queue = 'B' OR Queue = 'C' )
I see options to change the width when editing a dashboard:
I don’t write code, so my operators were off. Let me play around with this model a bit to see how far i get writing other queries. Is there a cheat sheet/ query bible for non-developers that includes commonly used/ helpful queries for RT 5?
Hi, try adding extra () in each comparison:
I had to do it like this in some queries or the editor drop the parenthesis when saving.
Hi, just to add to this advice. We had a lot of confusion about date parameters and what will work. Here are some examples that will work.
Created Last Month: ( Created > ‘1st day last month’ AND Created < ‘1st day this month’ )
Resolved Last Month: ( Resolved > ‘1st day last month’ AND Resolved < ‘1st day this month’ )
Month to Date: Created > ‘this month’ or Resolved > ‘this month’
If you want a search that covers tickets created in the last actual calendar week (i.e. not the last 7 days): ( Created = ‘last monday’ OR Created = ‘last tuesday’ OR Created = ‘last wednesday’ OR Created = ‘last thursday’ OR Created = ‘last friday’ OR Created = ‘last saturday’ OR Created = ‘last sunday’ )
For Resolved simply use Resolved.
Some other tips. On our Created searches we want to exclude all Rejected tickets so you can use Status != ‘rejected’ and on Resolved searches you may want to exclude tickets that have since been re-opened so you can simply add AND Status = ‘resolved’
NOTE: != means “not equal to” so you can use this for search operators to e.g. exclude tickets with a specific custom field.
Let’s say you want to find all tickets that have a DependsOn ticket you can simply use the search parameter: DependsOn IS NOT NULL
If you want the search to be for DependedOnBy tickets use DependedOnBy IS NOT NULL
Some other time parameters that work:
Created = ‘today’
Created = ‘yesterday’
Created > ‘7 days ago’
Created > ‘this month’
I hope this helps you.
I believe the library is Time::ParseDate Time::ParseDate - date parsing both relative and absolute - metacpan.org
Yes!!! That’s the link I was looking for earlier because it helped us tremendously. Thanks for the update!
Thanks for the heads up. I’ll keep this tip in mind as i try to write a few more.
This is very helpful Lloyd. Just to clarify are the terms Created and Resolved coming from your lifecycle? If we created custom lifecycles the query would need altered, right?
As I understand it, Lifecycles deal with Ticket status (eg ‘new’ ‘open’ ‘rejected’ ‘deleted’ ‘stalled’ ‘resolved’) and is customizable, whereas Created is a standard parameter used together with your desired date parameters to identify all tickets Logged/Created within the desired period. The same with Resolved. However, I am not an RT developer so please experiment on your Server and see what works for you.
That would make querying easier & faster when looking at those 2 buckets. Thank you for sharing your thoughts on this matter. I’ll run a few tests to verify it works as described.