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:

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?

@Marcos Oviedo You said you’d done some stiff with the windows search API. Any thoughts about this?

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

Thanks for the tips @Marcos Oviedo! I will look at implementing all this.

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.

thanks for the feedback @Marcos Oviedo!

Hey James, sorry for the late response here! I've been buried with stuff these last few days.

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.

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

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.

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.

Cool, thanks @Marcos Oviedo! I'm going to attend office hours today in case there is anything else to talk about.

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

Hi @Marcos Oviedo, thanks for the feedback, I updated the table and included some examples of converting the the unix timestamp to datetimes.

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!

🎉 woo thanks @Marcos Oviedo! My first OSS C++ contribution 🎉