vCloud iso search for uploaded on all tenants

Sometimes you may have issues when upload ISO/OVA to vCloud. Issues might be not to move file to transfer NFS or not to move from transfer NFS to vCloud cluster.

For faster troubleshot if you see in progress bar upload stuck at 50% this means file was successfully uploaded to transfer NFS but might be a issue between NFS transfer server and vCloud cluster.

If you need to check status for upload you may use the following trick from postgres db:

1. Connect to postgres db: sudo -u postgres psql vcloud
2. Search global in db for the uploaded file: select * from global_search(‘CentOS-7-x86_64-Minimal-20091111.iso’);
3. Search in the media table to check upload path: select * from managed_media where media_name = ‘CentOS-7-x86_64-Minimal-20091111.iso’;

In this example I’ve used a Centos 7 minimal ISO.

If you don’t have global_search function enabled in postgres db you can create it with the following snipet:

CREATE OR REPLACE FUNCTION global_search(
search_term text,
param_tables text[] default '{}',
param_schemas text[] default '{public}',
progress text default null -- 'tables','hits','all'
)
RETURNS table(schemaname text, tablename text, columnname text, rowctid tid)
AS $$
declare
query text;
hit boolean;
begin
FOR schemaname,tablename IN
SELECT table_schema, table_name
FROM information_schema.tables t
WHERE (t.table_name=ANY(param_tables) OR param_tables='{}')
AND t.table_schema=ANY(param_schemas)
AND t.table_type='BASE TABLE'
LOOP
IF (progress in ('tables','all')) THEN
raise info '%', format('Searching globally in table: %I.%I',
schemaname, tablename);
END IF;

query := format('SELECT ctid FROM %I.%I AS t WHERE strpos(cast(t.* as text), %L) > 0',
schemaname,
tablename,
search_term);
FOR rowctid IN EXECUTE query
LOOP
FOR columnname IN
SELECT column_name
FROM information_schema.columns
WHERE table_name=tablename
AND table_schema=schemaname
LOOP
query := format('SELECT true FROM %I.%I WHERE cast(%I as text)=%L AND ctid=%L',
schemaname, tablename, columnname, search_term, rowctid);
EXECUTE query INTO hit;
IF hit THEN
IF (progress in ('hits', 'all')) THEN
raise info '%', format('Found in %I.%I.%I at ctid %s',
schemaname, tablename, columnname, rowctid);
END IF;
RETURN NEXT;
END IF;
END LOOP; -- for columnname
END LOOP; -- for rowctid
END LOOP; -- for table
END;
$$ language plpgsql;

Need help automating and auditing multi-tenant cloud infrastructure?
→ Check out our Infrastructure Support Services