SHARING: SQL Query to Find "HOW MANY DEPENDENT COPIES For Global DEDUPE POLICY"

Last post 02-01-2017, 11:51 AM by PJValdez. 1 replies.
Sort Posts: Previous Next
  • SHARING: SQL Query to Find "HOW MANY DEPENDENT COPIES For Global DEDUPE POLICY"
    Posted: 03-23-2016, 3:31 PM

    Just sharing

    Scneario:

    We have multiple Global Dedupe policies in our Environment. Made a workflow to create an Offsite Copy when configuring Subclients, HOWEVER needed to calculate how many Subclients were associated to a Global Dedupe Policy, for load balancing purposes.  The SQL command below will query the Commserve DB to find the gdsp AND tell you how many subclients are associated to it (only thing you would need to change is the WHERE statement where <CHANGEME> reflects a catch to you enviro's SPnames>).

     

     

     

    USE CVCLOUD

    select Substring(SPName,0,CHARINDEX(':',SPName)) as GlobalDedupePolicy,

      len(SPname) - len(replace(spname,':','')) as TotalSubclients,

      max(LogDate) as LogDate

    from cf_SurveyDDBProtection 

      where LogDate  >= DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0) and SPName like '<CHANGEME>%'

    Group By SPName

     

    Output

  • Re: SHARING: SQL Query to Find "HOW MANY DEPENDENT COPIES For Global DEDUPE POLICY"
    Posted: 02-01-2017, 11:51 AM

    Even Nerdier, Found a better way to query the above result (NOTE: in v11sp5 on up, this completely unnecessary, specially if you set up Media Agent Clusters).

     

    in the below instance it uses commserv vs cvcloud, if you use cvcloud that only updates well the cloud gathering scheduler ran, for us it was like 2 to 3 times a day.

     

    Also, I only return back the LOWEST gdsp with associations, i.e. if I had several global dedup policies, this query will return only the one with the lowest amount off associations.

     

    USE COMMSERV;
    select z.GDSP_name
    from
    (
    select top 1 c.name as GDSP_name, b.id, a.SIDBStoreId, COUNT(*) as occurance
    from archCopySIDBStore a
    join archGroupCopy b
    on a.SIDBStoreId =b.SIDBStoreId
    join archGroup c
    on b.archGroupId = c.id
    where c.name like '%NAME_OF_YOUR_GLOBAL_DEDUPPOLICY%'
    group by a.SIDBStoreId, b.id, c.name
    order by occurance asc
    ) as z
The content of the forums, threads and posts reflects the thoughts and opinions of each author, and does not represent the thoughts, opinions, plans or strategies of Commvault Systems, Inc. ("Commvault") and Commvault undertakes no obligation to update, correct or modify any statements made in this forum. Any and all third party links, statements, comments, or feedback posted to, or otherwise provided by this forum, thread or post are not affiliated with, nor endorsed by, Commvault.
Commvault, Commvault and logo, the “CV” logo, Commvault Systems, Solving Forward, SIM, Singular Information Management, Simpana, Commvault Galaxy, Unified Data Management, QiNetix, Quick Recovery, QR, CommNet, GridStor, Vault Tracker, InnerVault, QuickSnap, QSnap, Recovery Director, CommServe, CommCell, SnapProtect, ROMS, and CommValue, are trademarks or registered trademarks of Commvault Systems, Inc. All other third party brands, products, service names, trademarks, or registered service marks are the property of and used to identify the products or services of their respective owners. All specifications are subject to change without notice.
Close
Copyright © 2019 Commvault | All Rights Reserved. | Legal | Privacy Policy