This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Simple query to check if a folder exist

I know this may come off as a very basic question, but which table contains the list of all existing directories within an endpoint? 

I managed to come with this SQL query, but it only returns the folder if there is any file within the folder, hence the hash table.

SELECT
directory
FROM hash
WHERE
directory = '$$folderPath$$'

Table directories doesn't exist, neither does table folders, so how do I simply check if a folder exists within a given path regardless if it contains files or not?

Thank you.



This thread was automatically locked due to age.
  • FormerMember
    0 FormerMember

    Hmm, I am not sure if we index the entire folder structure. We mostly care about actual files. 

    What is the use case you need an index of folders whether they have files or not?

    Sincerely,

  • Thank you Richard. There is a parent virus which is creating child files within a specified folder. Sometimes Sophos is alerting and deleting the child file, but has not been able to detect the parent virus. I need a list of all endpoints containing this folder within our enterprise, empty or otherwise.

    Another scenario is that I need to check whether an old domain folder within GroupPolicy exist. The endpoints containing the old domain folder should be formatted. This GroupPolicy domain folder is the most decisive indicator whether an endpoint has been formatted after we changed the enterprise domain.

    Is it possible to run a "dir" cmd command using live discovery EDR to all endpoints and receive results, just like an SQL query? Which automated approach would check best if a folder exist using Sophos tools?

    Thank you.

  • FormerMember
    0 FormerMember in reply to Genc Kelmendi

    That is an interesting use case. Let me think about it and I will get back to you.

  • FormerMember
    +1 FormerMember in reply to FormerMember

    Okay, you can use the 'File Attributes and metadata' query:

    Now, to get an empty folder in the variable path put in the directory: C:\testFolder\  

    you can also put in \testFolder\ but it will just return a single entry even if the folder appears on multiple drives. 

    I just tested this by creating an empty folder on two drives and running the query. If I defined the specific path - it return each individual result. If I did the generic - it only returned one result even though there were two folders that matched. 

    I hope this helps.

  • Thank you Richard, that is exactly what I've been looking for, and works flawlessly.