Solved

WebConsole custom report Date format when exporting

  • 13 April 2021
  • 9 replies
  • 540 views

Badge +1

Hi,

 

I have created a custom report showing the backup job summary and defined a custom date format for the starttime.

But when the report is exported/saved to CSV the date format aren’t used. it uses US date format

How to fix this ?

icon

Best answer by Stuart Painter 19 May 2021, 15:31

View original

9 replies

Userlevel 5
Badge +8

Hi Henrick, 

Please can you check the following. 

  • Check the Date format settings configured under 'My Settings' on the report is correct as required. 
  • Open the CSV in Notepad and confirm if the date format it coming in correctly. 

I am assuming you are opening the CSV in excel and I have seen the column change its format automatically in Excel and the test in notepad will confirm if it is exporting correctly. 

 

Badge +1

Hi Blaine,

 

When displaying the report in the WebConsole it show this format as start time: 2021-13-04 14:04:00

But when the report are saved as a CSV file it uses this format : Apr 13, 2021, 10:30:08 AM

I did open it in notepad.

I am not able to find “My Settings” on the report anywhere ?

 

Badge +1

Hi 

I could settle for an SQL statement which give me this output for the last 24 hours:

"Job ID","Server","ClientId","Agent","BackupSet","Subclient","Type","Backup type","Start time","Job status","Failure reason"
"72652","prdodz03","124","Oracle Database","default","ArchiveLog","Backup","Full","Apr 13, 2021, 10:30:08 AM","Completed",""
"72648","mgrodz01","129","Oracle Database","default","ArchiveLog","Backup","Full","Apr 13, 2021, 10:30:08 AM","Completed",""
"72647","preodz01","117","Oracle Database","default","ArchiveLog","Backup","Full","Apr 13, 2021, 10:30:08 AM","Completed",""
"72646","prd01","14","SQL Server","defaultBackupSet","default","Backup","Transaction Log","Apr 13, 2021, 10:30:08 AM","Completed",""
"72645","dz02","120","Oracle Database","default","ArchiveLog","Backup","Full","Apr 13, 2021, 10:30:08 AM","Completed",""
"72644","prd02","13","SQL Server","defaultBackupSet","default","Backup","Transaction Log","Apr 13, 2021, 10:30:08 AM","Completed",""
"72643","dz01","115","Oracle Database","default","ArchiveLog","Backup","Full","Apr 13, 2021, 10:30:08 AM","Completed with errors","RMAN returned WARNING during backup operation [RMAN-06820: warning: failed to archive current log at primary database]. Please check RMAN logs for details and take corrective action.<br>Source: dz01, Process: ClOraAgent"
"72642","prd03","12","SQL Server","defaultBackupSet","default","Backup","Transaction Log","Apr 13, 2021, 10:30:08 AM","Completed",""

 

Userlevel 5
Badge +8

Hi Henrik, 

This looks like you are getting information regarding job summary but you are creating a new report? 

Have you looked at the Backup Job Summary report ?  This should fulfil your needs without customisation. However if you feel the need to customise it that is still possible. 

If you edit that report you can change the date format.  Select the Start Time field (or date field you want). 

https://documentation.commvault.com/commvault/v11/article?p=45038.htm

 

Also take a look at the CommCellBackupInfo view in sql As this might be what you are looking for if you just want to get the sql output.  https://documentation.commvault.com/commvault/v11/article?p=5524.htm

Accessing Commcell Views: https://documentation.commvault.com/commvault/v11/article?p=5519.htm

 

 

Badge +1

Hi Blaine

I did take the job summary report, and then removed the columns i didn’t need. Then i did change the date format just as you described.

But when you save report to a file the date format get’s changed.

 

But the SQL statement was perfect, much better since i can use that directly from powershell, instead of scheduling a report being saved, and then pull the numbers.

Thanks.

 

 

Userlevel 7
Badge +15

Hi @Henrik.Grevelund 

Thanks for the confirmation, I’m pleased you got what you needed from the CommCell View SQL statement.

I’ll mark @Blaine Williams post as Best answer.

Thanks,

Stuart

Badge +1

Hi @Stuart Painter ,

 

I would say that there still is a problem. Why does it not export using the same format as defined in the report.

Do i need to open a support case to get that fixed ?

 

Userlevel 7
Badge +15

Hi @Henrik.Grevelund 

Yes, please open a support case and PM me the number so we can track, then we can add the resolution in here for the benefit of the Community.

Thanks,

Stuart

 

Userlevel 7
Badge +15

Hi @Henrik.Grevelund 

I see a resolution was provided in your support case:

1. On the Commserve, open registry and navigate to 

HKEY_LOCAL_MACHINE\SOFTWARE\CommVault Systems\Galaxy\Instance001\CustomReportsEngine

2. In CustomReportsEngine, add the 2 values:

 

Name: csvDateTimeFormat

Type: String

Value: yyyy-dd-MM HH:mm:ss

 

Name: csvDateFormat

Type: String

Value: yyyy-dd-MM

 

3. After adding the entries, restart CommServe services using the Process Manager.

 

These settings will of course affect all reports generated, but it would be preferable to have date format selected on report generation, as required.

Development have reached out to explain this situation and what is being done behind the scenes.

When a report is requested with specific formatting, some processing needs to happen to present the data in the requested format.

CSV processing is performed in the backend to enable processing of larger datasets.

PDF and HTML processing is performed at the front end, but datasets are limited to avoid browser crash - which is why you see a difference in behaviour.

The registry keys above govern CSV backend processing formats, but on a system-wide level.

I am pleased to say Development are already working on a new design for processing report data to resolve these issues, which will be introduced in a future release.

Thanks,
Stuart

Reply