Limit System event log

Content Authoring
1

Hello,

I’m trying to limit the system event log records to show the latest event from a specific ID.

Here is what I have so far:

example using the event id 8015 from system:

(time generated of it, description of it) of records whose (event id of it = 8015) of system event log

it will list all the events from system with the event id = 8015, however, I just need the latest.

So, I tried to get the maximum time and filter using times:

maximum of times generated of records whose (event id of it = 8015 ) of system event log

it will get the date of the latest record from the event id 8015.

if I combine it all in one it should look like this:

(time generated of it, description of it) of records whose (event id of it = 8015 and time generated of it = (maximum of times generated of records whose (event id of it = 8015 ) of system event log)) of system event log

however, the fixlet debugger crash when I run this query.

another option is to use the filter by days, however if the log is not in the timeframe I will be losing logs:

(time generated of it, description of it) of records whose (event id of it = 8015 and (now - time generated of it < 1*day)) of system event log
this query will return the logs when it’s lower than 1 day.

any idea how to get the latest event log?

Thanks!

2

This posting on event log queries is worth a read Relevance: Speeding Up Event Log Queries

I think the real key to understanding why your query is crashing the fixlet debugger is to understand how cross-products work in a tuple or whose() block.

When you have a tuple like (set 0, set 1), a cross-product is created where each element of set 0 is paired with each element of set 1

q: (("a";"b";"c"), (1; 2; 3))
A: a, 1
A: a, 2
A: a, 3
A: b, 1
A: b, 2
A: b, 3
A: c, 1
A: c, 2
A: c, 3

A similar thing happens with “whose” clauses. For every item, the ‘whose’ query is executed again. In the case of

(time generated of it, description of it) of records whose (event id of it = 8015 and time generated of it = (maximum of times generated of records whose (event id of it = 8015 ) of system event log)) of system event log

We first find every record. Then for each record we run the whose() filter. The ‘event id of it = 8015’ is simple and fast, but the next step gets more complicated. For every record that was event id 8015, we perform this nested query that again searches the entire event log for every event 8015, and find the maximum times of those events.

On my machine, I have 5 events that match 8015. In this query, it pulls every event, matches against the 5 that are event id 8015, and then performs the nested query - which means I search the entire event log 5 more times to figure out which of the 8015 events has the maximum created time.

To avoid that, what we can do is to move the nested query - the one that finds the most-recent event - to the outside of the whose() clause. By doing this, we can perform that subquery just once, and pass its results into the whose () without have to recalculate the most-recent time.

q: (time generated of it, description of it) of items 0 of (records whose (event id of it = 8015) of system event log, it) whose (time generated of item 0 of it = item 1 of it) of (maximum of times generated of records whose (event id of it = 8015 ) of system event log)

Here, the ‘maximum of times generated’ is only calculated once, and that result is passed in to the tuple to be compared in the whose() block.

On my machine that only had 5 of these events, your original query took T: 5865.039 ms to complete, and my modified version took T: 1518.589 ms.

2 Likes
3

Thank Jason for the excellent explanation.

After using the analysis I needed to include more event ids to the same query because in some cases the event uses a different id, I tried the following but it’s using only the first event id 10036.

(time generated of it, description of it) of items 0 of (records whose (event id of it = 10036 or event id of it = 10037 or event id of it = 10038) of system event log, it) whose (time generated of item 0 of it = item 1 of it) of (maximum of times generated of records whose (event id of it = 10036 or event id of it = 10037 or event id of it = 10038 ) of system event log).

any idea?, do I need to set for each event id the items of x ?

4

Depends on what you’re trying to do. If I’m reading this right, I’d expect to get the latest event that matches any of those IDs - it might be 10036 now because 10036 is the most recent of the three, but if a new 10037 happens I’d expect to match it later.

If you want to get the most recent of each of those events, you’ve have to change it around quite a bit (and it takes six loops through the event log, to get the max time of each and then retrieve the event that matches that time). Let me know if that’s what you’re trying to do.

5

yes, this is what I’m trying to do.

Thank for your help!

6

Looking at it more closely, you should probably just have three separate properties - one for each event. I don’t see a way to reduce then number of times we traverse the event log that’s any more efficient than just doing it in three separate properties at the moment.

This maybe a better job for a SIEM tool than an endpoint management tool?

7

Ok, so I couldn’t quite give up on this. I don’t have those events in my deployment, but I do have event ids 8014 and 8015 so I’ve crafted the search for them.

Repeated searching through the event log is painfully slow, but looping through the elements of a string set is very fast in the BES client. So what I’m doing here is to first build a set of tuple strings representing (event id, timestamp, description) of the events we want. Here’s the final query -

q: (tuple string item 0 of it, tuple string item 1 of it, tuple string item 2 of it) of items 0 of (elements of item 0 of it, items 2 of it) whose (tuple string item 1 of item 0 of it as time = item 1 of it) of (item 0 of it, item 1 of it, maximum of (it as time) of tuple string items 1 of items 0 of (elements of item 0 of it, item 1 of it) whose (tuple string item 0 of item 0 of it = item 1 of it)) of (it, unique values of tuple string items 0 of elements of it) of set of (it as string) of (event id of it, time generated of it, description of it) of records whose (event id of it is contained by set of (8014;8015) ) of system event log

Breakdown of how this works:

First, create a string set containing (event id, time generated, description) for the event records we want:

set of (it as string) of (event id of it, time generated of it, description of it) of records whose (event id of it is contained by set of (8014;8015) ) of system event log

Next, I’ll loop through the set to find the unique event IDs for which we searched (so only the very end of the query, with the event IDs, needs to be modified). I preserve the original set, as well as the unique event IDs within the set - the event IDs are ‘tuple string items 0’ of the elements in the set.
(it, unique values of tuple string items 0 of elements of it)

