In our first blog on VOX, we discussed the basic options of custom reporting, and now we’ll dive deeper into the Advanced Options.
Customers often use the Advanced Options tab when creating custom DQL Reports and DQL Templates. Advanced Options allow you to run SQLite commands to access and manipulate the DQL output database that gets created when your DQL Script executes.
To explore additional resources for SQLite functions, you can refer to the following SQLite links:
- https://www.sqlite.org/lang_corefunc.html
- https://www.sqlite.org/lang_aggfunc.html
- https://www.sqlite.org/lang_datefunc.html
Select the Advanced Options tab after creating your custom DQL Query or the DQL Template you are using.
Once in Advanced Options, check the box beside Run SQL commands on generated DQL output database. If you select View DQL output database schema, this shows you the table your DQL Script creates and the column names.
You will notice that some of the column names may not be very user-friendly. For example, absname returns the full file path. Let’s use Advanced Options to create column names that are more intuitive for our end users. (Please note that you can also create more intuitive column names within Power BI itself.) Now let’s specify the folder location where we want our report exported.
At the bottom of my DQL template for PSTs, there is a section called Advanced SQL Queries.
The code in this script will not execute, because it is commented out, being enclosed with /* and */. We will copy the script found inside /* and */, and paste it into Advanced Options. The script within Advanced Options performs many useful tasks:
- Separates data from each column with a | instead of a comma. We do this because some filenames include commas.
- Names our report psts.csv and exports it out to the C:\DIReports folder
- Makes our column names more intuitive, changing the original names listed on line four of the script below to the more intuitive names on line three. (e.g. instead of absname, our report will display the column name Full_Path instead).
This is a simple example of how customers can use the Advanced Options feature. Other examples include:
- Categorizing data into buckets (e.g. last accessed, last modified) for different age ranges 0-3 years, 3-7 years, 7 years, or more.
- Converting a file’s size in bytes to megabytes or gigabytes
- Combining information from multiple tables into one report
- Advanced grouping and string functions
- Advanced table joins or self joins to extract complex analytics
In our next blog, we will discuss how to create a Microsoft Power BI Desktop Template and how to use it with your Data Insight reports. In the meantime, you can explore on Gartner Peer Insights why customers are calling Veritas Data Insight a "really good end-to-end analytics and risk identification tool." Veritas Data Insight was named a Gartner Peer Insights Customer Choice in the fall of 2020.
