[rt-users] Query for Service downtime

[jul]

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 ?)
http://search.cpan.org/~drolsky/DateTime-0.47/lib/DateTime.pm#Datetime_Subtraction
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:"