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.trafficdata 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
mm2cityoperation. - Get the region/state out of dstIp with the
mmregionnameoperation.
- Get the city out of dstIp with the
- 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
countof 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
sumof 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
mm2countryoperation 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
isnotnullfiltering operation after the enrichment.
- You might want to save this query. When you are ready, close the query (this is also best practice.)
