count successful backup

[cartman]

Hi,

I am looking for a script to tell me how many client schedules completed. which table does the q event * * command pull its information from. I have found an events table but it does not conatain much.



I have also looked in the summary table , for jobs where type=backup ... however this table does not seem to contian failed jobs ..



Can anyone help

I need number of successful backup jobs, and total number of backup jobs (any state) withing the last 24 hours.



Thanks in advance , Cartman

 

[Murkin]

Hey,



The Events table is a dynamic table, try:

select * from events where scheduled_start >= '10/10/2003 00:00:00 AM'



Thus you can get information about past events

 

[cartman]

Yes I know that but I want to send the results to a mobile phone. So I need number of backup jobs (total) and number of successful jobs.



Thanks, Cartman

 

[cartman]

okay I have it counting now using this command ...but



select count (*) from events where scheduled_start >= '11/07/2003 06:00:00 PM' and status<>'Completed' and node_name<>''



Does anyone know how to make this substiture the current day - x hours . I have tried this (below) but cannot make it take off , say 6 hours from the current date.



select count (*) from events where scheduled_start >= (currnet_timestamp) and status<>'Completed' and node_name<>''





Thanks, Cartman.

 

[cartman]

okay here goes...



I have created this command which works fine, it counts all events with a node name in the last day that did not complete. When i run this command using the following syntax ,







dsmadmc /id=admin /password=password "select count (*) from events where days(current_timestamp)-days(scheduled_start)<1 and status<>'Completed' and node_name<>''" > e:\reports\sms.txt





here is the output to the text file



IBM Tivoli Storage Manager

Command Line Administrative Interface - Version 5, Release 2, Level 0.0

(c) Copyright by IBM Corporation and other(s) 1990, 2003. All Rights Reserved.



Session established with server GBNORTSMS01: Windows

Server Version 5, Release 2, Level 0.0

Server date/time: 11/11/2003 21:44:59 Last access: 11/11/2003 21:30:01



ANS8000I Server command: 'select count (*) from events where days(current_timestamp)-days(scheduled_start)<1 and status<>'Completed' and node_name<>'''



Unnamed[1]

-----------

0



ANS8002I Highest return code was 0.





is there any way to only report the :



Unnamed[1]

-----------

0

to a text file without all the other txt ?



Thanks in advance, Cartman

 

[jmiller]

Hello,



We had a similar situation a few months ago and the resolution was to wash the output through a batch file using DOS find commands. One of the first tricks is to use the "-outfile" switch when running the dsmadmc.exe command instead of the ">" (that gets rid of the "Session Established" paragraph). Here's the whole script:





----------



REM Change to baclient directory containing dsmadmc.exe

c:

cd c:\Program Files\Tivoli\TSM\baclient\



REM Build outfile containing command results

dsmadmc.exe -id=admin -password=password -outfile="c:\temp\tsm-out.txt" "select count (*) from events where days(current_timestamp)-days(scheduled_start)<1 and status<>'Completed' and node_name<>''"



REM Remove unwanted lines

cd c:\temp\

type "c:\temp\tsm-out.txt" | find /v "Tivoli Storage Manager" > "c:\temp\tsm-a.txt"

type "c:\temp\tsm-a.txt" | find /v "Command Line" > "c:\temp\tsm-b.txt"

type "c:\temp\tsm-b.txt" | find /v "Copyright" > "c:\temp\tsm-c.txt"

type "c:\temp\tsm-c.txt" | find /v "Server command:" > "c:\temp\tsm-d.txt"

type "c:\temp\tsm-d.txt" | find /v "eturn code" > "e:\reports\sms.txt"



REM Clean Up

del c:\temp\tsm-*.txt



----------





This will output the end results to "e:\reports\sms.txt". There are still a few leading carriage returns in the file, but all the other stuff is gone. Hopefully this works for you as well! Let me know if you have any questions.



Jordan

 

[cartman]

thanks for the reply. I will give it a try tomorrow and let you know how i get on.

Cartman

 

[pontus]

By default when the event table is generated it only contain information within todays date. Using (CURRENT_TIMESTAMP-SCHEDULED_START<'1 0:00:00') will get events from the last 24 hours within todays date (after midnight only). See the problem? To get past this you must specify the dates from which the event table should be generated from. None of the solutions below will do this dynamic. If you have a scheduled_start at 10 pm this will not be included when you run your script at 6 am next day. If you're workin within a script the best solution is to let the script calculate the dates for you. However if this is not possible you can get around the problem using the following workaround.



SELECT COUNT(*) AS COUNT FROM EVENTS WHERE LENGTH(DOMAIN_NAME) IS NOT NULL AND UPPER(STATUS)<>'COMPLETED' AND UPPER(STATUS)<>'STARTED' AND SCHEDULED_START BETWEEN '1970-01-01' AND '2199-12-31' AND CURRENT_TIMESTAMP-SCHEDULED_START<'1 00:00:00'

 

[bmcferon]

For more on "stripping" header information from an outfile , check out my previous post (and the great responses)



http://my.adsm.org/modules.php?op=modload&name=phpBB_14&file=index&action=viewtopic&topic=1306&5



<TABLE BORDER=0 ALIGN=CENTER WIDTH=85%><TR><TD><font class="pn-sub">Quote:</font><HR></TD></TR><TR><TD><FONT class="pn-sub"><BLOCKQUOTE>Hello,



One of the first tricks is to use the "-outfile" switch when running the dsmadmc.exe command instead of the ">" (that gets rid of the "Session Established" paragraph).



Jordan</BLOCKQUOTE></FONT></TD></TR><TR><TD><HR></TD></TR></TABLE>

 

[cartman]

- outfile switch does not work .... im running TSM 5.2.2 .. anyone else any ideas ?

 

[aroy]

TSM version 5.2.2 has an additional parameter available: -dataonly=yes

which removes the header info. It is documented in the newest Reference

Guide.



Art

 

[crowntsm]

Don't know if you solved this but try this select



select count(*) as "Successful Backups" from summary where current_date-date(start_time)='1' and activity='BACKUP' and successful='YES'

select count(*) as "Failed Backups" from summary where current_date-date(start_time)='1' and activity='BACKUP' and successful='NO'



KevvyH