Management Class/Domain Name for all Nodes

[skyj]

Hello Gurus,

I already search for the SQL query that can fetch me management class/domain name/agent version/type like archive/backup etc. all other details associated with each node that I can use to prepare one spreadsheet having details of all nodes created on TSM server.

Is someone having that type of query that will be really gr8.

Thanks in advance.


Skyj

 

[JohanW]

select nodes.*, bu_copygroups.*
from nodes, bu_copygroups
where nodes.domain_name = bu_copygroups.domain_name
and bu_copygroups.set_name='active'

Backup and archive copygroups are held in different tables, so it's unwieldy (though entirely doable) to have results for backups and archives in one query.
(change "bu_copygroups" to "ar_copygroups" to get results for archives.)

Frankly, I'm not sure you're asking for what you want.

 

[skyj]

Thanks JohanW.

I just saw your reply but that query didn't work. I agree with you that backup and archive copy groups are in different tables. I am not good in writing a SQL query and also not much aware abt the SQL tables used in TSM.

I am looking for query/queries that can help me in document the nodes backup/archive management class, domains each node associated, TSM/TDP agent version running on each node.

I can get lot of information from TSM reporting tool also but still the lot of information is somehow not available from the reporting tool.

Thanks in advance.
Skyj

 

[JohanW]

It helps to spell 'active' in capitals. Sorry, I make that mistake a lot.

Your above posts says you don't want to select * but nodes.NODE_NAME, bu_copygroups.DOMAIN_NAME, bu_copygroups.CLASS_NAME, nodes.CLIENT_VERSION, nodes.CLIENT_RELEASE, nodes.CLIENT_LEVEL, nodes.CLIENT_SUBLEVEL from ...

To also include archive mgmt class/domain, you could rewrite the query for that as specified in my previous post and run it twice, or do something like "select nodes.node_name, bu_copygroups.domain_name, ar_copygroups.domain_name from nodes, bu_copygroups, ar_copygroups where nodes.node_name=bu_copygroups.node_name and nodes.node_name=ar_copygroups.node_name and bu_copygroups.set_name='ACTIVE' and ar_copygroups.set_name='ACTIVE'" but you would not be getting backup copygroup results for nodes that do not have valid archive copygroup settings as well, even if they do have valid backup copygroup settings.

(If you want an exact query, please provide an exact example of the output you would like to see.)

 

[aweiler]

I believe what the original person was looking to do is what I am trying to do as well. What I need is a way to create a report that would show the mgmt class and the nodes that use that particular mgmt class. I can't seem to find the correct tables to use to create this report in TSM 5.5. Any ideas on how to get this accomplished would be greatly appreciated.

 

[JohanW]

You don't want to select * but bu_copygroups.class_name, nodes.node_name from ...

 

[skyj]

JohanW you asked for specifc example..here is the specific example.

There are lot of nodes/management classes/schedules in TSM environment.

I need a query to check for each node, what management class it is using, what schedule it is attached to so that I can do a cleanup of lot of management classes/schedules created but not in use.

Thx much for your help...

 

[JohanW]

Q ASSOC will show you what schedules do not have associated nodes.

I am hesitant to write a query that shows what mgmt classes are not used by default by nodes, because they may be in use by explicit custom configurations. To catch that, you'd need to look at the BACKUPS table, but that one is *huge* and beyond practical querying for all but the smallest environments. (What is your database size?) Besides, you're not only looking at mgmt classes but also policy domains, policy sets, copy groups and whatnot. Frankly, not going there ..

 

[skyj]

DB size is 105 GB.

I am ready to take that pain to look at backups table as we are planning soon to migrate to TSM6 and I want it more cleaner rather than this current complex one.

Thx in advance...

 

[JohanW]

You'll need to export the entire (relevant part of the) database to a dedicated database server, because TSM will crash if you're going to run a query like the one you'd need against a 100 GB database.

The database table has the mgmt class of the files in it. From there, you can follow associations to policy domains, policy sets, copy groups and whatever.

If this is over your head, hire an expert. It's not a small job, depending on the number of mgmt classes, schedules etc..

For a job like that, you can pay me, I'm not volunteering to do that one here on the forum. I just don't have the time. As consultant go, I might not be expensive, but depending on where you are, travel costs might be excessive.