SQL Help

Andrew21210

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

I'm trying to massage a select statement in order to get all Windows 2008 nodes that are running TSM client 5.5.x.x with specific node names. I'm starting with this as a base but I don't seem to be getting anywhere.


select case -
when varchar(platform_name,20) || ' ' || cast(client_os_level as char(20)) ='WinNT 6.00' then 'WinNT 2008' -
when varchar(platform_name,20) || ' ' || cast(client_os_level as char(20)) ='WinNT 6.01' then 'WinNT 2008 -
else varchar(platform_name,20) || ' ' || cast(client_os_level as char(20)) -
end -
AS platform_name, -
cast(client_version as char(1)) || '.' || cast(client_release as char(1)) || '.' || cast(client_level as char(1)) || '.' || cast(client_sublevel as char(1)) as TSM_Version, count(distinct tcp_name) AS COUNT from nodes group by platform_name, client_os_level, client_version, client_release, client_level, client_sublevel -
order by 1 DESC,2 DESC


Can anyone give some insight as to how to get a list of specific node names?
 
PREDATAR Control23

Although I'm working with TSM for about decade, I have no idea about some parts of your selects.
I would just point out that you don't even use node_name column anywhere in the select (possibly you are trying to get hostnames, rather than node names?).
I would personally get columns I'm interested in comma-delimited output, and play with it outside of TSM using gawk.
Also you probably know but just in case - tcp_name, platform name and client_os_level are is not necessarily those of system being backed up(most of time they are though)

I'm by no means trying to discourage you, but I don't particularly feel inclined to try to figure this out within TSM.

If I translate what you are trying to achieve (rather than looking on the ... creation of yours), I would achieve that using following (not croschecked against TSM, and assuming values in your select are correct).
select node_name,tcp_name,client_version, client_release, client_level, client_sublevel from nodes where platform_name='WinNT' and (client_os_level='6.00' or client_os_level='6.01') and client_version=5 and client_release=5
(not sure if client_version and client_release need to be ''-d)
 
PREDATAR Control23

Although I'm working with TSM for about decade, I have no idea about some parts of your selects.
I would just point out that you don't even use node_name column anywhere in the select (possibly you are trying to get hostnames, rather than node names?).
I would personally get columns I'm interested in comma-delimited output, and play with it outside of TSM using gawk.
Also you probably know but just in case - tcp_name, platform name and client_os_level are is not necessarily those of system being backed up(most of time they are though)

I'm by no means trying to discourage you, but I don't particularly feel inclined to try to figure this out within TSM.

If I translate what you are trying to achieve (rather than looking on the ... creation of yours), I would achieve that using following (not croschecked against TSM, and assuming values in your select are correct).
select node_name,tcp_name,client_version, client_release, client_level, client_sublevel from nodes where platform_name='WinNT' and (client_os_level='6.00' or client_os_level='6.01') and client_version=5 and client_release=5
(not sure if client_version and client_release need to be ''-d)


Thanks for the response. If you actually run the full select (attached), it gives you a useful overview of all your OS's and levels as well as TSM client levels and a count of how many of each you are backing up. You can easily redirect this output to a .txt file for further massaging in Excel if you want. What I would like to do is get a list of specific nodes. For example, if I want to see all Windows 2008 nodes with a TSM client level of 5.5.x.x with specific node names, is there I way to modify this select to do that or would I be better off writing a select from scratch. FYI: This is not my creation, it comes from another TSM Admin.
 

Attachments

  • Platform_TSM_version.txt
    1.1 KB · Views: 13
PREDATAR Control23

Hi Andrew,

hope below query will give you the desired output.

SELECT node_name, platform_name, domain_name, VARCHAR(client_version)||'.'||VARCHAR(client_release)||'.'||VARCHAR(client_level)||'-'||VARCHAR(client_sublevel) as "TSM Client Version", DATE(lastacc_time) AS LASTACC_TIME FROM nodes where CLIENT_OS_NAME like '%Windows Server 2008%' and CLIENT_VERSION='5' and client_release='5'
 
PREDATAR Control23

Hi Andrew,

hope below query will give you the desired output.

SELECT node_name, platform_name, domain_name, VARCHAR(client_version)||'.'||VARCHAR(client_release)||'.'||VARCHAR(client_level)||'-'||VARCHAR(client_sublevel) as "TSM Client Version", DATE(lastacc_time) AS LASTACC_TIME FROM nodes where CLIENT_OS_NAME like '%Windows Server 2008%' and CLIENT_VERSION='5' and client_release='5'

Thanks. I get ANR2034E SELECT: No match found using this criteria.
 
PREDATAR Control23

i don't have any 5.5.X client in my environment hence couldn't test it but am getting result for 6.3.x clients with the same query.

SELECT node_name, platform_name, domain_name, VARCHAR(client_version)||'.'||VARCHAR(client_release)||'.'||VARCHAR(client_level)||'-'||VARCHAR(client_sublevel) as "TSM Client Version", DATE(lastacc_time) AS LASTACC_TIME FROM nodes where CLIENT_OS_NAME like '%Windows Server 2008%' and CLIENT_VERSION='6' and client_release='3'

NODE_NAME PLATFORM_NAME DOMAIN_NAME TSM Client Version LASTACC_TIME
ENTZEMS11A WinNT W2K 6.3.1-0 9/12/2016
ENTZEMS11B WinNT W2K 6.3.1-0 9/12/2016

Possible cause i am suspecting is 5.5.X clients doesn't reports back the client_os_name to TSM server.
 
PREDATAR Control23

Possible cause i am suspecting is 5.5.X clients doesn't reports back the client_os_name to TSM server.
Easy to validate, could try:
Code:
SELECT node_name, platform_name, client_os_name, domain_name, VARCHAR(client_version)||'.'||VARCHAR(client_release)||'.'||VARCHAR(client_level)||'-'||VARCHAR(client_sublevel) as "TSM Client Version", DATE(lastacc_time) AS LASTACC_TIME FROM nodes where platform_name='WinNT' and CLIENT_VERSION='5' and client_release='5'
 
PREDATAR Control23

Easy to validate, could try:
Code:
SELECT node_name, platform_name, client_os_name, domain_name, VARCHAR(client_version)||'.'||VARCHAR(client_release)||'.'||VARCHAR(client_level)||'-'||VARCHAR(client_sublevel) as "TSM Client Version", DATE(lastacc_time) AS LASTACC_TIME FROM nodes where platform_name='WinNT' and CLIENT_VERSION='5' and client_release='5'


This will work but again he'll not get the result he is looking for which is 2008 windows with 5.5X clients.
 
PREDATAR Control23

This will work but again he'll not get the result he is looking for which is 2008 windows with 5.5X clients.
Agreed, but he will see of Client_os_name is null or not.
 
Top