Hi Everyone; I’m starting with osquery; is it possible to retrieve application metrics on Windows, just like the apps.last_opened_time on OSX?

Are you running the latest version of osquery? I believe you may be able to get the information you are looking for from the new ntfs_journal_events table. You can read the blog post from Trail of Bits, who added this table to osquery, here https://blog.trailofbits.com/2020/03/16/real-time-file-monitoring-on-windows-with-osquery/

https://blog.kolide.com/how-to-set-up-windows-file-integrity-monitoring-using-osquery-and-kolide-d5ac09db046b we also wrote a blog about this @Severin Launiau

Thanks for that link too @terracatta!

thank you very much! it sounds like it will catch more than what’s in userassist or scheduled_tasks alone; thanks a lot!

Windows historically doesn't do a great job of capturing this info. Technically speaking, there is a WMI class

Win32_SoftwareFeature

with the property

lastused

. Unfortunately, in practice this value is rarely updated/reliable and not all 'Software' seems to register itself with Win32_SoftwareFeature. If you are using Kolide's launcher, then you can attempt to query this info using the

kolide_wmi

launcher table using the following query:

WITH 
wmi_raw AS (
  SELECT * FROM kolide_wmi 
  WHERE class = 'Win32_SoftwareFeature' 
  AND properties = 'description,lastuse,productname'),
wmi_pivot AS (
  SELECT 
    MAX(CASE WHEN key = 'description' THEN value END) AS description,
    MAX(CASE WHEN key = 'productname' THEN value END) AS product_name,
    MAX(CASE WHEN key = 'lastuse' THEN SUBSTR(value, 0, 9) END) AS last_use
  FROM wmi_raw GROUP BY parent)
SELECT 
  product_name, 
  description, 
  CASE WHEN last_use = '19800000' 
       THEN 'Never' 
       ELSE last_use 
       END AS last_use 
FROM wmi_pivot;

@fritz should querying the file table update the file.atime? I'm running a query against unmodified files, and I'd rather it was last accessed but if I query

SELECT programs.name, file.atime FROM programs LEFT JOIN file on programs.install_location = file.directory WHERE file.path LIKE '%%.exe' GROUP BY programs.name UNION SELECT 'NOTAPP', '';

it updates their last accessed time to the time of the query

Teddy discussed this in his QueryCon talk iirc @theopolis can you chime in in terms of forensics / access time? I will pull up your talk in the meantime and see if I can find the relevant soundbite.

Thank you, Fritz - I'll wait for theopolis but as I understand from that talk it this is the expected but not desired behaviour?