This time, we’ll work with Arcadia’s Windows OS logs. Typically, Windows logs go to a data table that begins with box.win. Depending on the particular method for logging Windows events (Devo Endpoint Agent, Logstash...), subsequent tags might ensue.
Let’s have a quick look:
-
Open the
box.windata table. -
Use the time range filter to show only those events ingested in the last 7 days. There is a predefined interval already set for you. You don’t need to click on the days in the calendar.
-
Locate the eventID field.
-
Note that the values “4624” and “4634” are the ones with most frequency. No wonder: they correspond to the actions of logging in and loggin out. While this information might be important in some use cases, right now we are not interested in this.
-
Then, let’s filter out “4624” and “4634”.
-
The filtering phase can be deemed complete. Now is the time for data enrichment. The machineIP field stores the IP addresses. Knowing the location of these Windows users might be a valuable information.
-
Click on the Create field icon. Use our favorite geolocation operations: to get the latitude and longitude out of IP addresses. Use
mm2latitudeandmm2longitudeoperations to add two new fields. -
Group by the machineIp, srcHost, and of course the location fields every 15 minutes.
-
Hover over the srcHost field header. How many unique hosts do you see?
-
They are not that many, right? We should be able to plot them into a map and make some sense of these machines.
-
We have location information, but we need a quantitative field to measure the size of each group. This is needed for a visual representation of this query.
-
Let’s use the simplest of the aggregation operations:
count. -
Now we are ready to represent the information: go to Additional tools > Charts > Maps and select the Google heatmap option.
-
Add the location fields in their placeholder. Use the count operation for the Size/Color field.
-
Now add the srcHost field in the Partitioning field to further diffrentiate the data representation.
This query exemplifies a potential solution to this exercise:
from box.win
where eventID /= 4624 or eventID /= 4634
select mm2latitude(machineIp) as latitude
select mm2longitude(machineIp) as longitude
group every 15m by machineIp, srcHost, latitude, longitude
every 15m
select count() as count
Still here?
Then, we have two extra tasks for you:
- You might have noticed that the latitude and longitude fields contain some null values. It is best practice to do a second filter after enrichment. Let’s get rid of them! You can use the
isnotnulloperation.
- You’ve been told that it’d be more comfortable to read the source hosts if they were in lower-case. Let’s indulge this idea with a LINQ operation. Add a new field with the
loweroperation. Use srcHost as the argument. - Then, open the settings of the Google heatmap by clicking on the Show signals button. Repace srcHost with your lower-case new field.
Give this query a meaningful name (something Windows-related, for example) and keep it if you want. Mark it as a favorite and close the query.
Feel free to join the discussion with your comments.
