Hello everyone,

Like the most of you I'm also a Avamar-user and very font of stats and graphs.

For the past couple of weeks I've been trying to create the following stats:

1. Amount of data protected per domain (as in a domain in Avamar Administration, not Data Domain(s)).
2. Amount of data protected per domain per server/client.
3. Amount of servers/clients per domain (once again, as in domain in Administration, not Data Domain(s)).

What I've done so far is try to merge this SQL-code

select CASE WHEN display_name is NULL
                 THEN client_name
            ELSE display_name
       END as "ClientName",
       plugin_name as "PlugInName",
       CASE WHEN sch_sum_bytes is NULL
                 THEN '/Client On-Demand Data'
            WHEN adhoc_max_bytes is NULL
                 THEN 'All Custom Datasets'
            WHEN sch_sum_bytes >= adhoc_max_bytes
                 THEN 'All Custom Datasets'
            WHEN sch_sum_bytes < adhoc_max_bytes
                 THEN '/Client On-Demand Data'
            ELSE 'REPORT ERROR'
       END as "Dataset",
       cast((
       CASE WHEN sch_sum_bytes is NULL
                 THEN adhoc_max_bytes
            WHEN adhoc_max_bytes is NULL
                 THEN sch_sum_bytes
            WHEN sch_sum_bytes >= adhoc_max_bytes
                 THEN sch_sum_bytes
            WHEN sch_sum_bytes < adhoc_max_bytes
                 THEN adhoc_max_bytes
            ELSE 99999999
       END) / 1024/1024/1024 as numeric(30,4)) as "TotalGBProtected",
          c.agent_version as "Version",
          c.os_type as "OS",
          case when c.is_client_os='t' then 'true' when c.is_client_os='f' and c.agent_version !~ '^1.|^2.|^3.|^4.|^5.|^6.0' then 'false' else 'unknown' end as "IsClientOS"


 from (select cid, client_name, display_name,
              plugin_name,
              sum( sch_max_bytes ) as sch_sum_bytes
       from ( select cid, client_name, display_name,                  
                     plugin_name,
                     dataset,
                     max(bytes_scanned) as sch_max_bytes
              from v_activities_2
              where (v_activities_2.status_code in (30000, 30005)) and
                    (v_activities_2.type like '%Backup%') and
                    (v_activities_2.dataset not like '/Client On-Demand Data') and
                    (expiration_ts = '0' or expiration_ts::double precision >= extract( epoch from now() ))
              group by cid, client_name, display_name, plugin_name, dataset ) as sel1
       group by cid, client_name, display_name, plugin_name ) as sel2
 FULL JOIN
       ( select cid, client_name, display_name, plugin_name, max(bytes_scanned) as adhoc_max_bytes
              from v_activities_2
              where (v_activities_2.status_code in (30000, 30005)) and
                    (v_activities_2.type like '%Backup%') and
                    (v_activities_2.dataset like '/Client On-Demand Data') and
                    (expiration_ts = '0' or expiration_ts::double precision >= extract( epoch from now() ))
              group by cid, client_name, display_name, plugin_name ) as sel3
 USING (cid, client_name, display_name, plugin_name) LEFT JOIN clients c USING (cid)

which has this result:

with a few blocks of SQL-code

select
       CASE
         WHEN
           length(substr(full_domain_name, 1, length(full_domain_name) - length(client_name))) = 1
         THEN
           substr(full_domain_name, 1, length(full_domain_name) - length(client_name))
         ELSE
           substr(full_domain_name, 1, length(full_domain_name) - length(client_name) - 1)
         END  as "ClientDomain",
       CASE WHEN display_client_name is NULL
                 THEN client_name
            ELSE display_client_name
       END as "ClientName",
       to_char(created, 'mm/dd/yyyy') as "RegisteredDate",
       registered as "Activated",
       to_char(registered_ts, 'mm/dd/yyyy hh24:mi:ss') as "ActivatedDateTime",
       to_char(checkin_ts, 'mm/dd/yyyy hh24:mi:ss') as "LastCheckedIn",
       contact_name as "ContactName",
       contact_phone as "ContactPhone",
       contact_email as "ContactEmail",
       contact_location as "ContactLocation",
       contact_notes as "ContactNotes",
       os_type as "OS",
       agent_version as "ClientVersion",
       client_addr as "ClientAddr"
  from v_clients_2, axion_systems
  where has_backups = true
    and axion_systems.systemid = 1
    and full_domain_name not like '%MC_RETIRED%'

which has this result:

As stated before, I'm not interested in the outcome of the query above except the "ClientDomain" part (of the code). What I'm trying to do is very simple; I'm using the first query and the "ClientDomain"-code of the second query in combination with a inner/full join-statement, hoping that the outcome will be something like:

But as you may have predicted already, I've not been able to do so .

Can anyone help me please?