Query for Service downtime

RT Users
1

Hi,

  I am trying to generate a report for fetching the instance name,

the related services and what is the downtime for each one of them.

  I have figured out something like this:

  

  select t.id, t.queue, t.owner, substr(t.subject,1,20) subject,

t.timeworked, t.status, t.timeleft, t.told, t.started, t.resolved,
t.lastupdatedby, t.creator, t.created from tickets t, queues q where
t.status=‘resolved’ and t.subject like upper(’%down%’) and
q.name=’&client_name’ and q.id=t.queue;

    But it's not solving my purpose fully. I am getting the date on

which the services went down and the date on which it got resolved. All
in all I am not getting the respective Downtime for each of the
services. I had a plan of fetching the difference in time of when the
tickets were opened and when the tickets were resolved. That would give
me the downtime but I have no idea how to implement that. Any help on
this regard will greatly appreciated.

Regards,

Uday

DISCLAIMER:“The information contained in this message and the attachments (if any) may be privileged and confidential and protected from disclosure. You are hereby notified that any unauthorized use, dissemination, distribution or copying of this communication, review, retransmission, or taking of any action based upon this information, by persons or entities other than the intended recipient, is strictly prohibited. If you are not the intended recipient or an employee or agent responsible for delivering this message, and have received this communication in error, please notify us immediately by replying to the message and kindly delete the original message, attachments, if any, and all its copies from your computer system. Thank you for your cooperation.”

2

Hello
Uday Dey a �crit :

Hi,

  I am trying to generate a report for fetching the instance name,

the related services and what is the downtime for each one of them.

  I have figured out something like this:

  select t.id, t.queue, t.owner, substr(t.subject,1,20) subject,

t.timeworked, t.status, t.timeleft, t.told, t.started, t.resolved,
t.lastupdatedby, t.creator, t.created from tickets t, queues q where
t.status=‘resolved’ and t.subject like upper(‘%down%’) and
q.name=‘&client_name’ and q.id=t.queue;

    But it's not solving my purpose fully. I am getting the date on

which the services went down and the date on which it got resolved. All
in all I am not getting the respective Downtime for each of the
services. I had a plan of fetching the difference in time of when the
tickets were opened and when the tickets were resolved. That would give
me the downtime but I have no idea how to implement that. Any help on
this regard will greatly appreciated.

  1. add a custom field down-duration
    1’) find a sample of scrips playing with custom field
  2. on resolve compute time duration custom field.
    If needed include this perl module (isn’t it already available in scrips
    environment ?)
    DateTime - A date and time object - metacpan.org
  3. query on the customfield, and you’ll even be able to graph it.

Alternative solution:
Use nagios, and make him mail to RT with a link to the service status when
down.

Generaly I am quite uneasy with ticketing solutions used for monitoring
downtime services, and CRM used for tickting… I believe in the almighty
dogma that one application is best used for its initial purpose except
when dealing with physicists.

This should take between an half and a full day depending of your
knwoledge in perl and murphy’s law.

Julien Tayon // digital craftsman // making things simpler (when possible)

“The wages of sin are death; but after they’re done taking out taxes,
it’s just a tired feeling:”

3

services. I had a plan of fetching the difference in time of when the
tickets were opened and when the tickets were resolved. That would give
me the downtime but I have no idea how to implement that. Any help on
this regard will greatly appreciated.

That’s not really downtime, I wouldn’t think, unless the tickets are
both opened and resolved automatically. Are they?

If either is done by a human, then its not system downtime. It’s time
to whenever-the-human-felt-like-doing-RT-stuff.

Tom Lahti