Event script by 'Status' ?

[ILCattivo]

Hi scripting guru's..

I'm looking for a TSM script that can pull out the backup/archive events from the previous 24 hrs, but only display/list those with a status of 'Complete', 'Missed' or 'Failed'.

Is this at all possible to get a handle on, as I don't really want all the 'Future' events displayed?

Many thanks

 

[marclant]

Something like this: http://thobias.org/tsm/sql/#toc144 ?

 

[ILCattivo]

Very close...
I did actually see that one online and attempted to modify it myself with my needs, but unfortunately failed miserably! ha ha

I believe that particular statement will display ALL types of event Status for a particular schedule name, where as I would like to generate a list of ALL events, as you would if you did a '>q eve * * begind=-1 & -0' omitting those events with a status of 'Future' from the list?

Thanks marclant

 

[marclant]

If you have a scheduled_date in the past, it should not include future events. Example:

select * from events where node_name is not null and scheduled_start<=current_timestamp-24 hours

You could add " status<'Future' " to your where clause if the above returns Future.

 

[ILCattivo]

Superb stuff marclant..

Slight revision to yours as it seemed to go back a whole month.

So the end product is :-

select * from events where node_name is not null and status<'Future' and scheduled_start >= '2016-12-12' and scheduled_start>current_timestamp-24 hours

Thanks once again for your help.

 

[marclant]

I had a typo, I had <= instead of >= for the scheduled_start, which gave all events older than 24 hours, instead of events from the last 24 hours.

You have a typo, should be: status<>'Future'

And you can do without "scheduled_start >= '2016-12-12'" now that the >= is fixed. And if you have status<>'Future' as it will not include Future events, only previous ones. Plus, you don't really want a hard-coded date in a query you will use frequently.

select * from events where node_name is not null and status<>'Future' and scheduled_start>=current_timestamp-24 hours

 

[ILCattivo]

Event better one here fella, obviously playing around with the dates and -hours...

select node_name, status, scheduled_start from events where scheduled_start >= '2016-12-12' and (status like 'Missed' or status like 'Failed' or status like 'Completed' or status like 'In Progress') and scheduled_start>current_timestamp-48 hours

That's exactly the one I'm after to pipe into a daily report email.

 

[marclant]

select node_name, status, scheduled_start from events where scheduled_start >= '2016-12-12' and (status like 'Missed' or status like 'Failed' or status like 'Completed' or status like 'In Progress') and scheduled_start>current_timestamp-48 hours

 

[RobinIvens]

Is there a way to incorporate a count of each Failed / Missed / Completed into the same select statement?

 

[marclant]

Is there a way to incorporate a count of each Failed / Missed / Completed into the same select statement?

select schedule_name,status,count(*) from events where (status like 'Missed' or status like 'Failed' or status like 'Completed' or status like 'In Progress') and scheduled_start>current_timestamp-48 hours and node_name is not null group by schedule_name,status order by schedule_name,status

 

[RobinIvens]

select schedule_name,status,count(*) from events where (status like 'Missed' or status like 'Failed' or status like 'Completed' or status like 'In Progress') and scheduled_start>current_timestamp-48 hours and node_name is not null group by schedule_name,status order by schedule_name,status

Thanks for the quick response. I don't need all the extra detail just the 4 status complete / failed / missed / In progress and their counts. I had these 3 scripts but wasn't able to figure a way to merge them into 1 line. We have to provide a summary from last month.

select count(*) as "Completed" from EVENTS where STATUS='Completed' and node_name IS NOT NULL and (scheduled_start >=current_timestamp - 30 days)

select count(*) as "Failed" from EVENTS where STATUS='Failed' and node_name IS NOT NULL and (scheduled_start >=current_timestamp - 30 days)

select count(*) as "Missed" from EVENTS where STATUS='Missed' and node_name IS NOT NULL and (scheduled_start >=current_timestamp - 30 days)

 

[marclant]

Ok. Just use my SQL query, trim out the fields you are not interested in between "select" and "from". Adjust the time in my "where" clause. Leave "node_name is not null" unless you want both admin and client schedule results. And adjust the "group by" and the "order by" to only contain the status.

 

[RobinIvens]

Ok. Just use my SQL query, trim out the fields you are not interested in between "select" and "from". Adjust the time in my "where" clause. Leave "node_name is not null" unless you want both admin and client schedule results. And adjust the "group by" and the "order by" to only contain the status.

got it.. Fantastic !!! Thanks so much for your assistance.

select status,count(*) from events where (status like 'Missed' or status like 'Failed' or status like 'Completed' or status like 'In Progress') and scheduled_start>current_timestamp-48 hours and node_name is not null group by status
STATUS Unnamed[2]
-------------------------- ------------
Completed 663
Failed 1
Missed 5

 

[marclant]

Cool. You may want to change the time range to: (scheduled_start >=current_timestamp - 30 days).

You can also put a column header if you want:
select status,count(*) as "COLUMN NAME" from ...

 

[RobinIvens]

Cool. You may want to change the time range to: (scheduled_start >=current_timestamp - 30 days).

You can also put a column header if you want:
select status,count(*) as "COLUMN NAME" from ...

perfect. I had changed the timestamp to 30 days ... but wasn't aware I could change the column name.
thanks again.