Those are all good suggestions. The winner seems to be Jose's long SQL query idea, since it fits what data and systems Andrew has. All he has to do now is to come up with the query language!
Moving forward, it would be easy enough to create some workflow that fires on submit or modify if the Priority changes, that writes the Priority value and the associated record ID to a new table. You can also have a column in that table tell you what kind of change happened to the Priority - did it go up or down, was it from Medium to High, ect, moving the heavy lifting SQL to the front end, with the added advantage of knowing if the Priority was changed more than once in a particular ticket. Then your Analytics query would run a lot faster. I would do it this way because I haven't figured out how to run decent subqueries yet. Drew Soto Cano Air Base Honduras On Tue, Mar 20, 2012 at 1:25 AM, Ben Chernys <[email protected]> wrote: > ** > > Or do it all in a Meta-Update script J It would process the Text/Diary > field, extract the data required from the text and either accumulate it or > update another simpler table to report on. About an hour development time. > > > > SQL would work but I’d expect development time to be a lot longer – and > dependent on the db. > > > > To do the task, you need a history of the changes. In effect you’d try to > “normalise” a subset of the audit log. The audit does contain that history > - just in an inconvenient form. > > > > As an aside. I note that you have a different “downward” set than > “upward”. I would try to generalise this (of-course) but if you only needed > the set of numbers you gave, you could add the integer fields to the Help > Desk and do it in workflow. Of course to set older tickets you would still > need to process the audit log. > > > > Cheers > > Ben > > > > Ben Chernys > > Senior Software Architect > Software Tool House Inc. > > Canada / Deutschland > Mobile: +49 171 380 2329 GMT + 1 + [ DST ] > Email: Ben.Chernys _AT_ softwaretoolhouse.com > Web: www.softwaretoolhouse.com > > Check out Software Tool House's freebies section for > > an ITSM 7.6.04 forms and fields spreadsheet. > > Meta-Update, our premium ARS Data tool, lets you automate > your imports, migrations, in no time at all, without programming, > without staging forms, without merge workflow. > http://www.softwaretoolhouse.com/ > > > > From: Action Request System discussion list(ARSList) > [mailto:[email protected]] On Behalf Of Jose Huerta > Sent: March-20-12 08:06 > To: [email protected] > Subject: Re: BMC Analytics Report Question > > > > ** As I see, you have to options: > > > > 1.- Figure out an SQL sentence that provides this information, (You can use > the audit). This sentence will be a high CPU and Mem consuming, since the > only solution I see is to create a subquerys at the audit to see which log > recors are about priory and when this records mean a higher value or lower > value than the current value. Another option would be to get the first log > value, since it will contain the initial priority. Then you can create a > derived table at the universe designer anduse it. Every time you get a > report with some dimension of this table it will take long. > > > > 2.- Create a new field at HPD:Help Desk that reports the initial priority > value, populated at submit. So you can compare this value with the current > one to see if goes up or down. > > Regards, > > > > Jose Huerta > > http://theremedyforit.com/ > > > On Tue, Mar 20, 2012 at 05:15, andrew tokarz <[email protected]> > wrote: > > ** > > Curious if anyone has tried to create a report using BMC Analytics where > you track the amount of times the priority is upgraded or downgraded in an > incident. > > So for 25000 incidents how many times were the incidents upgraded from: > > -Low to Critical > -Low to Medium > -Medium to High > -High to Critical > > or downgraded from: > > -Critical to Low > -Critical to Medium > -Medium to Low > > Audit log tracks when a priority is changed but its not in a single field to > report on. Its in a text/diary field with 40 other fields. You would have > to somehow parse this out. > > > Is there a simple way to do this in Analytics? Or would it be best if you > create a supporting table that is updated to track priority changes when > they occur. > > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ > > > > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ > > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
