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
countoperation. - 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:
`lu/<lookup_name>/<lookup_field>` - 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.
