Solved

How to fetch Backup Duration Trend via Job Summary/shortcut


Userlevel 1
Badge +2

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

21 replies

Userlevel 7
Badge +23

Not yet, though keep in mind the normal cycle for a CMR means we likely won’t hear anything for a while (or at all).

I would expect at least 6 months before this is possibly implemented.

If you need anything more immediate, I would reach out to your Account Rep.

Userlevel 1
Badge +2

Hi Mike, did you receive any update on it.

Userlevel 7
Badge +23

@Rishabh Kumar , I created a CMR in your name to get initiating user added to the CommCellBackupInfo view:

348477

Userlevel 1
Badge +2

I need columns like Start date, end date, username, job type, duration, backup status(completed/failed/CWE,etc)

 

So for now I guess username, Backup status and Job Type columns to be added so I can filter full backups too sometimes. 

 

Userlevel 7
Badge +23

@Rishabh Kumar , so I get everything you need, can you give me a list of each field you need (that does not exist now)?

I’ll put it all in the same CMR.

Userlevel 1
Badge +2

@Mike Struening , Yes please raise one, we only require durations for scheduled jobs, so we have to filter “Admin” jobs either with where clause or through excel.

Userlevel 7
Badge +23

@Rishabh Kumar , I heard back...we don’t have any views for the initiating user.  The number of table joins would slow things down, though I can put in a CMR to get it added to the view if you’d like.

Userlevel 7
Badge +23

@Rishabh Kumar , let me find out.  I’m not seeing a CommCell View for this.  I know the users are stored in the user table, and it might be a simple enough matter of pulling initiating user names with user IDs in job info or elsewhere, though I’m not 100% sure.

I’ll be in touch (or get someone to reply here).

Thanks!

Userlevel 7
Badge +23

Moved the new question to its own thread:

 

Userlevel 1
Badge +2

@Mike Struening I used BackupLevel column to filter ‘Incremental’ in below query with where clause found on the documentation you provided. Thanks for that!

 

declare @start_day as date

declare @end_day as date

set @start_day = '2022-01-01' --Date edit should be in same format

set @end_day = '2022-01-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 and backuplevel = 'incremental' 

group by CONVERT(date, startdate),idataagent

order by CONVERT(date, startdate) DESC

 

But unable to find the correct way to put either UserName column or filter. I tried User_id from suggestion. Tried CommCellPrePostCMDInfo.UserName and alot more with no luck from documentation or suggestions in SSMS. Can you please help with that?

 

Userlevel 7
Badge +23

@Rishabh Kumar , you should be able to add a whrre clause for the admin user ID (I’m 99.99% sure it is 1).

Better yet, you can pull a LOT of that info (and much, much more!) from this built in SQL view:

https://documentation.commvault.com/11.24/expert/5524_commcellbackupinfo.html

Userlevel 1
Badge +2

Hi, Finally I got SSMS installed by approvals and cleared login issues.

 

@B*star - Thank you so much, the query works just fine after I selected CommServ. Appreciate it.

 

Additionally it would be great if someone could help me fetch only incremental data and also the jobs only triggered by “Admin” not some user ID. Or simply just Username and Job Type columns which I can filter out.

 

 

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

@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 1
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

Userlevel 1
Badge +2

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.

Userlevel 6
Badge +15

Hello !

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

Userlevel 1
Badge +2

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. 

Userlevel 7
Badge +23

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

Userlevel 1
Badge +2

It’s a custom report.

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

Userlevel 7
Badge +23

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

 

Reply