• Please help support our sponsors by considering their products and services.
    Our sponsors enable us to serve you with this high-speed Internet connection and fast webservers you are currently using at ADSM.ORG.
    They support this free flow of information and knowledge exchange service at no cost to you.

    Please welcome our latest sponsor Tectrade . We can show our appreciation by learning more about Tectrade Solutions
  • Community Tip: Please Give Thanks to Those Sharing Their Knowledge.

    If you receive helpful answer on this forum, please show thanks to the poster by clicking "LIKE" link for the answer that you found helpful.

  • Community Tip: Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING)

    Click the link above 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 notice will disappear after you have made at least 3 posts.

SQL Command to see how many files backed up per node

itdrew

ADSM.ORG Senior Member
#1
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
 

moon-buddy

ADSM.ORG Moderator
#2
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')
 

itdrew

ADSM.ORG Senior Member
#3
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!
 

moon-buddy

ADSM.ORG Moderator
#7
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')
 

marclant

ADSM.ORG Moderator
#8
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
 

itdrew

ADSM.ORG Senior Member
#9
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?
 

moon-buddy

ADSM.ORG Moderator
#10
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
 

itdrew

ADSM.ORG Senior Member
#11
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?
 

marclant

ADSM.ORG Moderator
#14
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'
 

Advertise at ADSM.ORG

If you are reading this, so are your potential customer. Advertise at ADSM.ORG right now.

UpCloud high performance VPS at $5/month

Get started with $25 in credits on Cloud Servers. You must use link below to receive the credit. Use the promo to get upto 5 month of FREE Linux VPS.

The Spectrum Protect TLA (Three-Letter Acronym): ISP or something else?

  • Every product needs a TLA, Let's call it ISP (IBM Spectrum Protect).

    Votes: 17 19.8%
  • Keep using TSM for Spectrum Protect.

    Votes: 52 60.5%
  • Let's be formal and just say Spectrum Protect

    Votes: 10 11.6%
  • Other (please comement)

    Votes: 7 8.1%

Forum statistics

Threads
31,453
Messages
134,012
Members
21,551
Latest member
JeffreyBro
Top