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.

Filespace on nodes

Discussion in 'Command Line Administrator Client' started by cwilloug, Jul 29, 2010.

  1. cwilloug

    cwilloug Senior Member

    Joined:
    Sep 13, 2006
    Messages:
    387
    Likes Received:
    11
    Occupation:
    Storage / Backup / VMware / HyperV admin
    Location:
    North Dakota
    Is there a way to query the filespace dates on all my nodes? I few months ago I started going through all the nodes, one at a time, to see if there were any old (2008, 2006 etc.. ) filespaces that could be deleted from my nodes to free up some space. Today I was deleting a node and found some filespaces from 2005 (I didn't make it down that far on my list).

    Thanks.
     
  2.  
  3. TonyB

    TonyB Senior Member

    Joined:
    Dec 10, 2009
    Messages:
    384
    Likes Received:
    9
    Location:
    Sydney, Australia
    Try this - its formatted as a script with a single parameter ($1) which is the number of days since a backup completed. Note that a partial backup of a filespace doesn't update this timestamp, so the data you get back isn't completely authoritative.

    Code:
    select -
         filespaces.node_name as "Node", -
         contact as "Team", -
         filespace_name as "Filespace", -
         filespace_type as "Type", -
         date(backup_end) as "Last Backup" -
    from -
        filespaces, nodes -
    where -
        backup_end<current_timestamp-$1 days and -
        filespaces.node_name = nodes.node_name -
    order by 2,1,5
    
    I've a v6 version if thats more useful:

    Code:
    select -
        char(n.node_name, 16) as "Node", -
        char(contact, 32) as "Team", -
        char(filespace_name, 16) as "Filespace Name", -
        char(filespace_type, 12) as "Type", -
        date(backup_end) as "Last Backup" -
    from -
        filespaces f -
    right outer join nodes n on f.node_name=n.node_name -
    where -
        (backup_end<current_timestamp-0 days  or backup_end is null) -
    order by -
        2,1,5
    hth,

    Tony
     
  4. cwilloug

    cwilloug Senior Member

    Joined:
    Sep 13, 2006
    Messages:
    387
    Likes Received:
    11
    Occupation:
    Storage / Backup / VMware / HyperV admin
    Location:
    North Dakota
    Thanks Tony.
     
  5. Fattire

    Fattire New Member

    Joined:
    Jun 5, 2006
    Messages:
    203
    Likes Received:
    0
    I tried the v5 code on my 5.5..0.3 server and come up with error, "unexpected SQL literal token - '5.'. when i ran it at the command line.
    also got this error when I ran it as a script:
    ANR1464E RUN: Command script FILESPACE, line 1, parameter 1 was not specified

    I'm not sql person so any help would be helpful. would love to get this query to work and add to my tool kit.
     
  6. TonyB

    TonyB Senior Member

    Joined:
    Dec 10, 2009
    Messages:
    384
    Likes Received:
    9
    Location:
    Sydney, Australia
    When you invoke the script you need to specify an argument (parameter), e.g.

    Code:
     run blah 7
     

Share This Page