SQL Query to get list of Jobs present in specific copy of a Storage Policy

HI ​@rahul_nair ,

We have ready report named with Jobs in Storage Policy Copies report displays all jobs that ran for the specified storage policy and the specified storage policy copy.

https://documentation.commvault.com/11.20/jobs_in_storage_policy_copies.html 
https://documentation.commvault.com/11.20/viewing_jobs_in_storage_policy_copies_report.html

Hi Pradeep,

Thanks for the info, i have went through the report but i am trying to get a query since i need to use in workflow to take some action on the job ids. Hence i need the query rather then using the report. Will you be able to help me with the query 

Hi ​@rahul_nair ,

Could you please check the 'CommCellBackupInfo' table in CommServDB?
It should provide the job ID along with client-related details for a given Storage Policy.

You can start with a basic query like: SELECT * FROM CommCellBackupInfo

In this table, the column 'data_sp' represents the Storage Policy name. You can filter on this column to retrieve the list of jobs associated with a specific Storage Policy.

Ref Query by filtering the required details : select jobid, clientname, idataagent, subclient, data_sp, jobstatus, startdate, enddate  from CommCellBackupInfo where data_sp = '<<Storage Policy Name>>'

Regards,
Dheeraj

I need it for specific copy in storage policy. This query you gave will return for all the jobs of storage policy and . I need specific to one copy and only the active jobs.