thread on the `unified log` table

Question

grahamgilbert · Answer

It looks like we are limited to 100 rows no matter what `timestamp` clause we use How can we get all of the results out of this table Or is the recommendation to run it every 60 or 30 seconds or something

grahamgilbert · Answer

```osquery gt select count from unified log where subsystem= com apple SoftwareUpdate AND timestamp gt SELECT unix time from time 3600 count = 100```

seph · Answer

The `max rows` rows column is how many it ll fetch from the underlying API

seph · Answer

But it s worth noting the unified log is huge I don t think you can get everything out of it with a simple select

seph · Answer

So it has an awkward not quite event model

seph · Answer

The suggestion is to set timestamp to ` 1` and it will behave like it s evented and a suitable max rows and query frequency

seph · Answer

How large `max rows` can be depends a lot on your specific installation environment

grahamgilbert · Answer

Can you expand on the behave like it s evented statement So we should just set the time stamp to 1 and call it every x seconds

grahamgilbert · Answer

With the asl table we scheduled our query for five minutes which worked okay

seph · Answer

The `unified log` is unique as a table If you query it normally it searches the log The log is very large thus there is a max rows parameter Otherwise an exploratory `select from unified log` would overwhelm something If you include `timestamp = 1` it behaves like a log follower It tracks the last returned timestamp and appends that This feature was contributed to allow someone to pull the whole log It s a bit like it s evented but implemented very differently It s somewhat beta it may not work quite right and it may change etc As for what you should do I don t know I imagine up the max rows to as high as your pipeline can handle and set timestamp to 1 Then fetch on a suitable interval

Brandon Kurtz · Answer

gt If you include `timestamp = 1` it behaves like a log follower It tracks the last returned timestamp and appends that What do you mean when you say the last returned timestamp we have to run the query multiple times in a row in order to get more and more hence appened results

Brandon Kurtz · Answer

In any case I can t figure out how to get any results from unified log right now ```select from unified log WHERE process= kernel AND timestamp= 1 AND max rows=1000 ``` Console shows results from this as of 10s ago but not seeing it in the table

zwass · Answer

cc < Daniel Bretón Suárez> who wrote the table

zwass · Answer

IIRC the use case Daniel had in mind was to be able to continually query for the full unified log Just looked at the code and Seph s syntax is slightly off What you need is `timestamp gt 1` If you schedule that query with a sufficiently high `max rows` you should eventually get the entire log and then be essentially streaming new results as they come in

zwass · Answer

if your `max rows` is too small then the log would grow faster than you were collecting it

Daniel Bretón Suárez · Answer

< Brandon Kurtz> as Zach and Joseph said if you add the condition `timestamp gt 1` the table uses a mode that saves the last log entry it has returned so the next time you query the table it will return logs starting from the entry after the last it has return Imagine we have entries like | last 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | `select from unified log where timestamp gt 1 and max rows = 10` It returns the entries 1st to 10th | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 last | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | And again `select from unified log where timestamp gt 1 and max rows = 10` It returns the entries 11th to 20th | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 last | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | If the OS deletes entries we can t control that you will still be getting sequential logs but you could lose some | last 20 | 45 | 46 | 47 | 48 | 49 | 50 | 51 | 52 | 53 | 54 | 55 | 56 | 57 | 58 | 59 | 60 | 61 | 62 | 63 | 64 | My advice is to set max rows so that eventually the number of rows returned is less than max rows I mean if max rows = 100 and you re always getting 100 entries it means there were more entries in the buffer If max rows = 500 and eventually you get i e 450 entries you know you have emptied the buffer

Brandon Kurtz · Answer

Is it possible to go backwards with the results or will the pointer moving through the buffer always go forward

Brandon Kurtz · Answer

I e if I want to see historical info I need to record the results of the query myself in soem external system

zwass · Answer

You can still run a historical search if you leave out the `timestamp` column If you want the entire log you would have to record it in an external system