take a look at this query. i'm not suere if it works
select hpd_help_desk.Incident_Number, DATEADD(s, dbo.HPD_Help_Desk.
Reported_Date-18000, '1970-01-01 00:00:00') as reported_date,dbo.
ENUM_FIELD_VALUES_LOOKUP('HPD:Help Desk','Status',dbo.HPD_Help_Desk.Status)
As Status, dbo.HPD_Help_Desk.First_Name, dbo.HPD_Help_Desk.Last_Name,DATEADD
(s, HPD_HelpDesk_AuditLogSystem.Audit_Date-18000,'1970-01-01
00:00:00') asreopened_date
from hpd_help_desk,HPD_HelpDesk_AuditLogSystem
where hpd_help_desk.Entry_ID=HPD_HelpDesk_AuditLogSystem.Original_Request_ID
and [LOG] like '%Status: In Progress%' and Fields_Changed like ';Status;Last
Resolved Date;'
you have to change the date conversion formula my time zone is -5 so thats
the reason for -1800
2013/5/29 Joanne Janssen <joanne.jansse...@gmail.com>
> Natalie,
>
> Thanks for the info. I thought that what you described might be the
> case, but on this contract, I am the reporting guru and (my Remedy
> privileges are extremely limited) so I'm not able to see hidden fields
> except by querying the database. Looks like I'll be digging through
> the audit log. At least I know there is no easy way around it.
>
> Thanks again!
>
> Joanne
>
> On 5/29/13, Stroud, Natalie K <nkst...@sandia.gov> wrote:
> > Joanne:
> >
> > Well, we are on ITSM 7.6.04 SP2, so hopefully this will get you close to
> an
> > answer.
> >
> > I don't know how familiar you are with the re-open feature in ITSM, but
> > while you can set the status back to something earlier if it is resolved,
> > that's not actually how BMC seems to define a reopened incident (as far
> as I
> > can tell). If the incident is Closed or Cancelled, you can use the
> re-open
> > incident feature if you have the functional role of Support Group Lead or
> > Support Group Manager for the ticket's Owner Group. This functional role
> > makes a Re-Open Incident option available under one of the navbars on the
> > left, and if you use it, what that will do is generate a new ticket with
> > some of the data from the original ticket and a Re-Opened From
> relationship
> > to the original, which will remain in either Closed or Cancelled status.
> It
> > will also add a date/time value to a field that in Analytics is called
> > Incident Reopened Date, though I can't tell offhand what field that maps
> to
> > on the incident form. And you can see that this makes reporting fairly
> > straightforward - just search this Incident Reopened Date field and look
> for
> > the Re-opened From or Re-opened By relationship in the appropriate
> > relationship table.
> >
> > If you just move the status from Resolved to something earlier, it
> doesn't
> > look to me like the Incident Reopened Date field gets populated.
> >
> > So, as far as reporting on reopened tickets using the definition of
> resolved
> > to any open status, I'm not seeing an easy way to do that. (Yeah, you can
> > troll through the audit log, but while that might be possible, I wouldn't
> > call it easy.)
> >
> > So unless you're on a different Service Pack and you want to do the
> research
> > to confirm that this behavior isn't different in your version, I'm afraid
> > there's not much good news. :(
> >
> > Good luck!
> >
> > Natalie Stroud
> > SAIC @ Sandia National Laboratories
> > ARS-ITSM Reporting Specialist
> > Albuquerque, NM USA
> > nkst...@sandia.gov
> > ITSM 7.6.04 SP2 - Windows 2003 - SQL Server 2008
> >
> >
> >
> > -----Original Message-----
> > From: Action Request System discussion list(ARSList)
> > [mailto:arslist@ARSLIST.ORG] On Behalf Of Joanne Janssen
> > Sent: Wednesday, May 29, 2013 10:30 AM
> > To: arslist@ARSLIST.ORG
> > Subject: Re: [EXTERNAL] Re-Open Date
> >
> > Sorry about that It is indeed ITSM and it is 7.6.04. It's been a while
> > since I needed to send a question in.
> >
> > On 5/29/13, Stroud, Natalie K <nkst...@sandia.gov> wrote:
> >> Joanne:
> >>
> >> I suggest that you tell us what application and version you are using.
> >> I'd tend to assume ITSM, but it could be a custom ticketing app, and
> >> sometimes behavior varies between versions.
> >>
> >> Thanks,
> >>
> >> Natalie Stroud
> >> SAIC @ Sandia National Laboratories
> >> ARS-ITSM Reporting Specialist
> >> Albuquerque, NM USA
> >> nkst...@sandia.gov
> >> ITSM 7.6.04 SP2 - Windows 2003 - SQL Server 2008
> >>
> >> -----Original Message-----
> >> From: Action Request System discussion list(ARSList)
> >> [mailto:arslist@ARSLIST.ORG] On Behalf Of Joanne Janssen
> >> Sent: Wednesday, May 29, 2013 10:14 AM
> >> To: arslist@ARSLIST.ORG
> >> Subject: [EXTERNAL] Re-Open Date
> >>
> >> I'm doing some reports on the number of incidents that we re-opened,
> >> where re-opening is defined as moving from Resolved to any open state
> >> and not from Closed to any other state. I've looked on the web, but
> >> can't tell and don't have access to the admin tool to check. Is
> >> Re-opened date populated when an incident moves from Closed to an Open
> >> state or did I get lucky and it is populated when an incident moves from
> >> Resolved to an Open state.
> >>
> >> Thanks in advance,
> >>
> >> Joanne
> >>
> >> ______________________________________________________________________
> >> _________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
> >> "Where the Answers Are, and have been for 20 years"
> >>
> >> ______________________________________________________________________
> >> _________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
> >> "Where the Answers Are, and have been for 20 years"
> >>
> >
> >
> _______________________________________________________________________________
> > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the
> Answers
> > Are, and have been for 20 years"
> >
> >
> _______________________________________________________________________________
> > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
> > "Where the Answers Are, and have been for 20 years"
> >
>
>
> _______________________________________________________________________________
> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
> "Where the Answers Are, and have been for 20 years"
>
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
"Where the Answers Are, and have been for 20 years"
