Pro Tip: Creating Lookup Tables using Investigations app

[Tiit_Hallas]

Lookup tables in Dynatrace Grail® are a simple feature with serious leverage. They give your team a lightweight, maintainable way to enrich business context to your observability data at query time so your raw telemetry stays clean and enrichment remains flexible. This blog post will show you three amazing ways on how to manage your lookup tables using Investigations.

 

Uploading a new Lookup table from your computer

When in the Investigations view, you can expand the left pane that holds all your Investigations. If the left pane isn't shown, you can expand it using the expand button next to the Investigations' title.

To manage your Grail files, we have introduced a new tab there called "Grail Storage". Choosing that will show you all available lookup tables in Grail that you have access to.

At the very bottom you'll notice a button which enables you to create a new Lookup tables from a file. Choosing this option will open a simple and intuitive wizard that will guide you through the Lookup table creation process with the assistance of DPL Architect. The steps to create the table are as follows.

1. Upload a file

Choose a file from your disk and upload the raw data to use as the data source for the lookup table. Lookup tables can currently hold up to 100MB of data with 100000 records all together.

2. Define lookup data

With the help of DPL Architect you can define the DPL pattern for your lookup table.

 In the Match preview editor tab you can see your raw data loaded from the file which is coloured accordingly to indicate the parts of the data which is covered by your pattern. The alternating colours display help you to distinguish between extracted records.

In the Results tab, you can see what the lookup table will look like if the current pattern would be used. Similarly to the parse command, the lookup table will contain only these matchers from your DPL pattern which have an extraction name in place. 

In the lookup field you can define the field whose value identifies a record in the lookup data. 

PS! the lookup field values in the table are unique, i.e. if there are multiple records with the same value, the records will be deduplicated, leaving only the record that occurs first with that key.

3. Define Name and Path

If you're happy with the lookup table structure, you can continue with naming your lookup table. In the third step you can define the unique path for the table which is later used to access the table.

Additionally you can add a title for the table to identify the table faster and add a description to it.

After creating the table it will appear in the side pane under the Grail storage tab to be used comfortably for your investigations.

 

Using lookup tables in Investigations

Lookup tables can be used to enrich your observability data in Grail with commands like lookup, join or append, depending on your use-case. To speed up such enrichments during queries, Investigations enables quick access to query enhancement from lookup tables.

By opening the lookup table menu in the left pane, you see the commands in Grail that are currently supported by Investigations, starting with Load  that enables you to fetch the entire content of the lookup table to more complex commands like lookup, that merges the lookup data to your observability data.

Investigations makes the use of lookup command much faster: simply choosing Lookup from the menu lists all the available field names currently available in your results table. Choosing the relevant field name from the menu will automatically construct a lookup command with the correct path to the lookup table and populates the sourceField and lookupField so you don't have to. The finalised command is added to your DQL query for you to review and execute the query if you're satisfied with the result.

 

Creating lookup tables from query results

But what if you've created a statistical analysis on your data and would like to use this data as a lookup table in your next investigative steps? For example, you've just created a query with all usernames and their failed login counts from the past 3 months and would like to create a lookup table from this data to be used in follow-up queries. 

Simply choose the result records relevant to you (either one-by-one by holding down the Cmd (Ctrl for Windows) key, range by holding down the Shift key or select all the values from the column menu), right-click on the selected records and choose Create Lookup Table from the context menu. 

You can choose the fields to keep for your lookup table, select the lookup field from the dropdown list and the lookup table will be created quickly and comfortable to be used as described in the previous section of this blog.

Summary

Go ahead and give it a try in either the Playground of Dynatrace or your own Dynatrace environment. To learn more about Investigations go to our documentation or try it out for yourself in Dynatrace playground.