Solved

How to fetch Backup Duration Trend via Job Summary/shortcut

  • 26 July 2021
  • 9 replies
  • 109 views

Badge +1

I have to fetch a monthly report where for example, Agent Type Virtual Server. For this agent I’ll fetch the Job historyfor each day and will apply sorting descending on Duration column. Then manually enter the duration in my report.

For this, is there a shortcut from Reports or some other option? Where I can fetch the highest duration by agent type for a range of dates. For each date highest duration. Like we have 

icon

Best answer by B*star 31 July 2021, 19:04

View original

9 replies

Badge

Hi,

Kindly check if below query helps you. You can modify according to your environment needs.

you can run this query from ssms or workflow or custom report.

 

declare @start_day as date

declare @end_day as date

set @start_day = '2021-07-28' --Date edit should be in same format

set @end_day = '2021-07-30'   --Date edit should be in same format


select distinct CONVERT(date, startdate) as 'Date',

max(convert(varchar(8),dateadd(S, durationunixsec,'1970-01-01'), 108)) as 'Duration',

idataagent from CommCellBackupInfo

where startdate >= @start_day and enddate >= @start_day

group by CONVERT(date, startdate),idataagent

order by CONVERT(date, startdate) DESC

Badge +1

Hello @Laurent, You’ll only require Commvault application and EXCEL.

This report will allow customer to review the performance of the backups running on Commvault by agent types.

In our case, let’s take Virtual Server agent. I’ll fetch backup history from 1st to end of the month. Then i’ll save it by

Right Click any column of backup history>>Save

It will be saved as a MS Excel 97(xls) file. Open the file, keep necessary column like duration, start time, end time, etc. and remove rest. Then:

Insert two columns after start time>select whole Start Time column>go on Data menu>Click Text to Column>

This will Seperate date and time from Start time column. Convert this new start time date column to a different format(left column in below image).

Remove the other two columns after start time. Now insert another column to get the duration in minutes. To do so use a formula =<selected cell>*1440(1440=24hr * 60mins)

Press enter. Data might be in decimals so change it’s format to whole number from “Format Cells” Option by selecting whole new Duration column.

To fill same data with applied formula for whole column, simply double click the plus icon at the end of the cell.

Now you can apply sort descending on this duration column, and apply filter on start time column. Start selecting the date you want the highest duration for. And enter it in your original “Backup Success Trend” Report.

Threshold will depend based on your production hours and from the schedule start of the agent’s jobs. Create a graph from this table. And you can share the graph to customer.

 

Hope this info might be useful to you or someone else.

Badge

Hi,

  • Open ssms and pass the credentials
  • expand ‘databases’ → find “CommServ” database → right click and select “new query”
  • New tab opens with blank space. We can paste the query there and execute it (Shortcut button : F5). After successful process completion of query, results will be displayed.

 

updated query :

declare @start_day as date

declare @end_day as date

set @start_day = '2021-07-28' --Date edit should be in same format

set @end_day = '2021-07-31'   --Date edit should be in same format

select distinct CONVERT(date, startdate) as 'Date',

max(convert(varchar(8),dateadd(S, durationunixsec,'1970-01-01'), 108)) as 'Duration',

idataagent from CommCellBackupInfo

where startdate between @start_day and @end_day

group by CONVERT(date, startdate),idataagent

order by CONVERT(date, startdate) DESC

Userlevel 7
Badge +15

Ah got it - yes I was wondering if we had a close enough in-built report that you could modify using our custom reports feature

Badge +1

First issue will be to fetch the details, from reports, We can’t get the Duration column . Which is a must.

 

I got a manual solution yesterday, to fetch the Job history of Virtual server agent and then save it as excel. Remove unnecessary columns and filter out desired data. It’s time saving. Easiest so far.

 

I was hoping for something more time saving, but we can work with this out for now.

 

Thanks Damian btw. 

Badge +1

@B*star I’m not familiar with SSMS. Can you please let me know it’s full form.

 

This code looks promising, I might wanna give it a hit.

Userlevel 7
Badge +15

@MFasulo - do we have a built-in report for command center that might cover this, or more of a custom report?

 

Badge +1

It’s a custom report.

This is how we’ll be representing it to customer.

Userlevel 4
Badge +11

Hello !

I would be also interested in such a report, without having to use some 3rd party tool :-)

Reply