Relative data ticket search for monthly report?

Hi. I’ve found quite a few examples and documentation about using relative dates to search RT; however, for the life of me, I can’t figure out how to use relative dates to get a list of tickets that were created last month.

I would like to create and save a search for our secretary, so she can easily get a list of tickets in a specific queue that were created last month. Without using relative dates, getting the tickets in this queue that were created last month is not a problem. This works perfect.

Queue = ‘Root Cause Analysis Forms’ AND ( Created >= ‘07-01-2011’ AND Created < ‘08-01-2011’ )

Using relative dates, I tried these, and none work how I want.

This works, but the end date isn’t relative

Queue = ‘Root Cause Analysis Forms’ AND ( Created >= ‘first of last month’ AND Created < ‘08-01-2011’ )

Returns 0 results

Queue = ‘Root Cause Analysis Forms’ AND ( Created >= ‘first of last month’ AND Created < ‘first of this month’ )

Returns 0 results

Queue = ‘Root Cause Analysis Forms’ AND ( Created >= ‘first of last month’ AND Created < ‘first of the month’ )

Returns 0 results

Queue = ‘Root Cause Analysis Forms’ AND ( Created >= ‘first of last month’ AND Created < ‘this month’ )

#Returns 0 results
Queue = ‘Root Cause Analysis Forms’ AND ( Created >= ‘first of last month’ AND Created <= ‘end of last month’ )

Any help appreciated.

Thanks
John

Hi. I’ve found quite a few examples and documentation about using relative dates to search
RT; however, for the life of me, I can’t figure out how to use relative dates to get a list of
tickets that were created last month.
I would like to create and save a search for our secretary, so she can easily get a list of
tickets in a specific queue that were created last month. Without using relative dates,
getting the tickets in this queue that were created last month is not a problem. This works
perfect.

We use the CPAN module Time::ParseDate, so you can use whatever syntax
it supports.

-kevin

I know, and I looked at the docs, but I can’t figure out how to do what I
want. I figured someone on the list must have figured out a way to get
last months report easily, regardless of how long it is into the current
month.

After spending more time on this, it turns out Created >= ‘first of last
month’ doesn’t work either. It’s not documented to work, but it did
return the results I expected. I found out the only reason it did, was
because there are no tickets in the queue I queried older than a month
anyway. :slight_smile:

The problem is, I can’t figure out if it’s possible to combine a relative
date with other criterion. For instance, ‘last month’ returns exactly one
month ago, so since today is Aug 4th, it returns July 4th, although I need
July 1st @midnight.

Any ideas?

John Alberts
Cloud Optimization Engineer
Ex Libris (USA) Inc.
1350 E. Touhy Ave. Suite 200 East
Des Plaines, IL 60018
Phone: 1-219-979-6560

Follow Ex Libris on Twitter: @exlibrisgroup
http://twitter.com/ExLibrisGroupOn 8/4/11 9:42 AM, “Kevin Falcone” falcone@bestpractical.com wrote:

On Thu, Aug 04, 2011 at 01:29:53AM +0000, John Alberts wrote:

Hi. I’ve found quite a few examples and documentation about using
relative dates to search
RT; however, for the life of me, I can’t figure out how to use
relative dates to get a list of
tickets that were created last month.
I would like to create and save a search for our secretary, so she
can easily get a list of
tickets in a specific queue that were created last month. Without
using relative dates,
getting the tickets in this queue that were created last month is
not a problem. This works
perfect.

We use the CPAN module Time::ParseDate, so you can use whatever syntax
it supports.
Time::ParseDate - date parsing both relative and absolute - metacpan.org

-kevin

Queue = ‘Root Cause Analysis Forms’ AND ( Created >= ‘07-01-2011’
AND Created < ‘08-01-2011’
)
Using relative dates, I tried these, and none work how I want.

This works, but the end date isn’t relative

Queue = ‘Root Cause Analysis Forms’ AND ( Created >= ‘first of last
month’ AND Created <
‘08-01-2011’ )

Returns 0 results

Queue = ‘Root Cause Analysis Forms’ AND ( Created >= ‘first of last
month’ AND Created <
‘first of this month’ )

Returns 0 results

Queue = ‘Root Cause Analysis Forms’ AND ( Created >= ‘first of last
month’ AND Created <
‘first of the month’ )

Returns 0 results

Queue = ‘Root Cause Analysis Forms’ AND ( Created >= ‘first of last
month’ AND Created <
‘this month’ )
#Returns 0 results
Queue = ‘Root Cause Analysis Forms’ AND ( Created >= ‘first of last
month’ AND Created <=
‘end of last month’ )
Any help appreciated.
Thanks
John


2011 Training: http://bestpractical.com/services/training.html


2011 Training: http://bestpractical.com/services/training.html

The problem is, I can’t figure out if it’s possible to combine a relative
date with other criterion. For instance, ‘last month’ returns exactly one
month ago, so since today is Aug 4th, it returns July 4th, although I need
July 1st @midnight.

Any ideas?

Your easiest solution might be to create a local/ page which simply
hardcodes the search and passes it to the appropriate mason component
for display.

Thomas

I know, and I looked at the docs, but I can’t figure out how to do what I
want. I figured someone on the list must have figured out a way to get
last months report easily, regardless of how long it is into the current
month.

After spending more time on this, it turns out Created >= ‘first of last
month’ doesn’t work either. It’s not documented to work, but it did
return the results I expected. I found out the only reason it did, was
because there are no tickets in the queue I queried older than a month
anyway. :slight_smile:

The problem is, I can’t figure out if it’s possible to combine a relative
date with other criterion. For instance, ‘last month’ returns exactly one
month ago, so since today is Aug 4th, it returns July 4th, although I need
July 1st @midnight.

Any ideas?

Maybe we should send this as a feature request to the maintainer of the
Time::ParseDate module.

-Chris

John,

Maybe I missed something, but why don’t you simply put in the criteria
‘Create > 30 days ago’ and then set the query up in dashboards to run on the
first of every month?

Kenn
LBNLOn Thu, Aug 4, 2011 at 6:25 PM, Thomas Sibley trs@bestpractical.com wrote:

On 08/04/2011 05:28 PM, John Alberts wrote:

The problem is, I can’t figure out if it’s possible to combine a relative
date with other criterion. For instance, ‘last month’ returns exactly
one
month ago, so since today is Aug 4th, it returns July 4th, although I
need
July 1st @midnight.

Any ideas?

Your easiest solution might be to create a local/ page which simply
hardcodes the search and passes it to the appropriate mason component
for display.

Thomas


2011 Training: http://bestpractical.com/services/training.html

Since I found this thread when I was trying to do this recently, I thought I’d reply with how I managed to get it to work. As RT now supports both Time::ParseDate and DateTime::Format::Natural::Lang, we can use the later module to make this happen. The two key strings are:

  • 1st day last month
  • 1st day this month

Just use those in your search, and it’ll be ‘last month’.

2 Likes

Thank you very much, this works!
Surprising how hard it was to find an answer to this.