Query Exchange

 View Only
  • 1.  Programs Installed In Non-Standard Windows Locations

    Posted Feb 27, 2020 04:41 PM

    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";

     


    #CarbonBlack
    #IncidentResponse
    #Windows
    #ITHygiene


  • 2.  RE: Programs Installed In Non-Standard Windows Locations

    Broadcom Employee
    Posted Mar 13, 2020 02:15 PM

     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";

     



  • 3.  RE: Programs Installed In Non-Standard Windows Locations

    Broadcom Employee
    Posted Apr 08, 2020 03:09 PM


  • 4.  RE: Programs Installed In Non-Standard Windows Locations

    Posted Nov 22, 2021 08:43 PM

    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?



  • 5.  RE: Programs Installed In Non-Standard Windows Locations

    Broadcom Employee
    Posted Nov 23, 2021 05:19 PM

     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)?



  • 6.  RE: Programs Installed In Non-Standard Windows Locations

    Posted Nov 23, 2021 06:16 PM

     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';



  • 7.  RE: Programs Installed In Non-Standard Windows Locations

    Broadcom Employee
    Posted Nov 23, 2021 06:59 PM

     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


  • 8.  RE: Programs Installed In Non-Standard Windows Locations

    Posted Nov 23, 2021 07:10 PM

     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? 



  • 9.  RE: Programs Installed In Non-Standard Windows Locations

    Posted Nov 23, 2021 07:19 PM

     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. 



  • 10.  RE: Programs Installed In Non-Standard Windows Locations

    Broadcom Employee
    Posted Nov 23, 2021 08:18 PM

     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';


  • 11.  RE: Programs Installed In Non-Standard Windows Locations

    Posted Nov 23, 2021 08:36 PM

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