Devo Foundations - Exercise 5.1 - Using lookups

  • 31 January 2023
  • 6 replies
Devo Foundations - Exercise 5.1 - Using lookups
Userlevel 4

Firewall information is very relevant to Arcadia. However, sometimes they are not able to make sense of the real information hidden behind IP addresses and abstract categories.


This is where lookups come in: they take data enrichment to a whole new level.

For this exercise, we are going to rely on available lookups already existing in the devo_101 domain:

  • userInfo
  • pa_threat_categories

Go to Data search Lookup management and double check that you can access them. Otherwise, you won’t be able to complete this exercise.

The overarching theme here today is that we want to focus on where the connections that pass through the firewall are reaching: the destination IPs. Are these IPs trusted sites? Dangerous sites? Should we worry? To what extent? Can we quantify that concern? Let’s go.

  • Let’s begin by querying the union table firewall.all.traffic from the finder.
  • Set the time range to the last 24 hours.
  • As we are already used to, it’s best practice to start with filtering. Let’s get rid of private IPs in the dstIp field. We can’t make anything of private IPs, so we need to focus just on public ones.
  • Now, we want to correlate our firewall table with two external files that we have already uploaded into Devo as lookups. We’ll use them to enrich two fields of our table, the source IPs and the destination IPs. This will help us to know more about who’s doing what.
    • We are enriching srcIP adding the user ID.
    • We are enriching dstIp by matching IPs with known threats.
  • There is a LINQ operation for calling lookups: `lu`. Note the backtick symbol.

Note: `lu` is the right syntax for the search window and its LINQ query code editor. If you want to explore the Devo API framework, bear in mind that some operations differ in syntax. The lookup operation is one of them, and its API syntax does not use backticks. Learn more.

  • Click on the create field button, name this new field as “UserID”.
  • Let’s look for our first lookup table as if it was a regular, built-in LINQ operation: by clicking on the operation drop-down menu.
  • Scroll down until you start to see operations labeled as custom_lookup. You might simply type “userid” to narrow down the list.
  • Use srcIp as the argument. Click on Create field to finish the first lookup.
  • Scroll to the right to check the new field. What can you make of it?
  • Now repeat the process for the second lookup. Add a new field and give it a proper name (for example, “Threat”.)
  • Choose the custom_lookup operation “threat_category” and use dstIp as the argument.
  • Remember that it is usually best practice to filter again after enrichment. Get rid of null values from the new field “Threat”.
  • As if often the case when the output consist of IP addresses, it makes sense to geolocate this information.
  • Calculate the latitude and longitude based on the dstIp field.
  • The enrichment phase is done: we have created 4 new fields.
  • Group by these 4 fields.
  • Aggregate with the count operation.
  • What is the threat category with highest frequency?
  • Click on Additional tools and choose a Diagram called Voronoi. Add “Threat” as the signal and the field resulting from the count operation as the value.



The following LINQ reproduces these steps:

from firewall.all.traffic

  where ispublic(dstIp)

  select `lu/userInfo/userid`(srcIp) as UserID,

    `lu/pa_threat_categories/threat_catagory`(dstIp) as Threat

  where isnotnull(Threat)

  select mm2latitude(dstIp) as Dstlat

  select mm2longitude(dstIp) as Dstlon

  group by Dstlat, Dstlon, UserID, Threat

  select count() as count



You still want more? Then, we have 2 extra things for you:

Did you notice the particular syntax of a LINQ lookup operation. Check one of the lookups we used:

 select `lu/userInfo/userid`(srcIp) as UserID

There are a few takeaways that you should know before leaving this exercise:

  • The whole route is enclosed by backticks:
  • In this example, “userInfo” is the name of the lookup table, as it can be seen in the lookup management tab of the Devo Platform. Note that this name also appeared in the first screenshot of this exercise, grouping all available fields from this lookup table.
  • And you guessed right: userid is one of the many fields contained in the “userInfo” lookup table. Both items in this structure are passed onto the query. It resembles a route or a tag structure, as in other Devo tables.

Now with the other task. Voronoi widgets can plot several signals or keys at the same time. Let’s try that:

  • Just before the grouping, use a MaxMind2 geolocation operation of your liking (i.e., the country, the city...). 
  • Now, group again and include this new field.
  • Add both “Threat” and your new field as signals in the voronoi widget. Did it change?
  • Now, open the settings of the widget and drag the “Threat” field to the right so that it rests in the second position. Notice how this changes how fields are plotted in the chart.

As always, make sure you close the search when you are finished.

6 replies

Userlevel 2

Hi again Alex,

Sorry to bother you once more,

When I try to add the first lookup, the data table is not capable of retrieving any events. I tried repeating the process from the beginning but even after a long time (15 minutes or so) it always ends up like this: 



I checked previously that the lookup was available:


I think the field creation process was correct too:


I don’t know what’s bothering Devo :)

Thanks in advance

Userlevel 4

Hello Minion,

The firewall.all.traffic is a union table, and those take longer to load. I tried now and even though it took some time, it worked. Can you try again and send me a private message with the result? Thanks and sorry for the inconvenience.

Userlevel 2

Hi Alex,

This time was the original table firewall.all.traffic the one that took forever to show any event. Af afterwards, when correlating with “threat_category” lookup. It has happened to me several times before, but events always appeared when I was just about to ask you :D

I think data gets “stuck” at some point, and then they all come at once, I don’t know if this makes sense.



Userlevel 2

Finally, events came in at 17:48 :s

I understand some delay can be expected, but I don’t think this is working ok.

Anyway, continuing with the exercise, I see that if we filter null values in “Threat” field, we’ll have nothing:


Maybe the correlation with the lookup didn’t go well...or maybe I misunderstood something.

Thanks in advance.


Userlevel 4

Hello Minion! What you are experiencing is definitely not the normal behavior and I am unable to replicate. Something is interfering here for sure, but for some reason it’s not happening to me. I entered the full query, with the two lookup calls, and it took about 10 seconds to return 3 not-null results for the last hour. Can you try again and mail with the result so that we don’t get this area full of troubleshooting messages? Thank you and sorry for the inconvenience.

Userlevel 2

Hi Alex,

I’ll continue with the training exercises and if it keeps happening I’ll contact the email address you gave me.