Query for tickets created last month

RT Users
1

Hey

I’m currently trying to create a query that will show all tickets from queue X that were created during the previous month that have since been resolved.

What I have tried: Queue = ‘X’ AND Created > ‘last month’ AND Created < ‘this month’ AND Status = ‘resolved’
This did not give me any results. I believe the problem to be the “last month” part, since “this month” works well in other queries.

Another tactic I tried: Queue = ‘X’ AND Created > ‘-2 months’ AND Created < ‘-1 month’
This query works, but instead giving the tickets that were created last month it gives me tickets that were created 62-31 days ago. Right now I need the tickets only from June but this query shows tickets created before that.

Help?

With best regard,
Tiia Tikka

2

Hi Tiia

This may be of use: ManualSearchTickets - Request Tracker Wiki also the searching uses Time::ParseDate, so this may also help Time::ParseDate - date parsing both relative and absolute - metacpan.org

regards
Garry

3

I think the problem here is that Time::ParseDate can either work in absolute date/times, or date/times relative to now. I’m not sure there is a way of saying “1st June-30th June” when in an arbitrary day in July, as “last” and “ago” are relative to the day you’re on in July.

4

Hey

Yeah, so far I’ve been manually changing the days for these searches (with multiple different custom fields) each month, because I haven’t figured out if there is a way to build a query “1st of previous month 00:00 - 1st of this month 00:00”.

What is confusing me is that “this month” works as I want it to, and "Created > ‘this month’ " will show all tickets created after the 1st of this month 00:00, but there is no equivalent for a past tense? (That I know of)

5

Looking at the Time::ParseDate unfortunately it doesn’t appear there is.

6

Dear RT people, please add this feature and save me from a whole lot of unnecessary work.

7

I don’t think its in RT - its in the Time::ParseDate module that RT makes use of.

8

This niggled me… so I’ve dived in deeper. :slight_smile:

I started thinking I could write a callback on the “Initial” in Search/Results.html to process the query string to add extra function to do this. I started this with the string “prior month” but then noticed that if I used this in a query first, RT logged that it couldn’t parse the date by both Time::ParseDate and DateTime::Format::Natural. Digging into the latter, it appears there are a lot more options for strings than are shown the Time::ParseDate module docs, espectially if you look in DateTime::Format::Natural::Lang::EN documentation.

So try this query string and see if it does what you want:

Queue = 'X' AND Created >= '1st day last month' AND Created < 'this month' AND Status = 'resolved'

9

Wonderful that I’m not in this alone :smile:

It doesn’t appear to work. It gives this error message: Wrong query, expecting a AGGREGATOR in ‘Queue = ‘Helpdesk’ AND Created >= ‘1st >day<–here last month’ AND Created < ‘this month’ AND Status = ‘resolved’’

10

You might need to change the quotes if you’ve just cut-n-pasted from the forum (I did this morning when I cut-n-pasted your original query).

(and I’ve edit my post above to make it preformatted so smart quotes don’t appear to ruin everyone’s day!)

11

Doesn’t work. The “last month” always equals “31 days ago” instead of the concept of a calendar month that happened before the current calendar month. There for the equation doesn’t want to work as we want it to.

12

Try this:

        Created>='1st day last month' 
    AND Created<='1st day this month'
13

You are an absolute life saver! Thank you, this worked like a charm

14

No problem, glad I could help… :slight_smile:

That second filter might have to be:

    AND Created<'1st day this month'

I don’t know why I was using ‘<=’ but ‘<’ seems to work fine. I think it will only match the 1st day of the month at 00:00:00