How to find all Clients not in a group

Have you tried the Client manager interface through Enterprise manager ? This easily allows you to move clients and relocate them.

When you say do not belong, do you mean default group ? If so Client Manager should do the trick but you can also use policy manager in the Avamar Admin console.

Misty...

When I say do not belong I mean after I imported the vCenters in to the grid I created groups then added clients to them. After a month or so new vm's were spun up and people began to utilize "import multiple clients" from the administrator without assigning them to a group. It is these clients that I need to get a handle on.

Thanks for the reply!

I use an xls spreadsheet with a pivot table and a connection to the Avamar MCS database for this exact purpose.  We also have our VM's split between Optimised and Standard for retention so it shows me which VM's are in which group, which are in both (don't wnat that but can happen sometimes) and also which are only in the 'Default' group (ie not in any active group.

I only do this for a single vCenter in our main data center with 250 - 300 VM's but it should help with what you want to do.

Before I used the pivot table with live DB connection I used to just export the groups from the DB using the Postgre SQL Admin client and run vlookup - this is fine if you do this infrequently but if it's a regular process then the live connection to the pivot table is better.

You need the Postgre SQL admin installed (I have 9.1) then you can connect to your MCS DB using these details:

Name: MCDatabase

Host: 'your Avamar node/grid name'

Port: 5555   

Maint DB: mcdb

Username: viewuser

Password: viewuser1

The viewuser, as the name suggests, is only able to view the DB so it won't allow you to mess up the tables.

The details for the connection to our pivot table spreadsheet are:

Connection string

DSN=MCDatabase;DATABASE=mcdb;SERVER=audc1avun01;PORT=5555;UID=viewuser;SSLmode=disable;ReadOnly=1;Protocol=7.4-1;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=1;

MaxVarcharSize=254;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=1;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=0;

UnknownsAsLongVarchar=0;BoolsAsChar=0;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;LFConversion=1;UpdatableCursors=1;

DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=0;LowerCaseIdentifier=0;GssAuthUseGSS=0;XaOpt=1

SQL query / command

SELECT v_group_members.group_name, v_group_members.enabled, v_group_members.client_name, v_group_members.cid, v_group_members.dataset_name, v_group_members.retention_name, v_group_members.use_client_ds, v_group_members.use_client_rp, v_group_members.use_client_timeout, v_group_members.use_client_retry, v_group_members.restore_only, v_group_members.domain, v_group_members.full_client_name

FROM public.v_group_members v_group_members, public.v_group_members v_group_members_1

WHERE v_group_members.client_name = v_group_members_1.client_name AND ((v_group_members_1.group_name='Default Virtual Machine Group'))

if the clients are not backed up, but registered in Avamar maybe you can use the standard report "Clients - Unprotected" in the Avamar report section. this gives you an overview of clients that aren't protected.