SQL Command to see how many files backed up per node

itdrew

ADSM.ORG Senior Member
Joined
Mar 3, 2003
Messages
292
Reaction score
1
Points
0
Location
Philadelphia
Website
Visit site
PREDATAR Control23

Hello, Does anyone have a sql script handy that can list how many files backed up by node overnight? I am trying to identify those servers that pop up an enormous amount of files from time to time.

TSM Server 7.1.1.100

Much appreciated!

Drew
 
PREDATAR Control23

Just improve on this:

select distinct nodename as SERVER,message as FILES from actlog where message like 'ANE4954I%' and (date_time>'2015-08-20 08:00:00' and date_time<'2015-08-21 08:00:00')
 
PREDATAR Control23

Great start moon-buddy. Thanks! I wish there were numbers we could pull from the summary table or something like that but this is much better than nothing.

Thanks again!
 
PREDATAR Control23

Got it:

select entity as nodes,affected as files from summary where activity='BACKUP' and (start_time>'2015-08-15 08:00:00' and start_time<'2015-08-16 08:00:00')
 
PREDATAR Control23

In the event that there is more than one backup session for a given node in that period, I'd do this instead:

Code:
select entity as nodes,sum(affected) as files from summary where activity='BACKUP' and (start_time>'2015-08-15 08:00:00' and start_time<'2015-08-16 08:00:00') group by entity
 
PREDATAR Control23

Thanks again so much for your replies! One thing I noticed is that when I run the SQL marclant listed directly above, one of the results I get is NULL in the entity field. I am trying to figure out why there would be a NULL value in the entity (NODE) field. Here are three results from the above output:
NODE:

FILES: 114427
NODE: NODEF
FILES: 44312
NODE: NODEG
FILES: 81450
So I took a look at the summary table and did a select for entity, address and sure enough there are 19 entries where entity is null for multiple addresses. Because of sum(affected), the above select statement is summing items with entity=null, which would mean those items are not being listed properly with their respective hostnames.

Here is what I mean:
select ENTITY,ADDRESS from summary where activity='BACKUP' and ENTITY is NULL

ENTITY:

ADDRESS: nodea.blabla.com:1250

ENTITY:
ADDRESS:nodeb.blabla.com:3553
ENTITY:
ADDRESS: nodec.blabla.com:2350

So, the SQL works pretty well, but does not properly handle items with ENTITY=NULL.

What does ENTITY=NULL even mean and how does it get there? It doesn't seem to make any sense to me but I'd like to somehow get ENTITY=NULL entries accurately listed in the SQL too. Or maybe I can make a change to how the nodes are defined which would then populate the entity field as expected?
 
PREDATAR Control23

Do you want to remove the NULL entities?

If so, use this:

Code:
select entity as nodes,sum(affected) as files from summary where activity='BACKUP' and (start_time>'2015-08-15 08:00:00' and start_time<'2015-08-16 08:00:00') and entity not like 'NULL' group by entity
 
PREDATAR Control23

I'd rather somehow account for the NULL entities. Thinking about it, NULL entities don't really make sense on the summary table. Does anyone know how the NULL entities even get there?
 
PREDATAR Control23

Makes sense to have the entity NULL for server processes, but not for a client activity such as a backup.
 
PREDATAR Control23

Are they VM session? Can you lookup one of those sessions in the summary_extended table to see if there is more details?

select entity, number from summary where entity is NULL and activity='BACKUP'

select * from summary_extended where number='XX'
 
Top