The TSM Server usage poll closed Jan 1st and the results were interesting. See for yourself, but I thought the usage of 6.3 and 7.1 over 6.4 was interesting. I guess it's not worth going to 6.4 seeing as 7.1 is out???
Monday, January 12, 2015
SQL: CASE and CONCAT
SO I was trying to build a better report for TSM Client levels replacing the crappy windows OS level with the correct version using CASE but was worried that case with two fields being concatenated would work. Well it does and quite well. The only issue was that if the platform_name is longer than the varchar setting then you will receive a warning error at the end of the select (the select runs successfully but will truncate any results for platform_name which is easily fixed).
select case -
when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 5.00' then 'WinNT 2000' -
when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 5.02' then 'WinNT 2003' -
when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 6.00' then 'WinNT 2008' -
when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 6.01' then 'WinNT 2008 R2' -
when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 6.02' then 'WinNT 2012' -
when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 6.03' then 'WinNT 2012 R2' -
else varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) -
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 where LASTACC_TIME>(CURRENT_TIMESTAMP - 70 DAYS) and node_name like '%SU%' group by platform_name, client_os_level, client_version, client_release, client_level, client_sublevel
The results were exactly what I wanted.
PLATFORM_NAME TSM_VERSION COUNT
------------------ ----------- -----------
SUN SOLARIS 5.9 5.2.2.0 4
WinNT 2000 5.3.0.0 1
WinNT 2000 5.3.6.0 1
WinNT 2003 5.3.0.0 4
WinNT 2003 5.3.2.0 6
WinNT 2003 5.3.4.0 6
WinNT 2003 5.4.0.2 3
WinNT 2003 5.4.1.4 2
WinNT 2003 5.4.2.0 2
WinNT 2003 5.4.3.0 2
WinNT 2003 5.5.0.4 8
WinNT 2003 5.5.1.0 1
WinNT 2003 5.5.2.0 1
WinNT 2003 5.5.3.0 2
WinNT 2003 6.1.3.0 1
WinNT 2008 5.5.0.4 1
WinNT 2008 R2 6.1.4.0 3
WinNT 2008 R2 6.2.4.0 2
WinNT 2008 R2 6.3.0.0 2
WinNT 2012 6.4.1.0 1
select case -
when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 5.00' then 'WinNT 2000' -
when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 5.02' then 'WinNT 2003' -
when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 6.00' then 'WinNT 2008' -
when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 6.01' then 'WinNT 2008 R2' -
when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 6.02' then 'WinNT 2012' -
when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 6.03' then 'WinNT 2012 R2' -
else varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) -
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 where LASTACC_TIME>(CURRENT_TIMESTAMP - 70 DAYS) and node_name like '%SU%' group by platform_name, client_os_level, client_version, client_release, client_level, client_sublevel
The results were exactly what I wanted.
PLATFORM_NAME TSM_VERSION COUNT
------------------ ----------- -----------
SUN SOLARIS 5.9 5.2.2.0 4
WinNT 2000 5.3.0.0 1
WinNT 2000 5.3.6.0 1
WinNT 2003 5.3.0.0 4
WinNT 2003 5.3.2.0 6
WinNT 2003 5.3.4.0 6
WinNT 2003 5.4.0.2 3
WinNT 2003 5.4.1.4 2
WinNT 2003 5.4.2.0 2
WinNT 2003 5.4.3.0 2
WinNT 2003 5.5.0.4 8
WinNT 2003 5.5.1.0 1
WinNT 2003 5.5.2.0 1
WinNT 2003 5.5.3.0 2
WinNT 2003 6.1.3.0 1
WinNT 2008 5.5.0.4 1
WinNT 2008 R2 6.1.4.0 3
WinNT 2008 R2 6.2.4.0 2
WinNT 2008 R2 6.3.0.0 2
WinNT 2012 6.4.1.0 1
Subscribe to:
Posts (Atom)