1. timestamp(current_date - 1 day, '18:00:00') means yesterday at 6pm.
2. use 'SUM(bytes)' and 'GROUP BY entity' (see below)
3. Not with TSM. With another database you could use table aliases and
subqueries:
SELECT s.entity AS "Node Name",
(SELECT sum(bytes)/1048576
FROM tsm_summary
WHERE start_time>'10/17/2001 18:00'
AND entity = s.entity
AND activity = 'ARCHIVE') AS "Archive MB",
(SELECT sum(bytes)/1048576
FROM tsm_summary
WHERE start_time>'10/17/2001 18:00'
AND entity = s.entity
AND activity = 'BACKUP') AS "Backup MB"
FROM tsm_summary s
WHERE s.start_time>'10/17/2001 18:00'
AND s.activity in ('ARCHIVE','BACKUP')
The best you can do with TSM is
SELECT entity AS "Node Name", -
activity AS "Activity", -
COUNT(*) AS "Sessions", -
SUM(bytes)/1048576 AS "MB Transferred" -
FROM summary -
WHERE activity IN ('BACKUP','ARCHIVE') -
AND start_time>TIMESTAMP(CURRENT_DATE - 1 DAY, '18:00:00') -
AND bytes>0 -
GROUP BY entity,activity -
ORDER BY 1
You can take this into Excel and use a pivot table to get the table you
want. If you're going to use a pivot table, you just need:
SELECT entity AS "Node Name", -
activity AS "Activity", -
bytes/1048576 AS "MB Transferred" -
FROM summary -
WHERE activity IN ('BACKUP','ARCHIVE') -
AND start_time>TIMESTAMP(CURRENT_DATE - 1 DAY, '18:00:00') -
AND bytes>0 -
ORDER BY 1
Since the pivot table will do the sum for you.
Alternatively, there are probably ways to use SELECTs to generate a list
of SELECTs to get the data you want, but it would be messy.
Scotty
--
Scotty Logan <swl AT stanford DOT edu>
Scotty Logan <swl AT stanford DOT edu>
ITSS-CSS http://www.stanford.edu/group/itss/css/
> -----Original Message-----
> From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU]On
> Behalf Of
> George Lesho
> Sent: Tuesday, October 30, 2001 09:54
> To: ADSM-L AT VM.MARIST DOT EDU
> Subject: Re: script: transferred bytes per node/day ????
>
>
> THREE PART QUESTION for "select gurus" ;-)
>
> 1. Can someone tell me what the timespan for this summary is
> based on the:
> start_time>timestamp(current date - 1 day,'18:00:00')
>
> Does this mean for the last 18 hours?
>
>
> 2. Also, is there a way to sum the various sessions for a
> single node? Some
> of my servers, which have Informix databases, back up logical
> logs all day
> long and there are dozens of entries per server such as:
>
> AFCTEST 10252976
> AFCTEST 10252976
> AFCTEST 10252976
> AFCTEST 10252976
> AFCTEST 10252976
> AFCTEST 10252976
>
> (((plus a zillion more of these entries)))
>
> 3. and is there a way to place BACKUPS and ARCHIVES into this
> select statement and have
> separate columns for both backups and archives associated
> with a node on a row? Such as:
>
> NODE_NAME BYTES_XFRD(BACKUP) BYTES_XFRD(ARCHIVE)
> ------------------ -------------------- --------------------
> AFCPROD1 1234567890 1234567890
>
>
> Thanks -
>
> George Lesho
> Storage/System Admin
> AFC Enterprises
>
>
>
>
>
>
> Joe Cascanette <Joe.Cascanette AT CUMIS DOT COM>@VM.MARIST.EDU> on 10/30/2001
> 08:28:27 AM
>
> Please respond to "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
>
> Sent by: "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
>
>
> To: ADSM-L AT VM.MARIST DOT EDU
> cc: (bcc: George Lesho/Partners/AFC)
> Fax to:
> Subject: Re: script: transferred bytes per node/day ????
>
>
> select ENTITY as Node_Name, BYTES as Bytes_Transferred from
> summary where
> activity='BACKUP' and start_time>timestamp(current date - 1 day,
> '18:00:00')and bytes>0
>
>
> This was posted by another user, just added some fields.
>
> Joe Cascanette
> The Cumis Group Limited
>
> -----Original Message-----
> From: Winfried Heilmann [mailto:winfried.heilmann AT INFRACOR DOT DE]
> Sent: Tuesday, October 30, 2001 8:50 AM
> To: ADSM-L AT VM.MARIST DOT EDU
> Subject: script: transferred bytes per node/day ????
>
>
> Hi
>
> I'm looking for a script to calculate the transferred bytes
> for each node
> per node and a total for the day.
>
> Can anybody help me?
>
> Regards
>
> Winfried
>
|
