Search problem

[James Igoe]

Hi,

I'm trying to create a specific search, but despite a fair amount of time spent on the advanced search syntax page, some Internet research, and playing with queries, I can't seem to get something to work appropriately.

I would normally not request support for what seems like a rather simple request, but I'm hoping a quick answer from you will save some time.

Long and short of it - I'm trying to create a search that will show me all tickets modified by a certain group of users between a certain set of dates. I seem to be limited to select a certain field that is changed - which I don't want. I need to be able to see all issues changed in some capacity (be it comment, status, assignee, etc.) by members of my team.

I seem to be missing something obvious - Can you help?

P.S. I also created this ticket through the JIRA support section, but am not 100% sure it went through - If so, please close that one as a duplicate.

Thanks

[James Igoe · Accepted solution]

Not do-able within the application. May be looked at for future releases. In the interim, we used the following directly against the DB to get what we wanted:

SELECT author, pkey, summary FROM jiraissue LEFT JOIN jiraaction ON jiraissue.id = jiraaction.issueid WHERE jiraaction.AUTHOR='user1' and jiraaction.UPDATED between '2013-01-21 00:00:00' and '2013-01-27 23:59:59' group by jiraaction.author, jiraissue.pkey, jiraissue.summary ORDER BY jiraaction.author ASC;

[James Igoe]

Not do-able within the application. May be looked at for future releases. In the interim, we used the following directly against the DB to get what we wanted:

SELECT author, pkey, summary FROM jiraissue LEFT JOIN jiraaction ON jiraissue.id = jiraaction.issueid WHERE jiraaction.AUTHOR='user1' and jiraaction.UPDATED between '2013-01-21 00:00:00' and '2013-01-27 23:59:59' group by jiraaction.author, jiraissue.pkey, jiraissue.summary ORDER BY jiraaction.author ASC;

[petry]

hey Rich,

As I suggest you through the ticket, maybe this can be an option for you?

assignee in (jigoe, rlebel, awhittom) AND updated <= 2013-02-01 AND updated >= 2013-01-28

You will see the issues updated that belong to those guys in the past days. Is that what you need?

Cheers,

[James Igoe]

Hi,

Unfortunately that would only give me a list of tickets in which the three users mentioned were the assignee - I need to see all updates regardless of assignee.

[petry]

Hey Rich,

As I suggested you through the ticket, maybe use the "Member Of" criteria would be an option for your case.

Hope that helps.

Cheers,

[James Igoe]

Hi!

Thanks for responding!

I have three team members on my team. I would like to search for any issue that we've touched in some way over a certain period of time. So, for example, come a Monday, I'd like to pull up a list of all tickets worked on the past seven days by jigoe, rlebel and awhittom, across all projects.

If I added those three people into a certain group, and then searched by that group, I would be able to pull up the requested data? The problem seems to be in the modifier - there doesn't seem to be an option that gets me an all-encompassing change - not just a change of status.

One guy came up with this query direct from the DB, which is pretty good, but I'd be surprised if we can't get something via the standard search through the application.

SELECT author, count(author) as comments, pkey, summary FROM jiraissue LEFT JOIN jiraaction ON jiraissue.id = jiraaction.issueid WHERE jiraaction.AUTHOR='rlebel' and jiraaction.UPDATED between '2013-01-21 00:00:00' and '2013-01-27 23:59:59' group by jiraaction.author, jiraissue.pkey, jiraissue.summary ORDER BY jiraaction.author ASC;

Thoughts?