1. Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING) Click the link to access ADSM.ORG Acceptable Use Policy and forum rules which should be observed when using this website. Violators may be banned from this website. This message will disappear after you have made at least 12 posts. Thank you for your cooperation.

Time Taken for back up

Discussion in 'Scripting' started by treds, Feb 26, 2010.

  1. treds

    treds New Member

    Joined:
    Jul 26, 2007
    Messages:
    186
    Likes Received:
    0
    Code:
    Hi i have a nice script that gives me start and stop times for all my nodes on back up.But i would like to add the time taken in as well. Can any one please add to this script to give me the desired result.
    
    SELECT entity AS "Node name", CAST(sum(bytes/1024/1024/1024)
                               AS decimal(8,2)) AS "GB xfer", SUBSTR (CAST(min            
                               (start_time) AS char(29)),1,10) AS "start date", SUBSTR    
                               (CAST(min (start_time) AS char(29)),12,8) AS "start time", 
                               SUBSTR (CAST(max (end_time) AS char(29)),1,10) AS "end     
                               date", SUBSTR (CAST(max (end_time) AS char(29)),12,8) AS   
                               "end time"  FROM summary  WHERE activity='BACKUP' AND      
                               start_time> current_timestamp - 24 hours GROUP BY entity 
    
    thanks in advance
    Treds
     
  2.  
  3. ChrisRees

    ChrisRees Senior Member

    Joined:
    Jul 24, 2008
    Messages:
    537
    Likes Received:
    13
    Occupation:
    Storage Management Person
    Location:
    Birmingham, UK
    Hi

    Try adding this in somewhere

    substr(char(start_time-end_time,11),4,8) as Duration

    Cheers
     
  4. ChrisRees

    ChrisRees Senior Member

    Joined:
    Jul 24, 2008
    Messages:
    537
    Likes Received:
    13
    Occupation:
    Storage Management Person
    Location:
    Birmingham, UK
    grrr it shouldn't have put a smiley in there...
    that should be

    , number eight ) as Duration

    Cheers
     
  5. treds

    treds New Member

    Joined:
    Jul 26, 2007
    Messages:
    186
    Likes Received:
    0
    script

    Code:
    it fails
    
    tsm: TAG_TSM1>SELECT entity AS "Node name", CAST(sum(bytes/1024/1024/1024) AS decimal(8,2)) AS "GB xfer", SUBSTR (CAST(min (star
                  t_time) AS char(29)),1,10) AS "start date", SUBSTR (CAST(min (start_time) AS char(29)),12,8) AS "start time",  SUB
                  STR (CAST(max (end_time) AS char(29)),1,10) AS "end date", SUBSTR (CAST(max (end_time) AS char(29)),12,8) AS "end 
                  time"  FROM summary  WHERE activity='BACKUP' AND start_time> current_timestamp - 24 hours GROUP BY entity substr(c
                  har(start_time-end_time,11),4,8,) as Duration
    ANR2905E Unexpected SQL identifier token - 'SUBSTR'.
    
                                         |
             ............................V..................................
              - 24 hours GROUP BY entity substr(char(start_time-end_time,11)
     
  6. toxy13

    toxy13 Senior Member

    Joined:
    Sep 24, 2003
    Messages:
    739
    Likes Received:
    12
    Occupation:
    Administrator
    Location:
    Leipzig
    try:

    edit: typing errors ...
     
  7. ChrisRees

    ChrisRees Senior Member

    Joined:
    Jul 24, 2008
    Messages:
    537
    Likes Received:
    13
    Occupation:
    Storage Management Person
    Location:
    Birmingham, UK
    put

    start_time> current_timestamp - 24 hours GROUP BY entity substr(char(start_time-end_time,11),4,8 ) as Duration

    before the "FROM SUMMARY" part of the sql statement
     
  8. treds

    treds New Member

    Joined:
    Jul 26, 2007
    Messages:
    186
    Likes Received:
    0
    script

    Code:
    Sorry still getting error
    tsm: TAG_TSM1>SELECT entity AS "Node name", CAST(sum(bytes/1024/1024/1024) AS decimal(8,2)) AS "GB xfer", SUBSTR (CAST(min (start_time) AS char(29)),1,10) AS "start dat              e", SUBSTR (CAST(min (start_time) AS char(29)),12,8) AS "start time",  SUBSTR (CAST(max (end_time) AS char(29)),1,10) AS "end date", SUBSTR (CAST(max (end              _time) AS char(29)),12,8) AS "end time"  start_time> current_timestamp - 24 hours GROUP BY entity substr(char(start_time-end_time,11),4,8 ) as Duration FR              OM summary  WHERE activity='BACKUP' AND start_time> current_timestamp - 24 hours GROUP BY entity
    ANR2905E Unexpected SQL identifier token - 'START_TIME'.
    
                                       |
             ..........................V....................................
             (29)),12,8) AS "end time" start_time> current_timestamp - 24 ho
    
    Also this wont allow me to save in the scripts section on the TSM console getting this error
    Single and double quotation marks cannot be used in the same line of a scripted command. Use the continuation character (-) to split the command so that a line contains only single or double quotation marks.
    
    For example, split this line:
    
    select node_name as "Locked Node" from nodes where locked='YES'
    
    into these lines:
    
    select node_name as "Locked Node"-
    from nodes where locked='YES'
    
    Can you add this without going through console?
    
     
  9. toxy13

    toxy13 Senior Member

    Joined:
    Sep 24, 2003
    Messages:
    739
    Likes Received:
    12
    Occupation:
    Administrator
    Location:
    Leipzig
    copy & paste this
     
    admin likes this.
  10. treds

    treds New Member

    Joined:
    Jul 26, 2007
    Messages:
    186
    Likes Received:
    0
    script

    Code:
    Toxy
    thanks thats works great!! but i still cant save through the TSM console  get the error i posted how do i add to scripts manualy please
    Treds
     
  11. treds

    treds New Member

    Joined:
    Jul 26, 2007
    Messages:
    186
    Likes Received:
    0
    script

    thanks worked it out
     
  12. unpaired

    unpaired New Member

    Joined:
    Jun 17, 2010
    Messages:
    84
    Likes Received:
    1
    Hello ,

    The statement output is for one node. it is raely work.

    select entity AS \"Node name\",CAST(sum(bytes/1024/1024) AS decimal(8,2)) AS \" SIZE MB \",SUBSTR (CAST(min (start_time) AS char(29)),1,10) AS \"start date\", SUBSTR (CAST(min (start_time) AS char(29)),12,8) AS \"start time\", SUBSTR (CAST(max (end_time) AS char(29)),1,10) AS \"end date\", SUBSTR (CAST(max (end_time) AS char(29)),12,8) AS \"end time\" FROM summary WHERE (activity='BACKUP' AND start_time >= current_timestamp - 24 hours)and entity='NODENAMEXXX' group by entity
     

Share This Page