Useful SQL Queries - Revision history

Sam: /* Current Connections */

2026-04-05T11:11:25Z

Current Connections

2024-09-13T21:00:10Z

Make a Hashset From Loaded Data

2024-09-13T20:56:05Z

Files

2024-09-11T20:53:49Z

Python Script to Change Depot Path

2024-04-04T09:37:47Z

Files Truxton Can Exploit

2024-03-06T10:38:34Z

List NTHashes and Associated Passwords

2024-03-06T10:34:13Z

Find Messages that did not Parse

2024-03-06T10:32:55Z

Email/SMS/Communications

2023-10-25T21:25:51Z

Carved Files

2023-10-25T21:22:53Z

Carved Files

@@ Line 37: / Line 37: @@
         usename,
         application_name,
-        client_addr
+        client_addr,
 FROM pg_stat_activity
 </source>

@@ Line 347: / Line 347: @@
 GROUP BY "HashID"
 HAVING COUNT("HashID") > 50
-ORDER BY "HashID" ASC</source>
+ORDER BY "Hash" ASC</source>
 =Logs=

@@ Line 328: / Line 328: @@
 WHERE "FileTypeID" = 1021
 </source>
 =Logs=

@@ Line 535: / Line 535: @@
 import psycopg
-def main():
+def main() -> None:
    t = truxton.create()
@@ Line 544: / Line 544: @@
        database_cursor.close()
-   return
+   return None
 if __name__ == "__main__":

@@ Line 261: / Line 261: @@
 </source>
-This one produces a single line of output that is easier for a human to read"
+This one produces a single line of output that is easier for a human to read.
 <source lang="sql">
 SELECT CONCAT( "ShortName", ' - ', "LongName" )

@@ Line 680: / Line 680: @@
 ==List NTHashes and Associated Passwords==
 This will produce a list of passwords and their [https://en.wikipedia.org/wiki/LAN_Manager#Workarounds NTHash] in a given media.
 <source lang="sql">
 SELECT "B"."Value" AS "NTHash",
@@ Line 700: / Line 703: @@
 This will produce a unique list of hashes and passwords for all of Truxton.
 <source lang="sql">
 SELECT DISTINCT "B"."Value" AS "NTHash",

@@ Line 592: / Line 592: @@
    AND "File"."ID" NOT IN
+       (
-         SELECT "FileID" FROM "Message
+         SELECT "FileID" FROM "Message"
+       )
 </source>

@@ Line 219: / Line 219: @@
 ==Carved Files==
 This query is useful when examining Truxton's file carving quality.
 <source lang="sql">
 SELECT DISTINCT("File"."ID"),

@@ Line 226: / Line 226: @@
         "Content"."Offset",
         "File"."PhysicalDiskOffset",
         "Depot"."Filename" AS "Depot Path"
 FROM "File",
@@ Line 231: / Line 233: @@
       "Filename",
       "Content",
-      "FileType"
+      "FileType",
 WHERE "File"."HashID" = "Content"."Hash"
    AND "File"."FilenameID" = "Filename"."ID"
    AND "Content"."DepotID" = "Depot"."ID"
    AND "File"."FileTypeID" = "FileType"."ID"
    AND "File"."OriginID" = 3
    AND "File"."FileTypeID" IN (203, 204)