Solved

No longer able to SQL query Monthly Chargeback report

  • 7 October 2022
  • 8 replies
  • 108 views

Userlevel 1
Badge +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

icon

Best answer by Mike Struening RETIRED 31 October 2022, 21:52

View original

8 replies

Userlevel 7
Badge +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.

Userlevel 1
Badge +5

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.

Userlevel 7
Badge +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.

Userlevel 1
Badge +5

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,

Userlevel 7
Badge +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.

Userlevel 1
Badge +5

# 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

Userlevel 7
Badge +23

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

I’ll keep an eye out for updates!

Userlevel 7
Badge +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.

Reply