Title
#general
s

Severin Launiau

09/10/2020, 2:10 PM
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?
Ben Montour

Ben Montour

09/10/2020, 2:49 PM
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/
2:50 PM
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.
terracatta

terracatta

09/10/2020, 3:41 PM
Ben Montour

Ben Montour

09/10/2020, 3:43 PM
Thanks for that link too @terracatta!
s

Severin Launiau

09/10/2020, 3:46 PM
thank you very much! it sounds like it will catch more than what’s in userassist or scheduled_tasks alone; thanks a lot!
f

fritz

09/10/2020, 4:52 PM
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;
4:57 PM
You could also try lossier approaches such as the
file.atime
of a given Program's executable path.
a

asparamancer

01/13/2021, 5:49 PM
@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
f

fritz

01/13/2021, 5:50 PM
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.
5:52 PM

https://youtu.be/gSauI5U_Wmc?t=1506

a

asparamancer

01/14/2021, 2:26 AM
Thank you, Fritz - I'll wait for theopolis but as I understand from that talk it this is the expected but not desired behaviour?