Channels
#sql
Title
# sql
f
fritz
11/02/2020, 3:16 PMYour SQL's intent is not immediately clear to me. Can you describe the output you are trying to get?
Do you want to return results of only
rpm_packagesinstall_timez
Zach Zeid
11/02/2020, 3:18 PMI realized that I don't need for it to return
TRUEFALSEf
fritz
11/02/2020, 3:19 PMGot it, so you are all set?
z
Zach Zeid
11/02/2020, 3:20 PMI think so! The intent is to be able to get the install_time and compare it to a
date()actually,
select datetime(install_time, 'unixepoch', 'localtime') as last_install_time from rpm_packages where last_install_time >= date('now', '-14 days') order by last_install_time desc limit 1;COUNT() Copy code
osquery> select datetime(install_time, 'unixepoch', 'localtime') as last_install_time from rpm_packages where last_install_time >= date('now', '-14 days') order by last_install_time desc limit 1;
+---------------------+
| last_install_time |
+---------------------+
| 2020-10-30 11:40:57 |
+---------------------+
Run Time: real 1.454 user 1.372736 sys 0.081052
osquery> select datetime(install_time, 'unixepoch', 'localtime') as last_install_time from rpm_packages where last_install_time >= date('now', '-1 days') order by last_install_time desc limit 1;
Run Time: real 1.461 user 1.376146 sys 0.085041
osquery>f
fritz
11/02/2020, 3:30 PMI am still not quite sure what it is that you are trying to accomplish? Do you want just the timestamp of the last install on a given device?
Or all install times in the last 14 days?
If it is the latter, it appears you are doing things mostly right. I have a macOS example (no rpm based linux device handy):
Copy code
osquery> SELECT name, bundle_identifier, datetime(last_opened_time, 'unixepoch', 'localtime') AS last_opened_at FROM apps WHERE datetime(last_opened_time, 'unixepoch') > datetime('now', '-14 days') ORDER BY last_opened_time DESC;
+-----------------------------------+------------------------------------------------+---------------------+
| name | bundle_identifier | last_opened_at |
+-----------------------------------+------------------------------------------------+---------------------+
| <http://FaceTime.app|FaceTime.app> | com.apple.FaceTime | 2020-11-01 16:50:33 |
| <http://PTPCamera.app|PTPCamera.app> | com.apple.PTPCamera | 2020-10-31 14:08:09 |
| <http://Sketch.app|Sketch.app> | com.bohemiancoding.sketch3 | 2020-10-30 17:39:30 |
| <http://FindMy.app|FindMy.app> | com.apple.findmy | 2020-10-30 10:29:41 |
| Archive <http://Utility.app|Utility.app> | com.apple.archiveutility | 2020-10-30 09:43:18 |
| The <http://Unarchiver.app|Unarchiver.app> | cx.c3.theunarchiver | 2020-10-30 09:43:04 |
| <http://Screen.app|Screen.app> | <http://so.screen.screen.app|so.screen.screen.app> | 2020-10-30 09:37:59 |
| <http://Numbers.app|Numbers.app> | com.apple.iWork.Numbers | 2020-10-29 21:06:42 |
| <http://Maps.app|Maps.app> | com.apple.Maps | 2020-10-29 15:50:18 |
| Google <http://Chrome.app|Chrome.app> | com.google.Chrome | 2020-10-29 13:12:31 |z
Zach Zeid
11/02/2020, 3:31 PMI'm actually indirectly querying when was the last time updates were ran on a given machine (through the use of yum-crontab)
this is what I have so far
Copy code
osquery> select case when exists (select datetime(install_time, 'unixepoch', 'localtime') as last_install_time from rpm_packages where last_install_time >= date('now', '-1 days') order by last_install_time desc limit 1) then 'TRUE' else 'FALSE' end as updates_ran;
+-------------+
| updates_ran |
+-------------+
| FALSE |
+-------------+
Run Time: real 1.473 user 1.374503 sys 0.098190
osquery> select case when exists (select datetime(install_time, 'unixepoch', 'localtime') as last_install_time from rpm_packages where last_install_time >= date('now', '-28 days') order by last_install_time desc limit 1) then 'TRUE' else 'FALSE' end as updates_ran;
+-------------+
| updates_ran |
+-------------+
| TRUE |
+-------------+
Run Time: real 1.459 user 1.380649 sys 0.078003f
fritz
11/02/2020, 3:35 PMGot it, this sounds like a job for MAX. You want to grab the largest timestamp value and then run a comparison using
datetime('now', '-1 days')So flying a little blind here:
Copy code
WITH rpm_reduced AS (
SELECT datetime(MAX(install_time), 'unixepoch', 'localtime') AS max_install_time FROM rpm_packages)
SELECT CASE WHEN max_install_time >= datetime('now', '-1 days') THEN 'true' ELSE 'false' END AS updates_run_last_24h FROM rpm_reduced;The cte shouldn't be necessary but it helps me compartmentalize from a problem-solving approach
z
Zach Zeid
11/02/2020, 3:41 PMyeah that works perfectly too, thanks @fritz!
f
fritz
11/02/2020, 3:42 PMWithout the cte it would be:
Copy code
SELECT CASE WHEN datetime(MAX(install_time), 'unixepoch', 'local_time') >= datetime('now', '-1 days')
THEN 'true'
ELSE 'false'
END AS updates_run_last_24h
FROM rpm_packages;z
Zach Zeid
11/02/2020, 3:42 PMwhat use cases would I consider using a CTE?
f
fritz
11/02/2020, 3:43 PMI personally use cte's in a lot of places because they help me work stepwise through queries (and generally do not incur a huge performance penalty)
Places where you need to use them are things like performing internally recursive operations
👍 1
z
Zach Zeid
11/02/2020, 3:44 PMI see, its a way for you to comparmentalize the query in a way that's easier for you to follow along?
f
fritz
11/02/2020, 3:44 PMExactly, it helps me keep things tidy and organized.
z
Zach Zeid
11/02/2020, 3:47 PMgot it, thanks for the tip!
f
fritz
11/02/2020, 3:49 PMeg. let's say I knew I wanted to write a query where I was referencing only data pertaining to logged in users:
Copy code
WITH
relevant_users AS (
SELECT DISTINCT(username) AS username, u.uid
FROM users u CROSS JOIN logged_in_users liu
WHERE liu.user = u.username),I can now take data I stashed away earlier in my query to scope my results
without having to include those other `JOIN`'s in the logic of my main query
z
Zach Zeid
11/02/2020, 3:50 PMI see, you're making a temp table that gets the data you want so you can more easily join against other queries
💯 1
f
fritz
11/02/2020, 3:50 PMExactly right.
z
Zach Zeid
11/02/2020, 3:50 PMwithout having to craft complex queries that someone else might have trouble following
f
fritz
11/02/2020, 3:51 PMBingo
(also so that I can troubleshoot queries I wrote earlier and have forgotten the context of 🙂 )
I also find commenting my SQL in longer queries invaluable, for example:
Copy code
-- Reduce user accounts
WITH user_accounts AS (
SELECT username, description, uid, directory
FROM users WHERE SUBSTR(uuid, 0, 8) != 'FFFFEEE'),
-- Check against logged in users
liu AS (
SELECT ua.*
FROM logged_in_users JOIN user_accounts ua ON ua.username = logged_in_users.user
WHERE logged_in_users.tty = 'console'),
-- Check ByHost plists for screensaver settings
screensaver_byhost_multiple AS (
SELECT
u.username,
datetime(f.mtime, 'unixepoch') AS screensaver_pref_plist_modified,
MAX(CASE WHEN key = 'idleTime' THEN CAST(value AS int) END) AS screensaver_idle
FROM kolide_plist kp, file f USING(path)
JOIN user_accounts u ON u.directory = ('/Users/' || SPLIT(kp.path, '/', 1))
WHERE path LIKE '/Users/%/Library/Preferences/ByHost/com.apple.screensaver.%.plist'
GROUP BY path),
-- Cleanup ByHost files by resolving multiples
...z
Zach Zeid
11/02/2020, 3:58 PMoh, I didn't even know you could comment in sql queries.
that's incredibly userful
f
fritz
11/02/2020, 3:58 PMYep!
--double dash = comment
z
Zach Zeid
11/02/2020, 3:59 PMGot it, I'll be doing that from now on, thank you 😄
f
fritz
11/02/2020, 3:59 PM👍 Commented SQL is better for everyone, happy to have helped!
z
Zach Zeid
11/02/2020, 3:59 PM➕
5 Views