CP to Export list of items with metadata in a CSV

2 Likes
over 1 year ago

ControlPoint allows the data analysts to export a list of items in a CSV file with the desired associated metadata.

Go to the report, as known as category, in the list of items. Select those you want to export in the list.

image.png

Go to ACTIONS and select EXPORT.

image.png

You get a CSV file with all items selected.

image.png

To customize the columns, go to ADMINISTRATION >>> SETTINGS >>> GENERAL >>> FIELDS >>> ITEMS PROPERTIES.

image.png

You can add or remove fields that are exported in the CSV file.

 

Labels:

How To-Best Practice
Comment List
Anonymous
Parents
  • Check in this table that the Filesize column is 0 for your file:

    select * from [ControlPointMetaStore].[Metadata].[Document] WITH (nolock)
    where DocumentName = 'zero k file.txt'

    replace 'zero k file.txt' with the name of file you are wishing to check in metastore


    Also you can use SQL profiler shipped with SQL Server to see the underlying query issued to SQL.

    Within SQL profiler Select the following event to capture:
    TSQL: SQL:BatchCompleted

    Click Show all columns

    Then select Column Filter and Add DatabaseName Like ControlPointMetaStore

    This limits the traffic to just those calls to ControlPointMetaStore database.

    Click Run and then capture yourself clicking the category to display the docs in the listview.

    Then stop the trace.

    You will see a line in the resulting SQL trace that contains 2 lines like below:

    SELECT TOP 25 ROW_NUMBER() OVER(ORDER BY ":SortExpr:0", ":SortExpr:1") AS ":Hit", ":RepositoryId", ":DocKey:0", "DREREFERENCE", "AU_DOCUMENT_FILESIZE_BYTES", "CPROOTPARENTREFERENCE" INTO "#DataSet" FROM (SELECT ROW_NUMBER() OVER(PARTITION BY t1."MatchArchive" ORDER BY t1."MatchArchive") AS ":CombineNumber", t0."RepositoryId" AS ":RepositoryId", t0."DocKey" AS ":DocKey:0", t0."RepositoryId" AS ":SortExpr:0", t0."DocKey" AS ":SortExpr:1", t0."DocKey" AS "DREREFERENCE", t0."FileSize" AS "AU_DOCUMENT_FILESIZE_BYTES", t1."MatchArchive" AS "CPROOTPARENTREFERENCE" FROM "Metadata"."Document" t0 LEFT OUTER JOIN "ControlPointMetadata"."Additional" t1 ON t0."RepositoryId" = t1."RepositoryId" AND t0."DocKey" = t1."DocKey" WHERE t0."RepositoryId" = 1 AND t0."FileSize" = 0) tbl WHERE ":CombineNumber" = 1
    ORDER BY ":Hit";

    SELECT t0.":Hit", 0 AS ":FieldSet", t0.":RepositoryId", t1."Value" AS "DREREFERENCE", t2."DocumentName" AS "DRETITLE", t2."FileExtension" AS "IMPORTMAGICEXTENSION", t2."DocumentStatus" AS "CP_DOCUMENT_STATUS", t3."Value" AS "DREROOTPARENTREFERENCE", t2."OriginalFileExtension" AS "ORIGINALEXTENSION", t0."AU_DOCUMENT_FILESIZE_BYTES", t2."DisplayName" AS "AU_REPOSITORY_TITLE_STRING", t2."AutnIdentifierValue" AS "AUTN_IDENTIFIER", t4."Value" AS "CPLOCATION", t5."Value" AS "CPROOTFAMILYREFERENCE", t7."Value" AS "CPROOTPARENTREFERENCE", t2."DateLastModified" AS "AU_REPOSITORY_MODIFIEDDATE_EPOCHSECONDS", NULL AS "CP_HOLD", NULL AS "CP_POLICYID" FROM "#DataSet" t0 INNER JOIN "Metadata"."ReferenceHash" t1 ON t0.":RepositoryId" = t1."RepositoryId" AND t0."DREREFERENCE" = t1."HashKey" INNER JOIN "Metadata"."Document" t2 ON t0.":RepositoryId" = t2."RepositoryId" AND t0.":DocKey:0" = t2."DocKey" LEFT OUTER JOIN "Metadata"."ReferenceHash" t3 ON t0.":RepositoryId" = t3."RepositoryId" AND t2."RootParentReferenceHash" = t3."HashKey" LEFT OUTER JOIN "Metadata"."LocationHash" t4 ON t0.":RepositoryId" = t4."RepositoryId" AND t2."LocationHash" = t4."HashKey" LEFT OUTER JOIN "ControlPointMetadata"."Additional" t6 ON t0.":RepositoryId" = t6."RepositoryId" AND t0.":DocKey:0" = t6."DocKey" LEFT OUTER JOIN "Metadata"."ReferenceHash" t5 ON t0.":RepositoryId" = t5."RepositoryId" AND t6."MatchWithinArchive" = t5."HashKey" LEFT OUTER JOIN "Metadata"."ReferenceHash" t7 ON t0.":RepositoryId" = t7."RepositoryId" AND t0."CPROOTPARENTREFERENCE" = t7."HashKey" UNION ALL
    SELECT t0.":Hit", 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, t1."PolicyId", NULL FROM "#DataSet" t0 INNER JOIN "ControlPointMetadata"."DocumentPolicyHold" t1 ON t0.":RepositoryId" = t1."RepositoryId" AND t0.":DocKey:0" = t1."DocKey" UNION ALL
    SELECT t0.":Hit", 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, t1."PolicyId" FROM "#DataSet" t0 INNER JOIN "ControlPointMetadata"."DocumentPolicy" t1 ON t0.":RepositoryId" = t1."RepositoryId" AND t0.":DocKey:0" = t1."DocKey"
    ORDER BY ":Hit", ":FieldSet"

     

    You can copy each of these to SQL Management Studio then run them directly one after another to see the search issued directly against the ControlPointMetaStore DB.

    Take care to run it against ControlPointMetaStore DB.

    In my case I got back the row for the one file that was 0kB in size.

