The Lookup Tables feature allows administrators to add external data to an existing data table by correlating the table with a data file that contains the new fields to be appended to it (lookup file).
A common use case is to group computers or users into categories or profiles. To do that, you have to generate a lookup file with the list of computers and users as the first column, and the categories or groups in the subsequent columns.
A lookup file must meet the following requirements:
It must be a CSV file with the following characteristics:
- The first column is the key to be used to perform the join operation with an existing column in the table to extend.
- The other columns will be the values that can be used to extend the table.
- The values in the first column (the key) must be unique. The first column cannot have duplicate values. Otherwise, the process will be aborted.
- The columns must be separated by the comma character.
- Do not include special characters (quotation marks, accents, apostrophes, etc.).
- The file must have a maximum size of 2 GB.
- You can add as many columns to the first one (the key) as you want. For example: category1, category2. Or category, country. Or group1, group2. Or flags: active (yes/no), corporate (yes/no).
The tool to use to upload this file (the lookup file) can be found in the Search area:
Suppose you have a CSV file with the following information:IP,StreetAddress,Building,Floor,Office,Brand,Model22.214.171.124,Mainstreet 12,A,4,12,Canon,ZX15085.197.192.19,Mainstreet 13,A,4,14,HP,MJF55126.96.36.199,Mainstreet 14,A,5,44,Canon,CC23188.8.131.52,Mainstreet 15,A,5,23,HP,Laserjet 11184.108.40.206,Mainstreet 16,A,7,5,HP,LT6610.0.73.16,Mainstreet 17,A,8,6,Canon,BigPriter3220.127.116.11,Mainstreet 18,A,9,12,Brothers,Yeah64
To upload the lookup file, go to Search
, Lookup Management
(in the top right-hand corner of the screen). Click New Lookup
and fill out the required fields:
The file will be uploaded to the system in a few minutes and you'll be able to add the columns to an existing table. To do that, open the table to extend as if you were going to make a query. Select Create Column
, and Custom
Opening the operation drop-down menu will display as many functions as secondary columns were included in the uploaded lookup file.
Select one of them. The result will be a new column, whose name will be the same as that of the column in the lookup file, added at the end of the table.
Repeat this process for the remaining columns. The new columns will admit the same operations as those in the original table: they can be filtered, grouped, included in charts, etc.
We advise that you keep a copy of the last uploaded lookup file for each Lookup table that you are managing, as you will not be able to view the contents of the table once it has been uploaded to Advanced Reporting Tool.