Skip to main content
Solved

SQL query to get the client, subclient, SP, retention

  • 30 December 2021
  • 8 replies
  • 1133 views

Forum|alt.badge.img+7

Hello team,

 

Customer runs a commcell like following.

 Client

  - cvltclnt

  Subclient

   - cvltclnt_file_backup

  Storage Policy (Disk)

   - SamsungDedupe  (retention 10days, Full)

   - SamsungDedupe_Inc  (retention 5days, Incremental)

 

Customer is using this option “Incremental Storage Policy” for incremental backup on Storage Policy.

 

And they want to get this configuration info using SQL query.

What table/view should customer do a query to get this information?

 

Client Subclient Storage Policy retention days Schedule Backup Level
cvltclnt cvltclnt_file_backup SamsungDedupe 10 FS_FULL Full
cvltclnt cvltclnt_file_backup SamsungDedupe 5 FS_INC Incremental

 

I’ve tried to find the identifier for “Incremental Storage Policy” option in the table/view on MSSQL but I couldn’t. If I have to join several tables/views to get the value then kindly let me know how to make a query.

 

customer wants to get the values for subclient, storage policy, retention and backup level based on a client using direct query to SQL.

 

Thanks a lot

Best answer by Hongmo Kim

@Mike Struening 

Grateful for your update on this.

I figured out and want to share where this value is saved. You can find this in “dbo.archGroup” table for Incremental Storage Policy and “dbo.ArchCopyToApp” for associaton related value.

View original
Did this answer your question?

8 replies

Onno van den Berg
Commvault Certified Expert
Forum|alt.badge.img+19
  • Commvault Certified Expert
  • 1178 replies
  • December 30, 2021

@Hongmo Kim have you considered looking at the rest API? it should be fairly simple to retrieve this information using a few API calls. see for more context and pointes https://api.commvault.com.


Forum|alt.badge.img+7
  • Author
  • Vaulter
  • 30 replies
  • December 30, 2021

@ Onno

 

Thanks for your advice but customer doesn’t consider the REST API at the moment.

Customer is eager to know how to get the config value using SQL query.

 

Any stunning way?


MichaelCapon
Vaulter
Forum|alt.badge.img+14
  • Vaulter
  • 348 replies
  • December 30, 2021

Hi @Hongmo Kim 
 

I guess you could try using the CommCellSubClientConfig view for the Client, Subclient, SP and Retention, then use the CommCellBkScheduleForSubclient view to get the associated Schedule Policy, Schedule Name and Schedule Backup Type.
- The two could be joined with the Subclient ID (appid) 

 

Something like this might possibly help:

use commserv

select distinct CommCellSubClientConfig.clientname, CommCellSubClientConfig.subclient, data_sp, data_sp_copy, log_sp, data_sp_copy_fullcycles, data_sp_copy_retendays, CommCellBkScheduleForSubclients.scheduePolicy, CommCellBkScheduleForSubclients.scheduleName, CommCellBkScheduleForSubclients.schedbackuptype
from CommCellSubClientConfig

join CommCellBkScheduleForSubclients
on CommCellSubClientConfig.appid = CommCellBkScheduleForSubclients.appid

where CommCellSubClientConfig.clientname like 'cvltclnt' and CommCellSubClientConfig.subclient like 'cvltclnt_file_backup'

 

My MSSQL is limited, so I am interested to see if anyone else has any other (better) ideas here :slight_smile:

 

Best Regards,
Michael

 


Forum|alt.badge.img+7
  • Author
  • Vaulter
  • 30 replies
  • December 30, 2021

@MichaelCapon 

 

Grateful for your tip !

but still cannot bring the Storage Policy and Retention set as “Incremental Storage Policy”.

Wondering where option value for “Incremental Storage Policy” is saved.

 It won’t be a problem if customer doesn’t use this option “Incremental Storage Policy” for incremental backup but it’s mandatory option for customer so should be able to bring the value from Storage Policy set as “Incremental Storage Policy”.

 

Any decent idea?


Mike Struening
Vaulter
Forum|alt.badge.img+23

@Hongmo Kim , the closest View I can find is this one:

https://documentation.commvault.com/11.25/expert/5546_commcellstoragepolicy.html

though that doesn’t include Incremental SP info.

I’ll see if I can get that from anyone in our docs team as the preferred method is CC Views, not direct MSSQL queries to the tables.

 


Forum|alt.badge.img+7
  • Author
  • Vaulter
  • 30 replies
  • Answer
  • February 12, 2022

@Mike Struening 

Grateful for your update on this.

I figured out and want to share where this value is saved. You can find this in “dbo.archGroup” table for Incremental Storage Policy and “dbo.ArchCopyToApp” for associaton related value.


Mike Struening
Vaulter
Forum|alt.badge.img+23

That’s fantastic, thanks for sharing!!


Forum|alt.badge.img+7
  • Byte
  • 37 replies
  • November 7, 2023

Just wanted to create a new thread and somehow stumbled on this one.. This is amazing answer! 

 

Thank you!


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings