Hi Everyone; I’m starting with osquery; is it poss...
# general
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/
it's not quite as easy as the apps last_opened_time but that comes from a unique metadata field on MacOS I believe. So it doesn't work on Windows.
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!
👍 1
Windows historically doesn't do a great job of capturing this info. Technically speaking, there is a WMI class
with the property
. 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
launcher table using the following query:
Copy code
wmi_raw AS (
  SELECT * FROM kolide_wmi 
  WHERE class = 'Win32_SoftwareFeature' 
  AND properties = 'description,lastuse,productname'),
wmi_pivot AS (
    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)
  CASE WHEN last_use = '19800000' 
       THEN 'Never' 
       ELSE last_use 
       END AS last_use 
FROM wmi_pivot;
You could also try lossier approaches such as the
of a given Program's executable path.
@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?