This post is more than 5 years old
6 Posts
0
2868
December 30th, 2014 07:00
Avamar SQL Queries for Job Reporting
I would like to run a SQL query to find out the following information from the avamar database. Can anyone clue me in on the queries or point me to the right views/tables?
- The total duration a job ran. (It'd be nice to have the history of how long the job ran, but I'll settle for the last time the job ran.)
- The total client success/failures of a job
- A list of the clients that failed by job
- Integrity Status of the appliance
I basically want to automate a custom report for our NOC to take action on.
-Leslie
No Events found!
lesliebns9
6 Posts
1
January 9th, 2015 08:00
I think I found everything I need. I ended up doing a combination of database queries and mccli output with the xml option to get what I needed.
SystemMangler
24 Posts
2
December 30th, 2014 22:00
You could implement this in a couple of ways (EMC Data Protection Advisor, custom PSQL scripting), but my preferred method is to use Excel to access the Activities View in the MCDB database using ODBC as described in the Administration guide for Avamar here.
A couple of notes:
Thankfully, doing it this way allows Excel to correctly intrepted the time fields that Avamar uses and thus duration becomes completed_ts - started_ts. You will need to apply a custom format to the column in Excel to make sense of the answer: :mm:ss.
Jonathan
ionthegeek
2 Intern
•
2K Posts
1
January 9th, 2015 08:00
If there's information you'd like to see in the database views that is only available through mccli, please let me know what information is missing and I can send a request for enhancement to the team that works on the views. I can't guarantee that it will be added but that team is generally very good about helping with this type of request.
lesliebns9
6 Posts
0
January 9th, 2015 09:00
The biggest thing I'm missing is the Running and Waiting-Queued tasks that are shown in mccli activity show. I'm unable to find those in the database. Once they fail or complete I can get them from the v_activities_2 table.
ionthegeek
2 Intern
•
2K Posts
0
January 9th, 2015 10:00
I dug into this a bit with some of my colleagues. At the moment, running and queued activities are only tracked in memory (they're not in the database at all) so a change like this would be non-trivial.
As an alternative, one of my colleagues suggested taking advantage of the REST API that's available as an add-on for 7.0 and 7.1 systems. The API package is only available via RPQ but if you get in touch with your account team, they can help you get access. The information you're looking for is all available through the REST API so you should be able to boil things down to a single interface.