Retrieve information from CSDB - Subclient information

  • 30 May 2022
  • 5 replies
  • 615 views

Userlevel 2
Badge +3

Important note: do not modify CSDB data and modules, just use READ operations only.

You can refer official documentation to explain part of CSDB information as CommCell Views, but no more information as of now. So it's slightly hard nowadays to generate your own reports and/or workflows directly refer to CSDB tables which contain all CommCell information.
This article provides simple examples to retrieve data by your own.

In the first place, you can run any SQL queries from Microsoft SQL Server Management Studio or DBeaver as you like (and recent Linix CommServe environment, still there must be some tricks to gain access, though).

To keep your activities safe you need to keep uncommitted read from any table as one of the following techniques:

-- place the following at the top of query
set transaction isolation level read uncommitted;
-- place with(nolock) statement in each table reference
select * from APP_Application with(nolock)

Most of you would like to get the list of subclients along with client information (you can obtain the same from CommCell Configuration reports but this provides slightly complicated and verbose information).
CSDB already have a view named CommCellSubClientConfig which would serve the purpose, but it's slightly complicated

To ontain the list, you need to refer the following 4 tables:

select * from APP_Application
select * from APP_Client
select * from APP_InstanceName
select * from APP_BackupSetName

First one, App_Application contains actual Subclient information, connecting to other 3 tabiles which has foreign keys to client, instance and backupset, each stored in straight-forward naming columns.

To combine these 4 tables, you can generate like the following query (this omits with(nolock)):

select
apc.name as 'Client Name'
,api.name as 'Instance Name'
,apb.name as 'BackupSet Name'
,apap.subclientName as 'Subclient Name'
from APP_Application apap
inner join app_client apc on apap.clientId = apc.id
inner join APP_InstanceName api on apap.instance = api.id
inner join APP_BackupSetName apb on apap.backupSet = apb.id

Additionally you can show the iDA Type defined in APP_iDAType table, combining this (and adding last modified date of subclient) as:

select
apc.name as 'Client Name'
,api.name as 'Instance Name'
,apb.name as 'BackupSet Name'
,apap.id
,apap.subclientName as 'Subclient Name'
,apit.name as 'iDA Type'
,DateAdd(second, apap.modified+3600*0, '1970-01-01') as 'Last Modified (in GMT)'
from APP_Application apap
inner join app_client apc on apap.clientId = apc.id
inner join APP_InstanceName api on apap.instance = api.id
inner join APP_BackupSetName apb on apap.backupSet = apb.id
inner join APP_iDAType apit on apap.appTypeId = apit.type
order by apap.id

Hope this information helps a bit,


5 replies

Badge +1

Is there a way to fetch OS and Application version details of the clients (ESX/VCenter/Oracle/SQL/MySql)

Userlevel 2
Badge +3

Just creating relevant query for a customer, version detail cannot be fetched properly (some you cannot see even from CommCell Console), but you can fetch OS information from installed iDA like the following sample:

select 
apc.name
,apc.id
,apc.simOperatingSystemId
,sop.Name
,sop.Type
,sop.SubType
,sop.Version
,sop.PlatformType
,ctcp.OSInfo
,ctcp.osName
from APP_Client apc
inner join simOperatingSystem sop on apc.simOperatingSystemId = sop.id and apc.simOperatingSystemId !=
left outer join CT_ClientProperties ctcp on apc.id = ctcp.clientid
order by apc.name

 

Badge +1

getting syntax err on that sql before the last left outer join …

 

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'outer'.

 

Think something maybe missing from this ‘!=’ … ?

 

apc.simOperatingSystemId != left outer join

Userlevel 2
Badge +3

Oops sorry, trailing zero was missing while paste, …
 

select 
apc.name
,apc.id
,apc.simOperatingSystemId
,sop.Name
,sop.Type
,sop.SubType
,sop.Version
,sop.PlatformType
,ctcp.OSInfo
,ctcp.osName
from APP_Client apc
inner join simOperatingSystem sop on apc.simOperatingSystemId = sop.id and apc.simOperatingSystemId !=0
left outer join CT_ClientProperties ctcp on apc.id = ctcp.clientid
order by apc.name

 

Userlevel 5
Badge +16

Excellent. Thank you.

Reply