Rally Lookback: help fetching all history based on future state

Question

Probably a lookback newbie question, but how do I return all of the history for stories based on an attribute that gets set later in their history?

Specifically, I want to load all of the history for all stories/defects in my project that have an accepted date in the last two weeks.

The following query (below) doesn't work because it (of course) only returns those history records where accepted date matches the query. What I actually want is all of the history records for any defect/story that is eventually accepted after that date...

filters :
[
    {
        property: "_TypeHierarchy",
        value: { $nin: [ -51009, -51012, -51031, -51078 ] }
    },
    {
        property: "_ProjectHierarchy",
        value: this.getContext().getProject().ObjectID
    },
    {
        property: "AcceptedDate",
        value: { $gt: Ext.Date.format(twoWeeksBack, 'Y-m-d') }
    }
]

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

Thanks to Nick's help, I divided this into two queries. The first grabs the final history record for stories/defects with an accepted date. I accumulate the object ids from that list, then kick off the second query, which finds the entire history for each object returned from the first query.

Note that I'm caching some variables in the "window" scope - that's my lame workaround to the fact that I can't ever quite figure out the context of "this" when I need it...

window.projectId = this.getContext().getProject().ObjectID;

I also end up flushing window.objectIds (where I store the results from the first query) when I exec the query, so I don't accumulate results across reloads. I'm sure there's a better way to do this, but I struggle with scope in javascript.

filter for first query

filters : [ {
    property : "_TypeHierarchy",
    value : {
        $nin : [ -51009, -51012, -51031, -51078 ]
    }
}, {
    property : "_ProjectHierarchy",
    value : window.projectId
}, {
    property : "AcceptedDate",
    value : {
        $gt : Ext.Date.format(monthBack, 'Y-m-d')
    }
}, {
    property : "_ValidTo",
    value : {
        $gt : '3000-01-01'
    }
} ]

Filter for second query:

filters : [ {
    property : "_TypeHierarchy",
    value : {
        $nin : [ -51009, -51012, -51031, -51078 ]
    }
}, {
    property : "_ProjectHierarchy",
    value : window.projectId
}, {
    property : "ObjectID",
    value : {
        $in : window.objectIds
    }
}, {
    property : "c_Kanban",
    value : {
        $exists : true
    }
} ]

score 1 · Answer 2 · answered Feb 11 '14 at 03:59

Here's an alternative query that will return only the snapshots that represent transition into the Accepted state.

find:{
  _TypeHierarchy: { $in : [ -51038, -51006 ] },
  _ProjectHierarchy: 999999,
  ScheduleState: { $gte: "Accepted" },
  "_PreviousValues.ScheduleState": {$lt: "Accepted", $exists: true},
  AcceptedDate: { $gte: "2014-02-01TZ" }
}

A second query is still required if you need the full history of the stories/defects. This should at least give you a cleaner initial list. Also note that Project: 999999 limits to the given project, while _ProjectHierarchy finds stories/defects in the child projects, as well.

In case you are interested, the query is similar to scenario #5 in the Lookback API documentation at https://rally1.rallydev.com/analytics/doc/.

Thanks! I'm still getting the hang of using the _PreviousValues attribute. — tmain, Feb 12 '14 at 14:36

nickm · Accepted Answer · 2014-02-08T00:21:47.293

If I understand the question, you want to get stories that are currently accepted, but you want that the returned results include snapshots from the time when they were not accepted. Before you write code, you may test an equivalent query in the browser and see if the results look as expected.

Here is an example - you will have to change OIDs.

https://rally1.rallydev.com/analytics/v2.0/service/rally/workspace/12352608129/artifact/snapshot/query.js?find={"_ProjectHierarchy":12352608219,"_TypeHierarchy":"HierarchicalRequirement","ScheduleState":"Accepted",_ValidFrom:{$gte: "2013-11-01",$lt: "2014-01-01"}}},sort:[{"ObjectID": 1},{_ValidFrom: 1}]&fields=["Name","ScheduleState","PlanEstimate"]&hydrate=["ScheduleState"]

You are correct that a query like this: find={"AcceptedDate":{$gt:"2014-01-01T00:00:00.000Z"}}

will return one snapshot per story that satisfies it.

https://rally1.rallydev.com/analytics/v2.0/service/rally/workspace/12352608129/artifact/snapshot/query.js?find={"AcceptedDate":{$gt:"2014-01-01T00:00:00.000Z"}}&fields=true&start=0&pagesize=1000

but a query like this: find={"ObjectID":{$in:[16483705391,16437964257,14943067452]}}

will return the whole history of the 3 artifacts:

https://rally1.rallydev.com/analytics/v2.0/service/rally/workspace/12352608129/artifact/snapshot/query.js?find={"ObjectID":{$in:[16483705391,16437964257,14943067452]}}&fields=true&start=0&pagesize=1000

To illustrate, here are some numbers: the last query returns 17 results for me. I check each story's revision history, and the number of revisions per story are 5, 5, 7 respectively, sum of which is equal to the total result count returned by the query. On the other hand the number of stories that meet find={"AcceptedDate":{$gt:"2014-01-01T00:00:00.000Z"}} is 13. And the query based on the accepted date returns 13 results, one snapshot per story.

Thanks Nick. That still doesn't quite get me there. It only returns the specific history records that are in the Accepted state. I'm guessing there's no clean way to accomplish what I want. I basically need an old-school SQL clause like "where ObjectID in (select ObjectID from history where AcceptedDate > '2014-01-15'....) I don't really expect that to exist. I was hoping to avoid a bunch of subqueries where I first get the list of ObjectIDs that have the right accepted date, then load all of their history. — tmain, Feb 07 '14 at 22:16
And by the way, thanks for providing a URL example. Those are hard to come by for some reason. I searched the Rally documentation and couldn't find any examples. Now that I've seen one I can construct my own for debugging. — tmain, Feb 07 '14 at 22:28
You are most welcome. I modified the answer with two additional URL examples that illustrate what you already discovered. You are correct - to get full history I had to use OIDs in the query. When I use AcceptedDate it returns only one snapshot per artifact. — nickm, Feb 08 '14 at 00:20

Rally Lookback: help fetching all history based on future state

3 Answers3

filter for first query

Filter for second query: