Examples

This section provides examples of SQL statements that can be used to query Enterprise Recon data via the ODBC Reporting feature.

Examples:

1. Retrieve Current State of Sensitive Data Matches
2. Download Scan Summary Reports by Scan Schedule Label
3. Download Captured Match Samples Across Scans for Specific Target
4. Metadata Information for Sensitive Data Locations
5. Sensitive Data Locations by File Format

Retrieve Current State of Sensitive Data Matches

Retrieve the current count of sensitive data matches by data type, Target Location, Target and Group.

Required data tables:

• DATA_LOCATION_MATCH
• DATA_TARGET
• DATA_MATCH_SEVERITY
• DATA_MATCH_TYPES
• DATA_LOCATION
• DATA_GROUP

Sample SQL statement:

SELECT F.LABEL as `GROUP`, B.LABEL AS `TARGET`, E.LOCATION as `LOCATION`, C.LABEL as `MATCHSEVERITY`, D.LABEL as `DATATYPE`, A.QUANTITY as `MATCHCOUNT` FROM DATA_LOCATION_MATCH A, DATA_TARGET B, DATA_MATCH_SEVERITY C, DATA_MATCH_TYPES D, DATA_LOCATION E, DATA_GROUP F WHERE F.GUID = B.GUID AND A.TUID = B.TUID and A.CUID = D.CUID and A.SEVID = C.SEVID and A.LOCID = E.LOCID and A.TUID = E.TUID

Sample data:

Download Scan Summary Reports by Scan Schedule Label

Download the summary reports for a specific Target according to the scan schedule label. Information available in this scan summary report includes the number of matches per data type enabled for the scan, the number of scanned locations, scan root path, and more.

Required data tables:

• DATA_TARGET
• SCANREPORT_SUMMARY
• DATA_MATCH_TYPES

Sample SQL statement:

SELECT A.LABEL as `TARGET`, B.REPORTID as `REPORTID`, C.LABEL as `DATATYPE`, B.LABEL as `SCANLABEL`, B.ROOT as `ROOT`, B.START as `START`, B.END as `END`, B.FAILED as `FAILED`, B.STOPPED as `STOPPED`, B.LOCATIONS as `LOCATIONS`, B.BYTES as `BYTES`, B.INACCESSIBLE as `INACCESSIBLE`, B.MATCHES as `MATCHCOUNT`, B.SAMPLES as `SAMPLECOUNT` FROM DATA_TARGET A, SCANREPORT_SUMMARY B, DATA_MATCH_TYPES C WHERE B.LABEL LIKE '%16JUL%' AND B.TUID = 8283249956251157105 AND A.TUID = B.TUID AND B.CUID = C.CUID

Sample data:

Download Captured Match Samples Across Scans for Specific Target

Download the first 1000 match samples captured across all scans for a specific Target. Match samples are available by data type per location, per scan.

Required data tables:

• DATA_GROUP
• DATA_TARGET
• DATA_MATCH_TYPES
• SCANREPORT_SAMPLE

Sample SQL statement:

SELECT DATA_GROUP.LABEL as `GROUP`, DATA_TARGET.LABEL as `TARGET`, SCANREPORT_SAMPLE.REPORTID as `SCAN_REPORTID`, SCANREPORT_SAMPLE.START as `SCAN_START`, SCANREPORT_SAMPLE.END as `SCAN_END`, SCANREPORT_SAMPLE.LOCATION as `LOCATION`, DATA_MATCH_TYPES.LABEL as `DATA_TYPE`, SCANREPORT_SAMPLE.SAMPLE as `MATCH_SAMPLE` FROM DATA_GROUP, DATA_TARGET, DATA_MATCH_TYPES, SCANREPORT_SAMPLE WHERE SCANREPORT_SAMPLE.TUID = 8283249956251157105 AND SCANREPORT_SAMPLE.LOCATION LIKE ‘MariaDB Server%’ AND DATA_GROUP.GUID = DATA_TARGET.GUID AND DATA_TARGET.TUID = SCANREPORT_SAMPLE.TUID AND DATA_MATCH_TYPES.CUID = SCANREPORT_SAMPLE.CUID LIMIT 1000

Sample data:

Metadata Information for Sensitive Data Locations

Get an overview of metadata types for sensitive data locations for a specific Target.

Required data tables:

• DATA_LOCATION_META
• DATA_META
• DATA_LOCATION
• DATA_TARGET

Sample SQL statement:

SELECT LM.TUID as `TARGETUID`, T.LABEL as `TARGET`, LM.LOCID as `LOCATIONID`, LOC.LOCATION as `LOCATION`, LM.METAID as `METAID`, M.LABEL as `METALABEL` FROM DATA_LOCATION_META LM INNER JOIN DATA_META M ON LM.METAID = M.METAID INNER JOIN DATA_LOCATION LOC ON LOC.TUID = LM.TUID INNER JOIN DATA_TARGET T ON T.TUID = LM.TUID WHERE LM.TUID = 8283249956251157105

Sample data:

Sensitive Data Locations by File Format

Get an overview of the common file formats or content types where sensitive data was found for a specific Target.

Required data tables:

• DATA_LOCATION_CONTENT_TYPE
• DATA_CONTENT_TYPES
• DATA_LOCATION
• DATA_TARGET

Sample SQL statement:

SELECT LC.TUID as `TARGETUID`, T.LABEL as `TARGET`, LC.LOCID as `LOCATIONID`, LOC.LOCATION as `LOCATION`, LC.CTID as `CONTENTID`, C.LABEL as `CONTENTTYPE` FROM DATA_LOCATION_CONTENT_TYPE LC INNER JOIN DATA_CONTENT_TYPES C ON LC.CTID = C.CTID INNER JOIN DATA_LOCATION LOC ON LOC.TUID = LC.TUID INNER JOIN DATA_TARGET T ON T.TUID = LC.TUID WHERE LC.TUID = 8283249956251157105

Sample data: