https://github.com/osquery/osquery logo
#core
Title
# core
j

James Pickett

04/11/2023, 5:06 PM
Hi Everyone 👋 , I'm working on my first osquery table
windows_search
that uses the windows search API, similar to the darwin mdfind table. I'm considering adding in support for specifying the item attribute names in the search. Since the attributes can be dynamic I would need to use and EAV style table so it might look something like this:
Copy code
osquery> SELECT * FROM windows_search WHERE attributes = 'system.itempathdisplay,system.size' AND query = 'scope=''file:C:\Users\james\Pictures\Screenshots''';
+---------+------------------------+-------------------------------------------+-------------------------------------------------+
| entity | attribute              | value                                     | query                                            |
+--------+------------------------+-------------------------------------------+--------------------------------------------------+
| 0      | system.itempathdisplay | C:\Users\james\Pictures\Screenshots\0.png | scope='file:C:\Users\james\Pictures\Screenshots' |
| 0      | system.size            | 100000000                                 | scope='file:C:\Users\james\Pictures\Screenshots' |
| 1      | system.itempathdisplay | C:\Users\james\Pictures\Screenshots\1.png | scope='file:C:\Users\james\Pictures\Screenshots' |
| 1      | system.size            | 100000000                                 | scope='file:C:\Users\james\Pictures\Screenshots' |
+--------+------------------------+-------------------------------------------+--------------------------------------------------+
what do yall think?
s

seph

04/11/2023, 5:42 PM
@Marcos Oviedo You said you’d done some stiff with the windows search API. Any thoughts about this?
m

Marcos Oviedo

04/11/2023, 5:43 PM
you beat me to it! I was about to share some things about this. The Windows Search interface is pretty powerful and it contains an SQL-like syntax that can be used to perform very narrowed queries
In the past I used ISearchManager COM interface to interact with the Windows Search indexing DB. Code sample here.
@James Pickett I really like the idea of having this functionality in place! good to see that you are working on it. I think there should be a list of common attributes there by default, so if someone wants to just look for a given pattern, they can get the list of matches without having to provide the attributes. New attributes can be added following the
attributes
predicate, but ideally, if nothing is provided the common ones should be used (System.ItemName, System.ItemPathDisplay, System.ItemType, etc). This will allow writing simpler queries like the one below
Copy code
SELECT * FROM windows_search WHERE query = 'pattern';
Also, enabling support for Advanced Query Syntax (AQS) is the way to go as this will allow people to create very narrowed searchs
j

James Pickett

04/11/2023, 6:01 PM
Thanks for the tips @Marcos Oviedo! I will look at implementing all this.
m

Marcos Oviedo

04/20/2023, 7:35 PM
Hey @James Pickett, apologies for getting back late on this. I've been buried with stuff, and I'm just being able to take a look at your PR. I've ended up leaving a bunch of comments on your PR.
j

James Pickett

04/20/2023, 7:54 PM
thanks for the feedback @Marcos Oviedo!
hi @Marcos Oviedo I think this PR is ready for another look https://github.com/osquery/osquery/pull/7990 ... sorry for the long delay other stuff came up and it just took me a bit to get the pieces to fit like I wanted
hi @Marcos Oviedo, I think this pr is ready for another look (again 😃), I've implemented most of the schema you suggested and added a column that will spit out json for additional requested properties
m

Marcos Oviedo

05/11/2023, 9:55 PM
Hey James, sorry for the late response here! I've been buried with stuff these last few days.
Thanks for continuing pushing to make this work happen! This table is going to be a great addition to osquery. I'll try to have a look at the latest code sometime tomorrow
j

James Pickett

05/12/2023, 3:53 PM
cool thanks @Marcos Oviedo! One thing I was considering adding was a check to make sure any additional properties queried for are valid. I wanted to do this because the windows api just errors out if you provide an property that doesn't exists. I'm concerned that properties may be added or dropped in later windows versions and it could break existing queries. However, I can't find a way to programmatically get a list of valid properties. Either the systemindex doesn't expose metadata about itself the way standard sql tables do or I can't find the right syntax.
m

Marcos Oviedo

05/12/2023, 8:29 PM
Hey James, just added some minor comments on the PR, code, and schema are looking good! As for the point about validating the properties, another option could be just returning the error to the end user so they know that query property the passed is invalid
j

James Pickett

05/12/2023, 9:33 PM
Thanks @Marcos Oviedo! I'll address as much of the feedback as I can today, then I'm out of pocket until Wednesday of next week and I'll finish it up then.
@Marcos Oviedo, I think I have addressed all the feedback except the one about
max_results
(please see comment).
m

Marcos Oviedo

05/23/2023, 4:13 PM
Hey @James Pickett, I will look at the latest code later today. The PR is mostly ready, so I'm going to push to have this included in the upcoming release.
j

James Pickett

05/23/2023, 4:27 PM
Cool, thanks @Marcos Oviedo! I'm going to attend office hours today in case there is anything else to talk about.
m

Marcos Oviedo

05/24/2023, 3:58 AM
Hey James, just went through the PR and just had one question on how to use
date_created
and
date_modified
. I left some example queries that we can use for testing
j

James Pickett

05/25/2023, 2:42 PM
Hi @Marcos Oviedo, thanks for the feedback, I updated the table and included some examples of converting the the unix timestamp to datetimes.
m

Marcos Oviedo

05/31/2023, 8:52 PM
Hey @James Pickett, I've just approved and merged the search table. This is an amazing addition to osquery, thanks for the great work you did here!
j

James Pickett

05/31/2023, 8:55 PM
🎉 woo thanks @Marcos Oviedo! My first OSS C++ contribution 🎉
5 Views