Comment
  • Check in this table that the Filesize column is 0 for your file:

    select * from [ControlPointMetaStore].[Metadata].[Document] WITH (nolock)
    where DocumentName = 'zero k file.txt'

    replace 'zero k file.txt' with the name of file you are wishing to check in metastore


    Also you can use SQL profiler shipped with SQL Server to see the underlying query issued to SQL.

    Within SQL profiler Select the following event to capture:
    TSQL: SQL:BatchCompleted

    Click Show all columns

    Then select Column Filter and Add DatabaseName Like ControlPointMetaStore

    This limits the traffic to just those calls to ControlPointMetaStore database.

    Click Run and then capture yourself clicking the category to display the docs in the listview.

    Then stop the trace.

    You will see a line in the resulting SQL trace that contains 2 lines like below:

    SELECT TOP 25 ROW_NUMBER() OVER(ORDER BY ":SortExpr:0", ":SortExpr:1") AS ":Hit", ":RepositoryId", ":DocKey:0", "DREREFERENCE", "AU_DOCUMENT_FILESIZE_BYTES", "CPROOTPARENTREFERENCE" INTO "#DataSet" FROM (SELECT ROW_NUMBER() OVER(PARTITION BY t1."MatchArchive" ORDER BY t1."MatchArchive") AS ":CombineNumber", t0."RepositoryId" AS ":RepositoryId", t0."DocKey" AS ":DocKey:0", t0."RepositoryId" AS ":SortExpr:0", t0."DocKey" AS ":SortExpr:1", t0."DocKey" AS "DREREFERENCE", t0."FileSize" AS "AU_DOCUMENT_FILESIZE_BYTES", t1."MatchArchive" AS "CPROOTPARENTREFERENCE" FROM "Metadata"."Document" t0 LEFT OUTER JOIN "ControlPointMetadata"."Additional" t1 ON t0."RepositoryId" = t1."RepositoryId" AND t0."DocKey" = t1."DocKey" WHERE t0."RepositoryId" = 1 AND t0."FileSize" = 0) tbl WHERE ":CombineNumber" = 1
    ORDER BY ":Hit";

    SELECT t0.":Hit", 0 AS ":FieldSet", t0.":RepositoryId", t1."Value" AS "DREREFERENCE", t2."DocumentName" AS "DRETITLE", t2."FileExtension" AS "IMPORTMAGICEXTENSION", t2."DocumentStatus" AS "CP_DOCUMENT_STATUS", t3."Value" AS "DREROOTPARENTREFERENCE", t2."OriginalFileExtension" AS "ORIGINALEXTENSION", t0."AU_DOCUMENT_FILESIZE_BYTES", t2."DisplayName" AS "AU_REPOSITORY_TITLE_STRING", t2."AutnIdentifierValue" AS "AUTN_IDENTIFIER", t4."Value" AS "CPLOCATION", t5."Value" AS "CPROOTFAMILYREFERENCE", t7."Value" AS "CPROOTPARENTREFERENCE", t2."DateLastModified" AS "AU_REPOSITORY_MODIFIEDDATE_EPOCHSECONDS", NULL AS "CP_HOLD", NULL AS "CP_POLICYID" FROM "#DataSet" t0 INNER JOIN "Metadata"."ReferenceHash" t1 ON t0.":RepositoryId" = t1."RepositoryId" AND t0."DREREFERENCE" = t1."HashKey" INNER JOIN "Metadata"."Document" t2 ON t0.":RepositoryId" = t2."RepositoryId" AND t0.":DocKey:0" = t2."DocKey" LEFT OUTER JOIN "Metadata"."ReferenceHash" t3 ON t0.":RepositoryId" = t3."RepositoryId" AND t2."RootParentReferenceHash" = t3."HashKey" LEFT OUTER JOIN "Metadata"."LocationHash" t4 ON t0.":RepositoryId" = t4."RepositoryId" AND t2."LocationHash" = t4."HashKey" LEFT OUTER JOIN "ControlPointMetadata"."Additional" t6 ON t0.":RepositoryId" = t6."RepositoryId" AND t0.":DocKey:0" = t6."DocKey" LEFT OUTER JOIN "Metadata"."ReferenceHash" t5 ON t0.":RepositoryId" = t5."RepositoryId" AND t6."MatchWithinArchive" = t5."HashKey" LEFT OUTER JOIN "Metadata"."ReferenceHash" t7 ON t0.":RepositoryId" = t7."RepositoryId" AND t0."CPROOTPARENTREFERENCE" = t7."HashKey" UNION ALL
    SELECT t0.":Hit", 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, t1."PolicyId", NULL FROM "#DataSet" t0 INNER JOIN "ControlPointMetadata"."DocumentPolicyHold" t1 ON t0.":RepositoryId" = t1."RepositoryId" AND t0.":DocKey:0" = t1."DocKey" UNION ALL
    SELECT t0.":Hit", 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, t1."PolicyId" FROM "#DataSet" t0 INNER JOIN "ControlPointMetadata"."DocumentPolicy" t1 ON t0.":RepositoryId" = t1."RepositoryId" AND t0.":DocKey:0" = t1."DocKey"
    ORDER BY ":Hit", ":FieldSet"

     

    You can copy each of these to SQL Management Studio then run them directly one after another to see the search issued directly against the ControlPointMetaStore DB.

    Take care to run it against ControlPointMetaStore DB.

    In my case I got back the row for the one file that was 0kB in size.

Children
No Data
Related Discussions
Recommended