Title
#sql
f

fritz

11/02/2020, 3:16 PM
Your 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_packages
where an
install_time
exists?
z

Zach Zeid

11/02/2020, 3:18 PM
I realized that I don't need for it to return
TRUE
or
FALSE
in this case. I don't know of an instance where an installed rpm package would have an empty install_time column
f

fritz

11/02/2020, 3:19 PM
Got it, so you are all set?
z

Zach Zeid

11/02/2020, 3:20 PM
I think so! The intent is to be able to get the install_time and compare it to a
date()
object and return either True or False
3:23 PM
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;
is where I could use
COUNT()
here, right?
3:23 PM
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 PM
I 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?
3:30 PM
Or all install times in the last 14 days?
3:31 PM
If it is the latter, it appears you are doing things mostly right. I have a macOS example (no rpm based linux device handy):
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 PM
I'm actually indirectly querying when was the last time updates were ran on a given machine (through the use of yum-crontab)
3:31 PM
this is what I have so far
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.078003
f

fritz

11/02/2020, 3:35 PM
Got 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')
3:37 PM
So flying a little blind here:
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;
3:38 PM
The cte shouldn't be necessary but it helps me compartmentalize from a problem-solving approach
z

Zach Zeid

11/02/2020, 3:41 PM
yeah that works perfectly too, thanks @fritz!
f

fritz

11/02/2020, 3:42 PM
Without the cte it would be:
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 PM
what use cases would I consider using a CTE?
f

fritz

11/02/2020, 3:43 PM
I 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)
3:44 PM
Places where you need to use them are things like performing internally recursive operations
z

Zach Zeid

11/02/2020, 3:44 PM
I 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 PM
Exactly, it helps me keep things tidy and organized.
z

Zach Zeid

11/02/2020, 3:47 PM
got it, thanks for the tip!
f

fritz

11/02/2020, 3:49 PM
eg. let's say I knew I wanted to write a query where I was referencing only data pertaining to logged in users:
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),
3:49 PM
I can now take data I stashed away earlier in my query to scope my results
3:50 PM
without having to include those other JOIN's in the logic of my main query
z

Zach Zeid

11/02/2020, 3:50 PM
I see, you're making a temp table that gets the data you want so you can more easily join against other queries
f

fritz

11/02/2020, 3:50 PM
Exactly right.
z

Zach Zeid

11/02/2020, 3:50 PM
without having to craft complex queries that someone else might have trouble following
f

fritz

11/02/2020, 3:51 PM
Bingo
3:51 PM
(also so that I can troubleshoot queries I wrote earlier and have forgotten the context of 🙂 )
3:57 PM
I also find commenting my SQL in longer queries invaluable, for example:
-- 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 PM
oh, I didn't even know you could comment in sql queries.
3:58 PM
that's incredibly userful
f

fritz

11/02/2020, 3:58 PM
Yep!
--
3:58 PM
double dash = comment
z

Zach Zeid

11/02/2020, 3:59 PM
Got 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