Devo Foundations - Exercise 4.4 - Analyzing firewall data

  • 30 January 2023
  • 5 replies
Devo Foundations - Exercise 4.4 - Analyzing firewall data
Userlevel 2

Arcadia logs each firewall technology into its specific table. However, Devo Platform provides a union table that puts together all firewall data into one big table. This table is firewall.all.traffic.


Union tables usually contain huge amounts of information, so let’s try to build a search that starts with simple operations that make sense to us.

  • Open the firewall.all.traffic data table.
  • Set the time range to the last 24 hours.
  • Locate the dstIp field. It logs destination IP addresses for the connections.

  • We want to focus just on public IP adresses, as we can’t really infer anything at this point from private IP addresses.

  • There’s a operation exactly for that. Click on the Filter button and look for a function for filtering public IPs. Use dstIp as the argument.
  • Now we have valid and relevant geolocation information. It is time to enrich clean data with some human-readable information.
  • Click on the Create field button for adding enriched fields.
  • Let’s add geolocated information by using the MaxMind2 Geolocation services. Use the following operations:
    • Get the city out of dstIp with the mm2city operation.
    • Get the region/state out of dstIp with the mmregionname operation.
  • Now group disregarding time and just using City as the argument.
  • Almost there! We have done the filtering, the enrichment, and the grouping. It’s time for aggregating our data.
  • For this case, let’s get the total count of entries for each grouping. Click on the Aggregation button and select the operation from the drop-down list. This operation does not need an argument.
  • In addition to that, let’s also calculate the sum of total bytes. Use the bytesSent field as the argument.

Spoiler alert: Solution

The LINQ equivalent to the operations we did is:


from firewall.all.traffic
  where ispublic(dstIp)
  select mm2city(dstIp) as City,
    mmregionname(dstIp) as Region
  group by City
  every -
  select count() as count,
    sum(bytesSent) as bytes_sent



Still here? Then, here’s a list of extra things you can do.

  • Go back to the enrichment phase and use mm2country operation to add also the country information. Arcadia was originally from Greece, and some people are interested in what’s happening there. You can either use the path of operations and add the operation by using the GUI as you did or adding just another LINQ select clause. Try to guess how the syntax would be. You would need to change the grouping argument and add the new country field as another grouping key. Fields that are not used as groping keys are excluded from view after grouping.
  • Use the quick filtering tool that appears when hovering over a field to check if Greece appears in the list. Type “GR”. How many events are getting there?
  • Disregard the previous filter and, for the last item in this exercise, let’s check some visual representation of these data.
    • Go to Additional tools, then Diagrams, then select Pie layered chart.
    • This chart needs a number of signals and one numeric value. Add the Country and City fields - in that order as signals. Then add the bytes_sent field (the result of the sum operation) as the value.
    • The outer region represents the region. Which is the largest? You see a lot of null values, right? This is because we didn’t filter again after enrichment, which is best practice. Feel free to backtrack and use the isnotnull filtering operation after the enrichment.
  • You might want to save this query. When you are ready, close the query (this is also best practice.)

5 replies

Userlevel 1

Another good one, Played a lot with Linq queries and its beautiful.

Userlevel 3

mmregionname has deprecated. What to use instead of that?

Userlevel 7
Badge +3

Hello @hitesh 
Devo Docs  points to this possible replacement:


Userlevel 3

Hello @hitesh 
Devo Docs  points to this possible replacement:


Thanks @juan.delrio :D

Userlevel 1