The VMware Carbon Black Tech Zone is live! Checkout this great resource: Mastering Carbon Black Audit & Remediation.

Programs Installed In Non-Standard Windows Locations

Description: Programs Installed In Non-Standard Windows Locations

What The Data Shows: Programs Installed Outside of C:\Program Files 

SQL: 

SELECT path,
                DATETIME(atime,"unixepoch","localtime") AS "Last Accessed",
                DATETIME(mtime,"unixepoch","localtime") AS "Last Modified",
                DATETIME(btime,"unixepoch","localtime") AS "Created"
FROM file
WHERE path LIKE "\users\%\AppData\%.exe"
    OR path LIKE "\users\%\AppData\Roaming\%.exe"
    OR path LIKE "\ProgramData\%.exe";

 

10 Comments
jnelson
Carbon Black Employee
Status changed to: Under Review

@stympanick Thanks for your submission, and sorry it took me awhile to review it.

I would suggest not relying on the programs table when writing these types of queries. The data in this table is dependant on the creator of the MSI to properly construct it so all the data populates. On a lab system I have 70 items in the programs table and 62 of them do not have an install_location. Also if a binary is installed without a Windows installer then it will not populate in this table.

Instead I have found leveraging the file table to be a much better solution. Here is an example of a query looking for executables in non-standard locations:

SELECT path,
                DATETIME(atime,"unixepoch","localtime") AS "Last Accessed",
                DATETIME(mtime,"unixepoch","localtime") AS "Last Modified",
                DATETIME(ctime,"unixepoch","localtime") AS "Created"
FROM file
WHERE path LIKE "\users\%\AppData\%.exe"
    OR path LIKE "\users\%\AppData\Roaming\%.exe"
    OR path LIKE "\ProgramData\%.exe";

 

jnelson
Carbon Black Employee
Status changed to: Approved
 
vbriones
New Contributor II

Hi,

I tried this query and it doesn't work. I get no matches while running a test on my local host, is there any way to test for a known match kind of to baseline if OsQuery's file table is working?

jnelson
Carbon Black Employee

@vbriones to be clear you had files with a ".exe" extension in one of the three folders listed, and the query returned no results? Are you running this query in the Carbon Black Cloud or in EDR (formerly CB Response)?

vbriones
New Contributor II

@jnelson I checked the directories and notice that they don't exist on my machien (hahaha) and I played around with the Query and I've got it working, but it doesn't seem to reflect all the files that are sitting in the directory. One question from my side though, if I query for a directory do I always have to include the Directory? like below, I look at the downloads directory, but say I wanted to look at any matches that could be downloads, would I have to do this \%\%\Downloads\%.exe' or is there another way to accomplish looking for a Downloads folder with .exe downloads? 

 

SELECT path,directory,filename,inode,size,filename,attributes,hard_links,symlink, datetime(mtime,"unixepoch","localtime") AS "Modified", datetime(ctime,"unixepoch","localtime") AS "Created", datetime(atime,"unixepoch","localtime") AS "Accessed" FROM file WHERE path LIKE'\users\%\Downloads\%.exe';

jnelson
Carbon Black Employee

@vbriones all of those folders are hidden by default. If you are in Windows Explorer and type out the full path then you will see the directories.

The file table requires that you use a path in the WHERE clause. The query you have in your reply will work.

On your question about looking for any folder named "Download" there are a few issues with that:

  1. The way you have it written will look for a folder called "Downloads" that has two parent directories
  2. Users can choose whatever folder they want to download things to
  3. Looking across the entire file system can take longer than normal
vbriones
New Contributor II

@jnelson Got'cha

I undestand it may take longer, but I want to know if it's doable at all. In reference to that, is there any way to accomplish if i wanted to look for an executable without designating the exact path or a certain amount of directories? For instance, if I wanted to find all executables called lsass.exe running on a system regardless of the directory? 

vbriones
New Contributor II

@jnelson Also, I kind of forgot to give you context, I'm essentially interested in accomplishing detection of an executeable that may exist (I'm assuming that this file table is created through referencing the MFT table?)

So Im looking to see if a file exists so that I can build an understanding of when the file was first created so that I can pivot based on that for during an incident, but sometimes the directories aren't static or are subject to changes in the environment (like if windows profiles are hosted on servers) so I was looking for a more flexible solution. 

jnelson
Carbon Black Employee

@vbriones the file dates/times are collected by the Windows API (I believe) and are not from the MFT, so the timestamps can be manipulated by timestomping. 

With that said, you could run a query like:

SELECT path, 
       datetime(mtime,"unixepoch","localtime") AS "Modified",
       datetime(btime,"unixepoch","localtime") AS "Created",
       datetime(atime,"unixepoch","localtime") AS "Accessed"
FROM file
WHERE path LIKE'\%%'
  AND filename = 'lsass.exe';

You also said you wanted all executables running on a system named 'lsass.exe'. To do this you could use this:

select p1.name,
       p1.pid,
       p1.parent as ppid,
       p2.name as parent,
       p1.path,
       datetime(p1.start_time,'unixepoch','localtime') as start_time
from processes as p1
join processes as p2
  on p1.parent = p2.pid
where p1.name ='lsass.exe'
  and p1.path != 'C:\Windows\System32\lsass.exe';
vbriones
New Contributor II

Oooo, that's a lot of good information, thank you @jnelson