SQL Select Help

Andrew21210

ADSM.ORG Member
Joined
Apr 10, 2008
Messages
97
Reaction score
2
Points
0
PREDATAR Control23

I'm trying to perform an SQL select parsing the Contact field. Basically, I want only the nodes that have "xxxx_DB" in the contact field and in addition to that, I want to find out how much these nodes back up on a nightly basis (by node) I've tried a few things but I'm obviously not getting it. Anyone care to take a shot?
 
PREDATAR Control23

Try this:
Code:
SELECT sum.entity,CAST(FLOAT(SUM(bytes))/1024/1024/1024 AS DECIMAL(8,2)) as "GB" FROM summary sum,nodes node WHERE (sum.entity=node.node_name and node.contact like '%_DB%') and (activity='BACKUP' ) AND start_time >{'2016-05-01 18:00:00'} AND start_time <{'2016-05-02 17:59:59'} GROUP BY entity ORDER BY "GB"
Caveat:

You need to find a way to replace the dates for the detailed 14 days you need. A script will make this easier.
 
Last edited:
PREDATAR Control23

Try this:

SELECT sum.entity,CAST(FLOAT(SUM(bytes))/1024/1024/1024 AS DECIMAL(8,2)) as "GB" FROM summary sum,nodes node WHERE (sum.entity=node.node_name and node.contact like '%_DB%') and (activity='BACKUP' ) AND start_time >{'2016-05-01 18:00:00'} AND start_time <{'2016-05-02 17:59:59'} GROUP BY entity ORDER BY "GB"

Caveat:

You need to find a way to replace the dates for the detailed 14 days you need. A script will make this easier.


Wow. That's perfect. Many thanks! On a somewhat related note, I use MobaXTerm to connect to my TSM servers from my Windows workstation. One thing I'd like to know is whether or not I can redirect the output of a select or script to my workstation instead of to the TSM server. Not a deal breaker if it can't be done but it would be slightly more convenient for me.
 
PREDATAR Control23

If you want it locally, use Windows based access and scripting instead of SSH or TTY access to the TSM Servers.

SSH or TTY access executes remotely and the output stays on the TSM server.
 
PREDATAR Control23

Load the TSM BA Client on a workstation including the ADMIN tools (dsmadmc.exe)
Create an empty dsm.opt (this is a place holder that TSM looks for)
Create a script with contents like:

Code:
@echo off

set IDPA=/id=admin_id /pa=admin_password /noc /display=table

for /F "Delims=, Tokens=1,2" %%x in (date_list.txt) do (
dsmadmc /tcpserveraddress=<TSM_server> /tcpport=<port_of_TSM_server> %IDPA% "SELECT sum.entity,CAST(FLOAT(SUM(bytes))/1024/1024/1024 AS DECIMAL(8,2)) as "GB" FROM summary sum,nodes node WHERE (sum.entity=node.node_name and node.contact like '%_DB%') and (activity='BACKUP' ) AND start_time>{'%%x 18:00:00'} AND start_time<{'%%y 17:59:59'} GROUP BY entity" >> c:\path\output.txt
)

date_list.txt will contain:

2016-05-01,2016-05-02
2016-05-02,2016-05-03
2016-05-03,2016-05-04
2016-05-04,2016-05-05
2016-05-05,2016-05-06

Run the DOS script in C:\Program Files\Tivoli\TSM\baclient or where you installed dsmadmc.exe
 
Last edited:
PREDATAR Control23

Hello,
I could really use help creating a select statement. My goal is to create a report for billing. I would like each "contact" and the total space in GB they are using for all of their nodes.
 
PREDATAR Control23

Expand on this:

Code:
select occ.node_name,node.contact,cast(float(sum(logical_mb))/1024 as dec(8,2)) as GB from occupancy occ, nodes node where occ.node_name=node.node_name group by occ.node_name,node.contact order by GB desc

PS:

When posting, please start a new one. Do not append to another post of a different topic.
 
Top