Now, given ‘item 0’ is the set of all events and ‘item 1’ is one unique event ID, I want to find the maximum time for that particular event in the set:

maximum of (it as time) of tuple string items 1 of items 0 of (elements of item 0 of it, item 1 of it) whose (tuple string item 0 of item 0 of it = item 1 of it)

Now we know the maximum time for each event, we need to loop back through the whole set to find the events that matched those timestamps. At this point, ‘item 0’ is the whole set and ‘item 2’ is one of the maximum times that we found. Loop through the set and find the elements whose ‘tuple string 0’ (time) matches the time we seek:

(elements of item 0 of it, items 2 of it) whose (tuple string item 1 of item 0 of it as time = item 1 of it)

At this point we have the elements that match the time, and the time we were looking for. We’re not interested in that maximum time anymore, just the ‘items 0’ elements that matched the time. ‘items 0’ are now the individual matching ‘tuple strings’ , so we can just keep the items 0 and split out the ‘tuple string items X’ of items 0 of …

(tuple string item 0 of it, tuple string item 1 of it, tuple string item 2 of it) of items 0 of

On my machine, that had 13 events total between these two event IDs, it was actually pretty quick

A: 8014, ( Mon, 04 Apr 2022 12:52:18 -0500 ), ( The system failed to register pointer (PTR) resource records (RRs) for network adapter%0d%0awith settings:%0d%0a%0d%0a           Adapter Name : {83433568-A95C-4775-9932-C565F52C21C3}%0d%0a           Host Name : endpoint-1%0d%0a           Adapter-specific Domain Suffix : d.domain.home%0d%0a           DNS server list :%0d%0a             %09192.168.1.254%0d%0a           Sent update to server : <?>%0d%0a           IP Address :%0d%0a             192.168.1.101%0d%0a%0d%0aThe reason the system could not register these RRs during the update request was because of a system problem. You can manually retry DNS registration of the network adapter and its settings by typing 'ipconfig /registerdns' at the command prompt. If problems still persist, contact your DNS server or network systems administrator. See event details for specific error code information. )

A: 8015, ( Wed, 30 Mar 2022 08:32:09 -0500 ), ( The system failed to register host (A or AAAA) resource records (RRs) for network adapter%0d%0awith settings:%0d%0a%0d%0a           Adapter Name : {83433568-A95C-4775-9932-C565F52C21C3}%0d%0a           Host Name : endpoint-1%0d%0a           Primary Domain Suffix : d.domain.home%0d%0a           DNS server list :%0d%0a             %09192.168.1.254%0d%0a           Sent update to server : <?>%0d%0a           IP Address(es) :%0d%0a             192.168.1.86%0d%0a%0d%0aThe reason the system could not register these RRs was because the update request it sent to the DNS server timed out. The most likely cause of this is that the DNS server authoritative for the name it was attempting to register or update is not running at this time.%0d%0a%0d%0aYou can manually retry DNS registration of the network adapter and its settings by typing 'ipconfig /registerdns' at the command prompt. If problems still persist, contact your DNS server or network systems administrator. )
T: 1103.068 ms
2 Likes
8

Thank you Jason for your answer.

one more thing if you can.

Now I’m searching the get the latest 5 events from the following analysis, but I’m not sure how to limit this to 5, do you think should I use the tuple - item from 0 to 5?

Thanks again!

(time generated of it, description of it) of records whose (event id of it = 8015 and time generated of it = (maximum of times generated of records whose (event id of it = 8015 ) of system event log)) of system event log

9

That’s the point where I think you need to switch over to a SIEM tool to report on your logs. I don’t know of any kind of ‘top 5’ iterator we can use in Relevance.

1 Like
10

…unless you want to ruin this beautiful solution by gettin’ kludgy with it – like doing it in a task so that you can dump the results to a file and then get the first 5 lines of the file (using something like (line whose (line number of it <= 5) of it) of file "path of file you dumped to")… and you’ll have to parse the tuple into and out of a single line to do it that way. It’s inelegant, more complex, and slower – but if you really, really don’t want to go to a SIEM it’ll get it done! :slight_smile:

1 Like
11

you can also do other kludges like this:

tuple string items (integers in (0,5))

1 Like
12

Thanks, @hp3, I think it should be the best solution for the top 5, using PS I can dump the results and read them.

13

Hey,

Now I’m finding the best solution for pulling the logs every 100 days. I’m using this code for getting the result, however, I’m not sure if it’s the best solution, or should I try to use tuples like the previous @JasonWalker post?

(time generated of it, description of it) of record whose (event id of it = 10036 or event id of it = 10037 or event id of it = 10038 and (now - time generated of it < 100*day)) of system event log

Thanks

14

Looks good to me, I’d only question whether 100 days is going back too far.

Oh but ‘record’ should use the plural ‘records’ since there’s probably more than one result

15

Thanks, Jason, however, I’m getting more than one row (it’s expected), so I need to concatenate it in one line:

concatenation “;” of (time generated of it as string, description of it as string) of records whose (event id of it = 10036 or event id of it = 10037 or event id of it = 10038 and (now - time generated of it < 100*day)) of system event log as string

PS: I tried to convert all to strings in order to concatenate but I’m getting the error:
Error: The operator “concatenation” is not defined.

The issue is with the time generated of it where it can’t be converted to string I tried with:

Any idea?

16

It’s because each row is a tuple…

(time generated of it as string, description of it as string)

You’ve have to combine those first before you can concatenate them

(time generated of it as string & description of it as string)

But we can’t store a property result longer than 2048 characters long in the BFEnterprise database, so careful about how long this result is. You’ve probably have to split it back out into multiple results anyway.