Devo Foundations - Exercise 4.2 - Search window (II)

  • 27 January 2023
  • 5 replies
Devo Foundations - Exercise 4.2 - Search window (II)
Userlevel 4

This time, we’ll work with Arcadia’s Windows OS logs. Typically, Windows logs go to a data table that begins with Depending on the particular method for logging Windows events (Devo Endpoint Agent, Logstash...), subsequent tags might ensue.

Let’s have a quick look:

  1. Open the data table.

  2. 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.

  3. Locate the eventID field.

  4. 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.

  5. Then, let’s filter out “4624” and “4634”.

  6. 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.

  7. Click on the Create field icon. Use our favorite geolocation operations: to get the latitude and longitude out of IP addresses. Use mm2latitude and mm2longitude operations to add two new fields.

  8. Group by the machineIp, srcHost, and of course the location fields every 15 minutes.

  9. Hover over the srcHost field header. How many unique hosts do you see?

  10. They are not that many, right? We should be able to plot them into a map and make some sense of these machines. 

  11. 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.

  12. Let’s use the simplest of the aggregation operations: count.

  13. Now we are ready to represent the information: go to Additional tools > Charts > Maps and select the Google heatmap option.

  14. Add the location fields in their placeholder. Use the count operation for the Size/Color field. 

  15. Now add the srcHost field in the Partitioning field to further diffrentiate the data representation.

This query exemplifies a potential solution to this exercise:


  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 isnotnull operation.
  • 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 lower operation. 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.

5 replies

Userlevel 2



Interesting: I tried to filter out by excluding these 2 evenIDs but apparently Devo syntax prefers to include all the others, which is odd considering that some fields can have a huge amount of different values, and listing all of them wouldn’t seem to be the optimal approach. I’ll look further into it.





I see 5 srcHost distinct values:


I’m not being able or I don’t know how to aggregate the srcHost field. I tried by all means I can think of and also by IP_Latitud and IP_Longitude but these 3 fields aren’t even selectable:



I’ll come back tomorrow whith a fresher mind and try again.

Any suggestion will be welcome. Thanks!



Userlevel 4

Hello Minion!

Let me try to find a solution to your problems.

apparently Devo syntax prefers to include all the others

Not sure how that happened, but here’s a suggestion: when clicking the “4624” and “4634”, a pop-up window for the details of the filter should open. I can’t judge by your screenshots, but it’s very important that you change the operation of the filter to not equal:

Pay attention to this pop-up window when you have clicked on the two values from the list of unique values. Did you try this?


I’m not being able or I don’t know how to aggregate the srcHost field. 


Let me say first that for the exercise you can do a simple count operation of the whole grouping and not a particular field. Just set no arguments.

Why are you trying to do a count over one of the grouping keys? it really makes no sense what you are trying to do from a data perspective. Actually, if you force it with LINQ and compare the two new fields (a count operation with no arguments and a count operation with srcHost as the argument), you will see little difference.

select count(srcHost) as srcHostCount

select count() as count

This means that it’s the expected behavior that the GUI does not let you add “srcHost” (or any of the other grouping keys, for that matter) as the argument for the count operation.

The screenshot below is taken from the entry for count in Devo Docs:

Just for the sake of curiosity, group just by time (no fields as arguments) and then try to do a count operation over “srcHost”. I believe it would be selectable then.

Does this solve your problem? Let me know if I can assist.

Userlevel 2

Hi Alex,

Thanks for answering :)

Firstly: You are absolutely right, I didn’t do the filtering the right way. I didn’t understand the mechanics. Doing it as you say, it works just fine. Makes sense, when you already know how to do it :)



You are absolutely right, I wasn’t understanding what needed to be done. I thought that, having 5 srcHost values, we wanted to count how many events belong to each one of them. I saw the simpler option, just doing a “count” operation with no arguments, but I didn’t think it was what the exercise was asking.

Thanks for the detailed explanation and the examples.

Continuing with the exercise:



Filtering out the null values:







I did it :)

Thanks, Alex. You helped a lot. I’m sorry to inform you that I will most probably be bothering you again in the near future :D

Some of these things are obvious once you know them, but at the beginning they can be hairy.



Userlevel 4

Glad to help! Let me know if I can be of further assistance ^^

Really great exercise to get hands on with DEVO. Loved every bit of it. Thanks Alex.