Hi all, i would like to check the status on the work being done in https://github.com/osquery/osquery/pull/6904 , is there any chance to see this happening in the short term? or is it just looking for somebody with time to do the final changes?

yea it's lacking someone to carry it over the finish line

i see, thanks for the answer! it looks so close ! 😄

in the meantime this extension is available https://github.com/macadmins/osquery-extension/tree/main/tables/unifiedlog

That macadmins extension is an exec. Might be okay but be cautious there.

I am not certain of the downside of an exec in general? Yes we've discussed pseudo 'core-tenets' of osquery being "performance as a feature" and "never shell out", there is a non-zero 'impedance/context switch' cost, but what brings to mind the need for caution?

Philosophy aside, it’s can have high resource utilization and unexpected failure modes. So it’s something to be wary of, but sometimes one pragmatically accepts it.

i can take stab at finishing this, if this feature is still desired/wanted i may make a few changes though😄

considering TOB had removed their version a while back it would be great to get this finished being accepted in core, thanks!

It would be great if someone picked up pr 6904. I’m not sure where it was left.

This was mentioned/brought up in the office hours today I have this fixed I think Its a small change and should address all comments I also have tests for it as well I was able to run it multiple times on all 20 million+ log entries on my mac with no errors I hoping to comment/offer fixes/suggestions this week/weekend Unless someone else already fixed this Then no worries

@puffycid Are you saying you have a current version of https://github.com/osquery/osquery/pull/6904 or that you have a current version of something exec based?

It fixes the api version of #6904 No exec usage Its a small ~10 line change I hoping to comment/suggest changes later this week Unless a new PR is preferred? I've never edited an existing pr before

New PR seems easier?

i pushed a PR adding this to osquery it should address all issues/comments in the original PR 6904 Sorry for the delay (work/life stuff) I should have much more availability now and can quickly address any issues/comments for the PR ive opened (in addition to others i opened) also as fyi/shameless plug, I just wanted to mention that i am also trying to implement a raw UAL parsing support to osquery at https://github.com/puffyCid/osquery/tree/unifiedlogs. The benefit of raw parsing is that it will support older macos versions and it is slightly more forensic focus (raw parsing vs api). Its ~60% done and it still has lots of work to be done, but just wanted to mention it, if others want to monitor/contribute/comment. Im slowly working/chipping away on it between other osquery features im working on.

Hi all, i wanted to check about https://github.com/osquery/osquery/pull/7259, since i see it is pending review, is there any chance this is coming soon to the next osquery version? Thanks!

Hi Juan -- that one has been reviewed but the feedback from the TSC has been: as implemented, this table allows a user to

select * from log

in a way that might cause osquery to run too long / collect too much / get its worker process terminated by its watchdog process. I think the consensus was that we needed some way to prevent this. I'm just relaying what the conversation was and the status of that.

Puffycid asked how to implement the WHERE clause here: https://osquery.slack.com/archives/CP2RAJMU3/p1638367793007900?thread_ts=1634774133.001500&cid=CP2RAJMU3 The bad news is not around anymore (the slack account is disabled for some reason). I will happily finish the task (which is VERY close), but I don't want to disrespect anyone's job and I don't know how to reach Puffycid. Any ideas? Regarding the task itself, I would require to filter by timestamp. Requiring to filter by level (severity) could be a good idea too. Notice that error and fault messages have additional info attached, on the other hand, default and info messages are probably the most common, this way we don't mix large messages with lots of rows.

Querying a log filtered by timestamp is a good way to bound the query, but what if it were also paginated (you get up to some # of log entries). I am just thinking of how many REST APIs work where a service does not return everything all at once. https://www.moesif.com/blog/technical/api-design/REST-API-Design-Filtering-Sorting-and-Pagination/

I like it. Paginating could be a smart way to limit the number of logs returned. I'm thinking that we should order and pack the rows by timestamp establishing a limit of rows (maybe configurable by flags). A forensic tool would like to get a lot of logs and could iterate, in the case of continuous queries I don't know if this could cause the watchdog to raise the alarms. However, an aggregation tool may get the logs within a scheduled query so we must guarantee by design no logs are lost, so it must get all the logs that have been generated since the last query.

There's a

LIMIT

in SQL but I'm not sure if it's applied before osquery gathers the data, or after. If after, then osquery still tries to get all data first, which is what we're trying to avoid. I like the idea of a new CLI flag. This could be in a blueprint issue, as it probably applies equally to other types of log fetch queries.

yeah

LIMIT

applies after

So we are expecting the user to get the table like this: select * from unified_log where timestamp >= T0 and timestamp < T1 limit L In the case of scheduled queries, is it up to osquery to ensure that no logs are lost or is it up to the user? TODO: 0. Investigate if there is a way to apply the limit restraint before getting all the rows. If not, Shall we implement it on a new blueprint? 1. required=True, to the timestamp column 2. Is it possible to set a required limit? If not, Is it interesting enough to create a blueprint?

“In the case of scheduled queries, is it up to osquery to ensure that no logs are lost or is it up to the user?” I think it depends what you mean here and what is the query. Not knowing, I would say both in some cases and you could argue osquery only in some other. What I mean is that, first of all remember that table data is generated on the fly. When a scheduled query runs, it calls the table logic which start collecting data from the system, to memory. If osquery crashes or gets killed, then the collected data is lost, but the data in the system might still be there and unchanged. It’s not guaranteed obviously that it’s unchanged and a next query will get the same data again though. After the table logic has run though, the results are either written immediately in log files locally, and or written/buffered into RocksDB, so that they can be sent via TLS and not lost if the TLS service is temporarily unavailable or if osquery crashes. Now going back to the unified_log query, which logs are extracted depends on the query itself not on osquery. I see know that previously you stated “so we must guarantee by design no logs are lost, so it must get all the logs that have been generated since the last query.” There’s no such functionality in osquery. In that specific case it depends on the user to provide the timestamps that encompass the time range it’s missing, which also means that a scheduled query is not exactly ideal because the query is fixed. You could express the time range as relative to the current time, so if the scheduled query runs every 30mins, you get 1h worth of logs. Though if anything happens to osquery for more than 30mins, and it can’t extract the logs, you will have a hole.

ok, I understand, osquery is not responsible for that. Thank you for the clarification

Yeah when I mentioned it I was actually supposing that sqlite was exposing that but we were not using it or properly receiving it. I’ve given a quick look yesterday and I couldn’t find a way to do it ^^’.

It seems we won't be able to do it dynamically 😞 Shall we explore the possibility of setting a limit on the configuration?

Thank you for going the distance with it!

CLA signed, please review https://github.com/osquery/osquery/pull/7598

Added it to the 5.4 milestone

Where should I document this new pseudo-event-mechanism? Would it be ok if I wrote some lines on the table description? I mean, on the schema