How to add lookup tables in the Advanced Reporting Tool

The simplified SIEM-system Advanced Reporting Tool allows you to add custom lookup tables to customize reports in accordance with the needs of the enterprise. Consider how this can be done.

Today we will talk in detail about how you can customize reports in the simplified SIEM-system Advanced Reporting Tool (ART) using look-up tables. This may be necessary in cases where you need to add certain characteristics of the analyzed corporate network, for example:

• Bind IP addresses to the structural units of the enterprise and remote offices or geographical location
• Highlight corporate and non-corporate applications
• Indicate the endpoints of VIP employees (manual , accounting, etc.)

To do this, we need to provide the SIEM system with a custom lookup table prepared taking into account the following aspects:

• File format: CSV with a comma delimiter
• Without special characters
• First row with column names in English
• Maximum size: 2 GB
• Column with primary key
• Additional columns are allowed

As an example, consider the situation when we decided to organize all executable files into categories with reference to the manufacturer. For this we will use the file Executable_Category.csv . The following is a snippet of the contents of this file:



The Service column will act as a column with a primary key.

So let's get started.

If you want to simultaneously perform these actions in parallel, you can do this in the product demo console, which requires only Google Chrome or Mozilla Firefox.

URL: demologin.pandasecurity.com
Login: DRUSSIAN_FEDERATION_C16@panda.com
Password: DRUSSIAN # 123

After entering the Panda Adaptive Defense product demo console, in the upper part of the console you need to click on the Advanced Search button to go to SIEM -Advanced Reporting Tool.

Having entered the ART interface, click on the Search icon , and on the page that opens, click on the Lookup Management tab .



On the page that opens, a list of user reference tables will be displayed (it may be empty). In the upper right corner click on the New Lookup button to load our table.



In the form that appears, specify the name of the table ( Table name ) and select the file ( Choose File ). After checking the file, a list of file columns appears, in which you must verify that the Is Key label is specified for the Service column . Click the Upload button .



Click the Upload button and wait about 10 minutes for the table with lookup tables to refresh.



To download an updated version of this table, delete it or check its structure, you can click on the options button (...).



Now let's move on to the Ops table in ART: click on the Search icon.

In the right section click on Ops.



On the page that opens, in the control panel at the top, click on the Create Column : icon

and select the Filter Data tab .

Now let's select those entries that end with .exe in the ParentPath field . To do this, follow these steps:

• in the Operation drop-down list, select Ends with
• click New argument
• in the String drop-down list, select the value parentPath
• click the New argument button again
• next to the Sufix argument , click the icon to edit the entry and enter the value .exe



Now we need to extract the name of the executable in ParentPath into a new column called ExecutableName .

To do this, click on the Create Column icon, fill out the form as follows:

• Specify the field name
• Select the Substitute function
• Add an argument: parentPath field
• Add an argument: regular expression(. * \\) (? =. * (\. \ w *) $ | (\ w +) $) .
For example, this regexp extracts “chrome.exe” from “PROGRAM_FILESX86 | \ Google \ Chrome \ Application \ chrome.exe” into the ExecutableName column.
• Click on the Create column button



And now let's create another field ExecutableName2Lower , because the query we want to make is case sensitive. To do this, specify the following parameters in the form for adding a column:

• Field name
• Select the Lower case function
• Add an argument and select ExecutableName in the drop-down list
• Click the Create column button



As a result, you should see something similar:



If the process described above seems a little cumbersome to you, then you can click on the Toggle Query Editor icon and write the following query in the query editor:

from oem.panda.paps.ops
where endswith (parentPath, ".exe")
select subs (parentPath, re ("(. * \\\\) (? =. * (\\. \\ w *) $ | (\\ w +) $)"), template ("")) as ExecutableName,
lower (ExecutableName) as ExecutableName2Lower



As a result, you will get the same result provided that you loaded the lookup table correctly (which we talked about at the very beginning).

Click the Create Column icon again :



Now we will insert the Category field from our lookup table for those entriesequal to the corresponding value of the Service field in the lookup table, in a new column called Software Category .



Now you will see a new column in the query results:



In addition to this new column, you can use the Data Extract function to see the total distribution of the results into categories. To do this, hover the mouse over the down arrow icon in the upper right corner of the Software Category column name . A value of Null indicates that this entry was not found in the lookup table.



Now group Software Category and ExecutableName . To do this, follow these steps:

• Highlight these columns
• In the control panel, click on the Group button.



Then add a column using the corresponding button in the control panel and the Aggregate function tab in the form that opens:



As a result of these steps, you should get something like the following:



If the process described above seems a little cumbersome , then you can click on the Toggle Query Editor icon and write the following query in the query editor:

from oem.panda.paps.ops
where endswith (parentPath, ".exe")
select subs (parentPath, re ("(. * \\\ \) (? =. * (\\. \\ w *) $ | (\\ w +) $) "), template (" ")) as ExecutableName,
lower (ExecutableName) as ExecutableName2Lower
select `lu / Executable_Category / Category` (ExecutableName2Lower) as` Software Category`
group every 15s by `Software Category`, ExecutableName
every 0
select count () as count



And finally, with this newly added counter we can do something more visual: for example, a Voronoi diagram. Click on the Setup button in the control panel and select Charts → Diagrams → Voronoi tree map



Now drag the Sofware Category and ExecutableName to the SIGNALS panel in the settings of the Voronoi diagram.



Also drag count to the VALUE panel .

And ... done!



We hope that this will help you to customize our simplified SIEM-system Advanced Reporting Tool to the specific needs of your enterprise.