Search <book_title>...

Veritas Data Insight User's Guide

Last Published: 2018-09-04

Product(s): Data Insight (6.1.2)

About DQL query templates

Data Insight provides you with built-in queries to help you write complex queries. At the time of creating a DQL report, you can select any of the built-in queries, and modify the content to suit your particular reporting needs. Additionally, you can create your own queries and save them to be used later as templates.

See Creating custom templates for DQL queries.

See Creating a report .

Data Insight provides the following default query templates:

Table: Data Insight Query Language templates

Category	Name	Description
Data Management	Folder creation details	The query fetches the details about the creator and the date of creation for every first-level folder in the environment.
Data Management	All files with a specific extension	The query fetches details of files with specific extensions in your storage environment. You can use this query to find, for example, all media files. The query helps you find data that does not comply with your organization's policy, and reclaim storage on your device. Modify the template to add other extensions to get results that suit your needs.
Data Management	Capacity by extensions	The query and the provided advanced SQL queries help in identifying the storage capacity used by specific file extensions.
Data Management	Files in a confidential folder	The query lists all the files under a specified folder in a share. In this example, the folder has the word "confidential" as part of its name. Modify share name and folder name search criterion to get results that suit your needs.
Data Management	Files with undefined file groups	The query lists all the file extensions under a specified share that are not defined in Data Insight file groups. You can analyze these files and update the file groups for better reporting of consumption patterns. Use the advanced query to narrow down the results to specific extensions.
Data Management	Folder summary by file type	The query fetches the folder level summary of counts and size used by different file-types in a share. Only the files which are direct member of a folder will be used for computation. Only those file-types that are part of Data Insight file groups will be listed. For all other file types, it will be combined under empty "" file type. Modify the share name to get results that suit your needs.
Data Management	Stale file list	The query lists the files that have not been accessed for the past one year. You can use this report to make better archiving decisions. Modify the duration and the share name to get the results that suit your needs.
Data Management	Storage usage by user attribute	The query lists the consumption of storage on NAS devices based on the user attribute, department. The consumption is determined by calculating the owner of the file and mapping the owner to the corresponding department. Modify the filer name and user attribute to get the results that suit your needs. Additionally, you can modify the owner calculation by specifying access dates and order of the policy for computing the data owner.
Data Management	Duplicate Files in Share	This query along with the advanced SQL queries help in identifying duplicate files within a share by name, by size of files, and by modified time. Additionally, you can specify conditions to match copy string in file name to further tune the advanced SQL. These options are part of commented portion in Advanced SQL query in template.
Data Management	Stub Files	This query lists all stub files assuming that stub size equals 4 kb.
Data Management	Archived Files	The query lists all such archived files with the specified attributes. The attribute metadata is stored by the file system and can be used to find out the amount of reclaimable storage and take decisions about removal or archiving.
Risk Analysis	Sensitive files on a filer	The query lists all files which are marked sensitive by the Symantec Data Loss Prevention (DLP). These files can be further analyzed and acted upon as per organization's security measures. If DLP is configured and incidents are reported against a configured report ID, this report lists the sensitive files automatically. Alternatively, you can import sensitive file information to Data Insight using a CSV file. Modify the device name with valid filer name in your environment to get the results that suit your needs.
Risk Analysis	Sensitive files that are active	The query lists all the active sensitive files that violate a certain DLP Policy. In addition to file details, it also provides you the information on the number of active users on the files. Modify the activity period and policy to get the output that is valid for your environment.
Risk Analysis	Sensitive files with violated policies	The query lists all the sensitive files in a share and the associated DLP policy that are violated. Modify the share name to get the output that is valid for your environment.
Risk Analysis	Department-wise summary of risky behavior	The query fetches the summary of the users belonging to other departments who have assessed sensitive files owned by a specific department. For example, you may want to know the users belonging to any non-HR department accessing files owned by the HR department. This query computes the potentially risky behavior on a specific share during a specific time range. The files are classified as being sensitive by DLP policies. Note that sometimes the report may flag legitimate accesses as risky behavior. Use your discretion to eliminate such false alarms. Modify the share name, time range, DLP policy string, user department attribute, and department name in the query to get valid results in your environment.
Risk Analysis	Recent suspicious activity	This query fetches the details of the inactive sensitive files that were accessed recently. For example, it can get the list of sensitive files that were inactive for last year but were accessed in last 5 days. It also provides you information about the person who accessed the file most recently. The sensitive file information is fetched from DLP. Alternatively, you can import sensitive file information to Data Insight using a CSV file. Modify the recent access time range and inactivity time range in your environment to get results that suit your needs.
Risk Analysis	Last Accessed - Time Range	The query lists all files that are accessed between 1 year and 3 years.
Risk Analysis	Groups contributing to high risk	The query finds out common groups across users who have risk score > 90 and who are contributing to the high level of permissions. Use the query to analyse whether the users should be part of the group or the excessive permissions to the group should be reconsidered.
Risk Analysis	Risky Users Outlier	The query gives the count of high-risk users based on their custom attributes. The users are listed in the ascending order of their risk score. Use the query to find any unusual user with a risk score > 90. Typically, the high-risk users may include service or administrator accounts due to the high level of permissions assigned to these accounts.
Forensics	Share access details	This query provides the audit details on a share for a specified time range. Modify the time range and share name to get results specific to your environment.
Forensics	User access details	The query provides the details of accesses by a specified person on a share during a specified time range. Modify the person name, time range, and share name to get the results to suit your needs.
Forensics	Top users of sensitive files	The query lists top ten users who have accessed sensitive files in your storage environment within a specified time-range. Modify the time range to get valid result in your environment.
Forensics	Folders with maximum access counts	The query fetches the list of top ten folders that are accessed in a share during a specific time range. Modify the share name and time-range to get valid result in your environment.
Forensics	Users with maximum access counts	The query fetches the list of top ten users who have accessed a share during a specific time range. Modify the share name and time-range to get valid result in your environment.
User / Group Management	Group membership details	The query provides the details about a specified security group, its member groups, and users in the group. Modify the group name and domain name to get the results that are valid for your environment.
User / Group Management	Deleted or disabled groups	The query lists all the disabled or deleted security groups in the environment.
User / Group Management	Deleted or disabled users	The query lists all the disabled or deleted users in the environment.
User / Group Management	Groups with disabled users	The query lists all the groups with disabled users in the environment.
User / Group Management	Empty groups	The query provides a comma-separated list of security groups, their details and SIDs of its member users. To list the empty groups for clean-up, execute following query on the output: *SELECT FROM groups WHERE memberusers_sid = "**
User / Group Management	Circular groups	The query lists any security groups in the environment which are members of each other forming group loopings.
Data Protection	Open shares	The query lists all paths in your environment that have excessive permissions along with the reasons for their openess.
Data Protection	Shares with permissions to Everyone group	The query lists shares in the environment that have permissions to the "Everyone" group.
Permission Management	Paths with direct permissions to disabled users	The query provides the details about the paths that have explicit access to disabled users.
Permission Management	Box folders owned by a given user	The query lists all box folders owned by a given user. It excludes all shared folders.
Classification	Files to send for classification	Creates a report of all files that are accessible to more than 1000 users. Use the DQL report to send file paths in the output for classification.
Classification	Classified files with a specific extension	Creates a report of all files with a specific extension (for example, PST) and a specific tag name (for example, US-PII). You can either use the query to identify tags associated with specific files or to push these files to Enterprise Vault for archiving.
Classification	All PII files	Creates a report of all files that are tagged as Personally Identifiable Information (PII). These are files that may contain sensitive information such as Social Security, credit card, and drivers' license numbers.
Classification	Classify active users files	Creates a report listing all files that have been accessed by users identified as active by Data Insight. You can then use this report to submit these files for classification.
Classification	Classified files summary	Creates a report that summarizes all files that have already been classified.
Ransomware	WriteRename sensor	The query lists all the write and rename activities performed in the data source within 24 hours. An SQL query is used to fetch the per user activity (write) count performed on the file before it was renamed. If the activity count is higher than the configured threshold, only then a notification is sent to the users configured on Reports > Edit > Notifications. See About Data Insight custom reports. for information about how to configure the threshold value. Note: Do not modify the query or table names in the query as it might interfere with the notification process.
Ransomware	Activity by rename extensions	The query fetches the count of files that are renamed by per user, and have unique file extensions. For example, the query extracts the number of files that are renamed, and which have the extension as docx, pdf, xlsx.
Ransomware	Rename count for parent folders	The query fetches the top-level directories in the share, site collection, or equivalent, and the number of write and rename activities performed in each of these repositories by per user. Use this report to detect malicious activities performed on the parent folder in a share or equivalent.
Ransomware	Activity by create extensions	The query lists all the files that are created in the last 24 hours by per user. Use this query to identify files created by an infected or risky user.
Ransomware	List file patterns	This query lists the files that contain a specific string in the file name. For example, ransomware appends a unique extension to the encrypted files. With this query, you can fetch all the files that contain the specified extension.
Ransomware	Trace malicious executable	The query lists the duplicates of the ransomware executables residing on your system.