Query Node without Filespace

[TaSMania]

I copy all nodes to different server and want to make sure all of them started backing up. I want to see which didn't. Another word empty file space so I can trouble shoot why. I don't think you could Q N w/o files space without sql command.
Could someone help me achieve this task?

bkp: select .......... something.

 

[moon-buddy]

select distinct node_name, physical_mb, logical_mb, filespace_id from occupancy order by node_name

 

[TaSMania]

thank you,
what's the different with this command I just got from marclant:

select (node_name) from nodes where node_name NOT IN (select distinct(node_name) from filespaces where nodes.node_name=filespaces.node_name)

which i'm not sure if it worked because I saw whole bunch of message when I tried to out to temp directory > /tmp/

 

[TaSMania]

select distinct node_name, physical_mb, logical_mb, filespace_id from occupancy order by node_name

That didn't work. I want only NODES that has NEVER backup or LAST BACKUP START/COMPLETE is empty.

 

[moon-buddy]

select distinct node_name, physical_mb, logical_mb, filespace_id from occupancy order by node_name

That didn't work. I want only NODES that has NEVER backup or LAST BACKUP START/COMPLETE is empty.

marclant has the qualifier for nodes that have NO backup, and should work.

With my select, you need to do more leg work

 

[marclant]

Try this, it's simpler.

select cast(name as char(30)),endtime from tsmgui_allcli_grid order by endtime desc

That will give you all nodes with the date of the last backup. If the endtime is empty, it never backed up.

Edit to add: Will only work if status monitoring is enabled.

 

[TaSMania]

This didn't work either. At least not what i'm looking.
Q N on one of listed, shown backup last night and there's NAME listed from the query that is not even listed as node or at least didn't show up when I Q N it.

Try this, it's simpler.

select cast(name as char(30)),endtime from tsmgui_allcli_grid order by endtime desc

 

[TaSMania]

Looking for something like this:

Q N * with either capacity=n/a or last backup start date/time=n/a, if this column is empty I know for sure it hasn't START backup.

 

[marclant]

This didn't work either. At least not what i'm looking.

You must not have status monitoring enabled and/or have an out of support version (or will be out of support on April 30).

Q N * with either capacity=n/a or last backup start date/time=n/a, if this column is empty I know for sure it hasn't START backup.

Query Node doesn't track either of those things.

My initial query works on my system. I have 60 nodes without a filespace, 11 with a filespace, and 71 in total.

Protect: SERVER1>select count(node_name) as "Node count" from nodes 

  Node count
------------
  71

Protect: SERVER1>select count(node_name) as "Nodes with filespace" from nodes where node_name IN (select distinct(node_name) from filespaces where nodes.node_name=filespaces.node_name)

Nodes with filespace
---------------------
  11

Protect: SERVER1>select count(node_name) as "Nodes without filespace" from nodes where node_name NOT IN (select distinct(node_name) from filespaces where nodes.node_name=filespaces.node_name)

Nodes without filespace
------------------------
  60

 

[marclant]

My initial query gives you the list of nodes that never started a backup as a filespaces gets created when they start backing up a filesystem for the first time.

This query will give you the list of nodes that never finished a backup or hasn't backed up in 7 days:

select cast(node_name as char(25)) as NODENAME,min(BACKUP_END) as "LAST COMPLETED BACKUP" from filespaces where backup_end is null or backup_end>timestamp(current_date)-(7)days group by node_name order by min(backup_end) desc

 

[TaSMania]

could my parameter cause my issue?

sudo dsmadmc -se=isp1 -comma -dataonly=yes

I will try the first query again without -comma and -dataonly

Edit: add-on: So how to check if monitoring you speak of is enable or not? nothing in Q STAT

 

[marclant]

could my parameter cause my issue?

Unrelated.

I will try the first query again without -comma and -dataonly

For testing purposes, try the queries in the admin command line interface first. If it works, then you can work your way up to a batch job.

So how to check if monitoring you speak of is enable or not? nothing in Q STAT

Remember, you need a current server version, not one that is EOS. And QUERY MONITORSETTINGS to see if it's on or not.

 

[TaSMania]

Not sure what i did wrong other than this time I removed -comma and -dataonly but I think I got what I needed.
I output to excel delimited worksheet and compare.

Not sure how I missed this in my 7.1 reference guide, but thanks.

QUERY MONITORSETTINGS
Session established with server ISP002: Linux/x86_64
Server Version 7, Release 1, Level 7.0
Server date/time: 04/28/2017 10:10:08 Last access: 04/28/2017 10:04:58


Monitor Status: On
Status Refresh Interval (Minutes): 10
Status Retention (Hours): 24
Monitor Message Alerts: On