Skip to main content
commvault.com commvault.com
Solved

No longer able to SQL query Monthly Chargeback report

S
Byte
Forum|alt.badge.img+5

We recently upgraded to 11.28. We have used the metrics server to query the Monthly Chargeback table under CVCloud DB so we can get charges back to our customers. This no longer has any data. I did a query of pulling everything with no parameters and nothing is there. Is there a new table to query for these reports.

 

Here is my current SQL query

SELECT client.ClientName AS ServerName, MAX(CAST(monthly.FrontEndSize AS float)/1073741824) AS QTY, monthly.StartDate AS CVMonthAdded, --GETDATE() , CASE WHEN client.CommServUniqueId = '10000' THEN '1460' WHEN client.CommServUniqueId = '10003' THEN '760' WHEN client.CommServUniqueId = '10005' THEN '4903' WHEN client.CommServUniqueId = '10006' THEN '4903' WHEN client.CommServUniqueId = '10012' THEN '3303' WHEN client.CommServUniqueId = '10014' THEN '9609' WHEN client.CommServUniqueId = '10015' THEN '9529' WHEN client.CommServUniqueId = '10017' THEN '9609' ELSE '' END as BLC --, client.CommServUniqueId --, monthly.ClientId, client.LatestDataProtection, monthly.StartDate FROM [KBSGBY0RPT01\COMMVAULT].[CVCloud].[dbo].[cf_SurveyMonthlyChargeback] monthly JOIN [KBSGBY0RPT01\COMMVAULT].[CVCloud].[dbo].[cf_SurveyClient] client ON (monthly.CommservUniqueId = client.CommServUniqueId AND monthly.ClientId = client.ClientId) WHERE monthly.Type = 1 AND (StartDate > GETDATE() - 50) AND (monthly.FrontEndSize/1073741824) >= 5 GROUP BY ClientName, monthly.StartDate, client.CommServUniqueId

Best answer by Mike Struening RETIRED

Sharing case resolution:

Development confirmed that as part of a project to reduce the size of CVCloud database, cf_SurveyMonthlyChargeback table was moved to UsageHistoryDB in SP26.
The new table in UsageHistoryDB is called ChargebackUsageDetails.

View original
Did this answer your question?

8 replies

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

@ShaneHicks , thanks for the post!

To confirm, the tables are there, just empty?  If you run the billing reports, do you get results?

One thing to consider is getting the REST API script from any Web Console report:

https://documentation.commvault.com/2022e/expert/134936_copying_rest_api_for_report_table_in_web_console.html

I don’t see anything stating that the report has changed, though if we changed the underlying SQL, that likely wouldn’t be documented:

https://documentation.commvault.com/2022e/expert/38095_chargeback_report_overview.html

Curious, where did you get the SQL query you have now?  Might be worth checking with them as well.

If you didn’t know, we are moving towards using CommCell Views instead of direct table queries (easier to track changes) though I don’t see any for billing.

https://www.linkedin.com/in/michael-struening
S
Byte
Forum|alt.badge.img+5
  • ShaneHicks
  • Author
  • Byte
  • 16 replies
  • October 7, 2022

A teammate and I created the query we have now and has been around for several years. 

I have opened a ticket as well. We have been shifting over to Restapi on other efforts and this like one we need to change as well..

Can you elaborate on Commcell Views? Link?

 

Thanks for replying.

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

Sure thing:

https://documentation.commvault.com/2022e/expert/39461_commcell_views_01.html

CommCell Views are essentially SQL Views to collect common data.  This way, no matter what changes in the database, developers only need to update the view code and your functions work as expected.

I did get some more detail for you:

You can get the SQL query that's used to generate a report in either webconsole or command center.

 

  1. open the report

  2. click on 'Edit' in the top right

  3. go to the 'DataSets' tab

  4. click 'Edit' on the dataset you want the query for and it will show you

This way, you can get the updated query right from the report itself.

https://www.linkedin.com/in/michael-struening
S
Byte
Forum|alt.badge.img+5
  • ShaneHicks
  • Author
  • Byte
  • 16 replies
  • October 7, 2022

Thanks for the information. The Edit on the dataset was how we got the original query and changed to fit our scenario. Trying to avoid going through all of that again,

Mike Struening
1 person likes this
  • Mike Struening
    Mike Struening
Mike Struening
Vaulter
Forum|alt.badge.img+23

Let me know the case number when you get a chance.  I imagine the engineer is is likely going to do something similar to get the details for you.

https://www.linkedin.com/in/michael-struening
S
Byte
Forum|alt.badge.img+5
  • ShaneHicks
  • Author
  • Byte
  • 16 replies
  • October 7, 2022

# 221007-351

 

Reply I have received:

Hi Shane,

 

I am seeing the same behavior on my end.

 

Also tested on a SP24 server and at least this query returned results: SELECT *  FROM [CVCloud].[dbo].[cf_SurveyMonthlyChargeback]

 

I sent an email to Development to confirm if the [cf_SurveyMonthlyChargeback] table has been deprecated or replaced.

 

Will keep you posted on their feedback.

 

Best regards,

Sarahy Pardo

Commvault Support

Mike Struening
1 person likes this
  • Mike Struening
    Mike Struening
Mike Struening
Vaulter
Forum|alt.badge.img+23

Sarahy is absolutely brilliant.  One of our best in all of support.

I’ll keep an eye out for updates!

https://www.linkedin.com/in/michael-struening
Mike Struening
Vaulter
Forum|alt.badge.img+23

Sharing case resolution:

Development confirmed that as part of a project to reduce the size of CVCloud database, cf_SurveyMonthlyChargeback table was moved to UsageHistoryDB in SP26.
The new table in UsageHistoryDB is called ChargebackUsageDetails.

https://www.linkedin.com/in/michael-struening

Reply


Most helpful members this week

Terms & ConditionsCookie settings

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