Title
#fleet
n

n0b00de

03/16/2022, 7:09 PM
Can temp tables be created in a fleet queries?
zwass

zwass

03/17/2022, 12:18 AM
Yes that should be possible! Are you running into any issue? Also, I'm curious what your use case for this is?
n

n0b00de

03/17/2022, 3:23 PM
@zwass We were brainstorming ideas and temp tables came up for a possible solution for the problem we are facing, we want to run about 70 different queries and we didn’t want to run OR statements for each query. What would you recommend for this ex. if I wanted to detect what av/xdr an endpoint had, how can I write a query looking for the top 30 av/xdr products.
zwass

zwass

03/17/2022, 4:50 PM
What would you be looking for in that query? Perhaps something like
select name, cmdline, path, pid from processes where name in ('carbonblackagent', 'cylanceagent', 'crowdstrikeagent')
?
4:50 PM
(replacing those strings with the actual process names you'd be looking for)
Michal Nicpon

Michal Nicpon

03/17/2022, 5:00 PM
If you want to use temporary tables, this seems to work
drop table if exists x;
create temp table x (
  name varchar(255)
);
insert into x (name) values ('clamd');

SELECT * FROM processes p join x on p.name = x.name;
I think you need to drop temporary tables because osquery keeps a single sqlite db connection option. It would fail next time the query runs if you don't clean up.
zwass

zwass

03/17/2022, 5:04 PM
IIRC sqlite also supports
CREATE TABLE... IF NOT EXISTS