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

[blankgap]

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:

Nodename	GB
Node1	30,02
Node2	72,84
Node3	946,87
VM-Datamover1	1698,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:

Date	VM where name like 'XYZ'	Data +/- in TB	Data +/- in %
2021-08-10	All "XYZ" VM's	+100 TB	+10 %
2021-08-11	All "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]

This is dirty but should get you close.

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:

   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]

This is dirty but should get you close.

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:

   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!

 

[RecoveryOne]

You are welcome!