Avamar - How to query the PostgreSQL MCS database

Here is how to connect to the DB if you need custom reports and want to create custom queries:

1.)  SSH to the Utility node


root@dpn:~/#: su - admin
admin@dpn:~/>: psql -p 5555 -U viewuser mcdb
Welcome to psql 8.2.3, the PostgreSQL interactive terminal.

mcdb=>

Note:  Please use "viewuser" (read only) account for your queries !

2.) Here is the example for a simple SQL query, but you can build your own queries and export it as plain text .

    SELECT * FROM "public"."v_clients";
    SELECT v_clients.cid, v_clients.client_name, v_clients.client_addr,v_clients.os_type FROM "public"."v_clients";


select client_name,status_code,domain FROM v_activities_2 WHERE (status_code=30901 or status_code=30915) and recorded_date_time > current_timestamp - interval '24 hours';

avagent Error 5320- The client named: client1 is not registered as CID: NO LOCATION - RECORD MAY BE CORRUPT

While I was trying to fix a corrupted install I got the following error:

2013-11-13 09:40:40 avagent Error <5320>: Received from MCS#103:The client named: client1 is not registered as CID: NO LOCATION - RECORD MAY BE CORRUPT

2013-11-13 09:40:40 avagent Error <6210>: Unable to register 'client1' with MCS at utilitynode_addr:28001

Fix:

 Delete de client ID file (cid.bin), and re-register.

Avamar - How to get around the 1 schedule repetition per hour maximum

I recently stumbled on the need to take SQL transactional backups every 15 minutes for a specific project (RAID protection on an active-active array was not enough it seem), I came up with a  solution using crontabinf the EMC community forums.

1. Create a Dataset such as SQL1hrinc
    a. Select the database to backup
    b. Choose incremental as backup type

2. Under Policy, create a New Group i.e., SQL1hrinc
    a. Add Dataset SQL1hrinc to the group
    b. Select Retention, client etc

3. Under Policy, click on Group and run backup to make sure group meets the requirements

Now, please login to Avamar utility node via Putty as root user

1. Create a batch file in the /usr/local/avamar/bin such as sqlinc and add the following:
/usr/local/avamar/bin/mccli client backup-group-dataset --xml --name=/DOMAIN_NAME/CLIENT_NAME --group-name=/SQL1hrinc

2. Save file and set the execute permissions i.e., chmod 755 sqlinc

3. Run the file and make sure that the backup is performed.

4. As root, type crontab -e and add the following:

*/60 7-19 * * 1-5 /usr/local/avamar/bin/sqlinc >/dev/null 2>&1

5. Save and exit

(As seen on the Avamar forums, thanks Sandeep Sinha)