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

Search for password database files and private keys

Description: This query shows filenames in windows users subfolders that contains private key files (.pfx, .p12), and password manager database files (Keepass - .kdb and .kdbx, Bitser Password File -.bpw, Password Safe - .plk). The issue is some password managers can be bruteforced and private keys passwords are weak.

What The Data Shows: The data shows what password managers are installed as well as private keys. It is important to ensure that private keys and password managers have strong passwords.

SQL:

SELECT f.filename, f.path, u.username, h.sha256,
datetime(f.atime,"unixepoch","localtime") AS atime,
datetime(f.ctime,"unixepoch","localtime") AS ctime,
datetime(f.mtime,"unixepoch","localtime") AS mtime
FROM file as f JOIN users AS u USING(uid) JOIN hash AS h USING(path)
WHERE ((filename like "%.plk%") OR (filename like "%.kdb") OR (filename like "%.kdbx%") OR (filename like "%.p12") OR (filename like "%.pfx") OR (filename like "%.bpw")) and path like "\Users%%";

 

> Requirement: Please test all submissions using Live Query or Osquery before posting.

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

@ksnihur you have a typo in your submission. You have the hash table as ash. Also you can remove the "C:" from your path statement.

ksnihur
Contributor II

Thanks @jnelson, I must have made the mistake when trying to format it for the cite as in my vm I had hash. 

jnelson
Carbon Black Employee
Status changed to: Approved
 
ben_bass
New Contributor

Some slight changes you can make to make this work on macOS as well (at least on standard osquery):

 

SELECT f.filename, f.path, u.username, h.sha256,
     datetime(f.ctime,"unixepoch","localtime") AS ctime,
     datetime(f.mtime,"unixepoch","localtime") AS mtime
FROM file as f JOIN users AS u USING(uid) JOIN hash AS h USING(path)
WHERE
((filename like "%.plk%") OR (filename like "%.kdb") OR (filename like "%.kdbx%") OR (filename like "%.p12") OR (filename like "%.pfx") OR (filename like "%.bpw")) AND ((path like "\Users%%") OR (path LIKE "/Users%%" AND path NOT LIKE "/Users/%/Library/Containers%%"));

macOS immediately stops as it doesn't have any paths like \Users%%.  I am also excluding the /Library/Containers/ directory as there are a lot of aliases/symlinks back to the desktop, and one file can show up dozens of times via different symlinks.

Also be aware that this query is extremely disk intensive, especially if run in a virtual environment.  This would not be something you would want to run on a frequent basis at all.  I also removed the atime field as the query itself searches and access the file, so the atime would be the time of the query and not very useful in the long run.

 

ksnihur
Contributor II

@ben_bass  Thats awesome thanks, I didnt have any macs in our environment so didnt go down that road. I completely agree that this is disk intensive as is and only ran it on an adhoc basis.

slist
Carbon Black Employee

Some slight changes, you can make this work on Linux as well (just added "/home" ):

Tested successfully on Linux Ubuntu.

Enjoy

 

SELECT f.filename, f.path, u.username, h.sha256, datetime(f.ctime,"unixepoch","localtime") AS ctime, datetime(f.mtime,"unixepoch","localtime") AS mtime FROM file as f JOIN users AS u USING(uid) JOIN hash AS h USING(path) WHERE ((filename like "%.plk%") OR (filename like "%.kdb") OR (filename like "%.kdbx%") OR (filename like "%.p12") OR (filename like "%.pfx") OR (filename like "%.bpw")) AND ((path like "\Users%%") OR (path like "/home/%%") OR (path LIKE "/Users%%" AND path NOT LIKE "/Users/%/Library/Containers%%"));