• 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.

Query the daily difference (in TB and %) of backed up VM-Data

blankgap

ADSM.ORG Member
Joined
Jun 5, 2018
Messages
60
Reaction score
2
Points
0
Hi,

I am trying to find a way to query the daily difference of how much data is backed up on certain VMs every day (in percent and TB).
Lets say those VM's all have "XYZ" in their names.


I once made a sql query on the summary-table to query the amount of node-data backed up between the given dates X and Y.


The Script looks like this:
SELECT cast(entity as char(60)) as Nodename, CAST(FLOAT(SUM(bytes)) / 1024 / 1024 / 1024 AS DECIMAL(8,2)) as "GB" FROM summary WHERE ( activity='BACKUP' ) AND start_time>'$1 00:00:00' AND start_time<'$2 00:00:00' GROUP BY entity ORDER BY "GB"

and gives Output like this:


NodenameGB
Node130,02
Node272,84
Node3946,87
VM-Datamover11698,23
......

Unfortunately I can't query single VM's (filespaces) on the summary table, only datamovers (nodes)

Does anyone have an idea on how to get the data I need?

The result should look anywhere near this:

DateVM where name like 'XYZ'Data +/- in TBData +/- in %
2021-08-10All "XYZ" VM's+100 TB+10 %
2021-08-11All "XYZ" VM's-10 TB-1 %
............


And as mentioned, my biggest problem right now is that I am unable to query the summary of VM-names (filespaces) instead of datamovers (nodename)

Any ideas? The only thing I can think of is to collect all needed VM's under one datamover and then use my old Summary-Script the get the daily change in TB, then convert this to percent. But for multiple reasons this is absolutely not optimal.
But I'm a SQL beginner, so maybe I am missing something.

Any help would be appreciated.

Kind regards
 

RecoveryOne

ADSM.ORG Senior Member
Joined
Mar 15, 2017
Messages
323
Reaction score
74
Points
0
This is dirty but should get you close.

Code:
select TO_CHAR(CHAR(START_TIME),'YYYY-MM-DD HH24:MI:SS') as START_TIME,TO_CHAR(CHAR(END_TIME),'YYYY-MM-DD HH24:MI:SS') as END_TIME,SUB_ENTITY as NODE_NAME,SCHEDULE_NAME,SUCCESSFUL,TRANSLATE('a bc:de:fg', DIGITS(END_TIME-START_TIME), '_______abcdefgh_____',' ') as ELAPTIME,cast(float(bytes)/1024/1024/1024 as dec(8,2)) as GB from summary_extended where ACTIVITY_DETAILS='VMware' and ACTIVITY<>'EXPIRATION' and SUB_ENTITY<>'' and START_TIME>=current_timestamp-48 hours and sub_entity='<vm_name_here>'
Results:

Code:
   START_TIME: 2021-08-21 18:55:20
     END_TIME: 2021-08-21 18:59:30
    NODE_NAME: <vmname>
SCHEDULE_NAME: DM01_sched
   SUCCESSFUL: YES
     ELAPTIME: 0 00:04:10
           GB: 3.96

   START_TIME: 2021-08-22 19:01:42
     END_TIME: 2021-08-22 19:16:25
    NODE_NAME: <vmname>
SCHEDULE_NAME: DM01_sched
   SUCCESSFUL: YES
     ELAPTIME: 0 00:14:43
           GB: 38.99
Change current_timestamp for your hours/days as needed. Further modifications will need to be done to get percentages as you wanted. But I hope it gives you a starting point.

**Edit:
So summary_extended is where most of the goods are at.
I'm not that great at SQL either!
 

blankgap

ADSM.ORG Member
Joined
Jun 5, 2018
Messages
60
Reaction score
2
Points
0
This is dirty but should get you close.

Code:
select TO_CHAR(CHAR(START_TIME),'YYYY-MM-DD HH24:MI:SS') as START_TIME,TO_CHAR(CHAR(END_TIME),'YYYY-MM-DD HH24:MI:SS') as END_TIME,SUB_ENTITY as NODE_NAME,SCHEDULE_NAME,SUCCESSFUL,TRANSLATE('a bc:de:fg', DIGITS(END_TIME-START_TIME), '_______abcdefgh_____',' ') as ELAPTIME,cast(float(bytes)/1024/1024/1024 as dec(8,2)) as GB from summary_extended where ACTIVITY_DETAILS='VMware' and ACTIVITY<>'EXPIRATION' and SUB_ENTITY<>'' and START_TIME>=current_timestamp-48 hours and sub_entity='<vm_name_here>'
Results:

Code:
   START_TIME: 2021-08-21 18:55:20
     END_TIME: 2021-08-21 18:59:30
    NODE_NAME: <vmname>
SCHEDULE_NAME: DM01_sched
   SUCCESSFUL: YES
     ELAPTIME: 0 00:04:10
           GB: 3.96

   START_TIME: 2021-08-22 19:01:42
     END_TIME: 2021-08-22 19:16:25
    NODE_NAME: <vmname>
SCHEDULE_NAME: DM01_sched
   SUCCESSFUL: YES
     ELAPTIME: 0 00:14:43
           GB: 38.99
Change current_timestamp for your hours/days as needed. Further modifications will need to be done to get percentages as you wanted. But I hope it gives you a starting point.

**Edit:
So summary_extended is where most of the goods are at.
I'm not that great at SQL either!
That should be good enough, thanks a lot for your help!
 

Advertise at ADSM.ORG

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

DigitalOcean $100 Credit

Support ADSM.ORG and get DigitalOcean FREE credit. DigitalOcean currently offer a $100, 60-day Free Credit for new accounts. Sign-up here:

DigitalOcean Referral Badge

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: 20 18.7%
  • Keep using TSM for Spectrum Protect.

    Votes: 65 60.7%
  • Let's be formal and just say Spectrum Protect

    Votes: 13 12.1%
  • Other (please comement)

    Votes: 9 8.4%

Forum statistics

Threads
31,871
Messages
135,906
Members
21,786
Latest member
london
Top