Useful SQL Queries

The following SQL queries were found to be useful when developing/debugging Truxton.

Files

Queries to get file lists.

Human Readable List

This will list files in a human readable format. The WHERE clause is commented out so you can add your own filter criteria.

SELECT
"File"."ID",
"File"."MediaID",
"File"."ParentFileID",
"Origin"."Name" AS "Origin",
"FileType"."ShortName" AS "FileType",
"FN2"."Name",
"File"."OSLength",
"File"."HashID",
"File"."RawEntropy" * 0.00024414502395702993865779595324564 AS "Entropy",
"Filename"."Name" AS "Path",
"File"."FullPathID",
"File"."FileTypeID",
to_hex( "File"."Signature" ) AS "Signature",
to_hex( "File"."PhysicalDiskOffset" ) AS "DiskOffset",
"FileType"."LongName" AS "FileTypeDescription",
"File"."Created",
"File"."LastWrite",
"ContentStatus"."Name" AS "CStatus",
"File"."NumberOfChildren",
"File"."FilenameID"
FROM "File"
LEFT OUTER JOIN "FileType" ON ( "File"."FileTypeID" = "FileType"."ID" )
LEFT OUTER JOIN "Filename" AS "FN2" ON ( "File"."FilenameID" = "FN2"."ID" )
LEFT OUTER JOIN "Filename" ON ( "File"."FullPathID" = "Filename"."ID" )
LEFT OUTER JOIN "Origin" ON ( "File"."OriginID" = "Origin"."ID" )
LEFT OUTER JOIN "ContentStatus" ON ( "File"."ContentStatusID" = "ContentStatus"."ID" )
--WHERE "File"."Signature" = -623191334
ORDER BY "File"."ID"

Files with Depot Locations

SELECT "File"."ID",
"File"."ParentFileID",
"File"."FileTypeID",
"FileType"."ShortName",
"Filename"."Name",
"Content"."Offset",
"Content"."Length",
"Depot"."Filename"
FROM
"File", 
"Depot",
"Filename",
"Content",
"FileType"
WHERE "File"."HashID" = "Content"."Hash" 
 AND "File"."FilenameID" = "Filename"."ID"
 AND "Content"."DepotID" = "Depot"."ID"
 AND "File"."FileTypeID" = "FileType"."ID"

Files Truxton Can Exploit

SELECT
"ShortName",
"LongName"
FROM "FileType"
WHERE "ID" IN
(
  SELECT DISTINCT("FileTypeID")
  FROM "ETLRoute"
  WHERE "ETLQueueName" IN ('expand', 'email', 'remoteexpand', 'archives', 'pfe                             ')
)
ORDER BY "ShortName"

This one produces a single line of output that is easier for a human to read"

SELECT CONCAT( "ShortName", ' - ', "LongName")
FROM "FileType"
WHERE "ID" IN
(
  SELECT DISTINCT("FileTypeID")
  FROM "ETLRoute"
  WHERE "ETLQueueName" IN ('expand', 'email', 'remoteexpand', 'archives', 'pfe                             ')
)
ORDER BY "ShortName"

NOTE the pfe item is a bug. There should NOT be spaces in that name.

Logs

All Messages

SELECT
"Log"."MediaID",
"Log"."When",
"T"."String" AS "Type",
"S"."String" AS "Source",
"Log"."ProcessID",
"Log"."ThreadID",
"C"."String" AS "Client",
"U"."String" AS "User",
"M"."String" AS "Message"
FROM
"Log",
"LogString" AS "S",
"LogString" AS "C",
"LogString" AS "U",
"LogString" AS "M",
"LogString" AS "T"
WHERE
"Log"."LogStringSourceID" = "S"."ID" 
 AND "Log"."LogStringClientID" = "C"."ID" 
 AND "Log"."LogStringUserID" = "U"."ID" 
 AND "Log"."LogStringMessageID" = "M"."ID" 
 AND "Log"."LogStringTypeID" = "T"."ID"
-- AND "Log"."LogStringTypeID" <> 2 -- Don't show Info messages
-- AND "Log"."MediaID" = '11111111-1111-1111-1111-000000000001'::uuid
ORDER BY
"Log"."MediaID",
"Log"."When"

Only Error and Warnings

SELECT
"Log"."MediaID",
"Log"."When",
"Log"."LogStringTypeID",
"T"."String" AS "Type",
"S"."String" AS "Source",
"Log"."ProcessID",
"Log"."ThreadID",
"C"."String" AS "Client",
"U"."String" AS "User",
"M"."String" AS "Message"
FROM
"Log",
"LogString" AS "S",
"LogString" AS "C",
"LogString" AS "U",
"LogString" AS "M",
"LogString" AS "T"
WHERE
"Log"."LogStringSourceID" = "S"."ID" 
 AND "Log"."LogStringClientID" = "C"."ID" 
 AND "Log"."LogStringUserID" = "U"."ID" 
 AND "Log"."LogStringMessageID" = "M"."ID" 
 AND "Log"."LogStringTypeID" = "T"."ID"
 AND "Log"."LogStringTypeID" IN ( 0, 1 ) -- Don't show Info messages
-- AND "Log"."MediaID" = '11111111-1111-1111-1111-000000000001'::uuid
ORDER BY
"Log"."MediaID",
"Log"."When"

Depots

These queries will tell you if you're missing depot file entries.

Missing Content Depots

SELECT DISTINCT( "DepotID" )
FROM "Content"
WHERE
"DepotID" <> '00000000-0000-0000-0000-000000000000'::uuid
 AND "DepotID" NOT IN ( SELECT "ID" FROM "Depot" )
ORDER BY "DepotID"

Missing Image Depots

ELECT DISTINCT( "DepotID" )
FROM "Image"
WHERE 
"DepotID" <> '00000000-0000-0000-0000-000000000000'::uuid
 AND "DepotID" NOT IN
 (
   SELECT "ID" FROM "Depot"
 )
ORDER BY "DepotID"

Depot IDs in Use

SELECT DISTINCT "DepotID" AS "ID" FROM
(
  SELECT "DepotID" FROM "Content"
  UNION
  SELECT "DepotID" FROM "Free"
  UNION
  SELECT "DepotID" FROM "Image"
  UNION
  SELECT "DepotID" FROM "Slack"
  UNION
  SELECT "ThumbnailDepotID" AS "DepotID" FROM "ReviewItem"
) AS "SubSelect"

Depot IDs Not in Use

SELECT "ID" FROM "Depot" WHERE "ID" NOT IN ( SELECT "ID" FROM get_id_of_depots_in_use() )

File Type Counts

SELECT
"FileTypeID",
"FileType"."ShortName",
COUNT( "FileTypeID" ) AS "NumberOfThatType"
FROM "File"
INNER JOIN "FileType" ON ( "FileType"."ID" = "File"."FileTypeID" )
GROUP BY
"FileTypeID",
"FileType"."ShortName"
ORDER BY "NumberOfThatType" DESC

Messages

Messages and Subjects

SELECT
"Message"."ID",
"MessageType"."ShortName" AS "Type",
"Sent", "Received", 
"MessageSubject"."Text" AS "Subject",
"NumberOfBodies",
"NumberOfAttachments"
FROM "Message"
LEFT OUTER JOIN "MessageSubject" ON ( "Message"."MessageSubjectID" = "MessageSubject"."ID" )
LEFT OUTER JOIN "MessageType" ON ("Message"."MessageTypeID" = "MessageType"."ID" )
ORDER BY "Message"."Sent"

Find Messages that did not Parse

SELECT
"File"."ID",
"FN2"."Name",
"File"."OSLength",
"File"."HashID",
"ContentStatus"."Name" AS "CStatus",
"File"."NumberOfChildren"
FROM "File"
LEFT OUTER JOIN "Filename" AS "FN2" ON ( "File"."FilenameID" = "FN2"."ID" )
LEFT OUTER JOIN "Origin" ON ( "File"."OriginID" = "Origin"."ID" )
LEFT OUTER JOIN "ContentStatus" ON ( "File"."ContentStatusID" = "ContentStatus"."ID" )
WHERE
"File"."FileTypeID" = 199
 AND "File"."ID" NOT IN
 (
   SELECT "FileID" FROM "Message
 )

SSID

SSID and Associated Passwords

SELECT
DISTINCT "A_ID" AS "PasswordEntityID",
"B_ID" AS "SSIDEntityID",
"SSID"."Value" AS "SSID",
"Password"."Value" AS "Password"
FROM "Relation"
INNER JOIN "Entity" AS "PasswordEntity" ON ( "Relation"."A_ID" = "PasswordEntity"."ID" )
INNER JOIN "Entity" AS "SSIDEntity" ON ( "Relation"."B_ID" = "SSIDEntity"."ID" )
INNER JOIN "EntityString" AS "Password" ON ("PasswordEntity"."EntityStringID" = "Password"."ID")
INNER JOIN "EntityString" AS "SSID" ON ("SSIDEntity"."EntityStringID" = "SSID"."ID")
WHERE "B_ID" IN 
(
  SELECT "ID" FROM "Entity"
  WHERE "MediaID" = '0e8beeee-1c42-91c3-e0b1-93edf6ed2d73'::uuid
   AND "EntityTypeID" = 25
)
ORDER BY "SSID"."Value"

Entity

Human Readable

How to get the entities to reprocess a piece of media.

SELECT 
"Entity"."ID",
"Entity"."FileID", 
"Entity"."EntityTypeID", 
"Entity"."EntityStringID",
"Entity"."Offset",
"Entity"."ObjectID", 
"Entity"."ObjectTypeID",
"Entity"."Length",
"Entity"."DataTypeID",
"ObjectType"."Name", 
"EntityString"."Value", 
"EntityType"."LongName"
FROM "Entity"
INNER JOIN "EntityType" ON ( "Entity"."EntityTypeID" = "EntityType"."ID" )
INNER JOIN "EntityString" ON ( "Entity"."EntityStringID" = "EntityString"."ID" )
INNER JOIN "ObjectType" ON ( "Entity"."ObjectTypeID" = "ObjectType"."ID" )
WHERE "Entity"."MediaID" = '139db997-1f71-58d2-46ae-e052bb67a946'::uuid

Entity

How Long did the Loads Take

This query is useful only when you are loading a bunch of media for an investigation at the same time.

SELECT MIN("Start"), MAX("End")
FROM public."Event"
where 
"EventTypeID" = 1002 AND
"MediaID" IN
(
  SELECT "MediaID" FROM "InvestigationMedia" WHERE "InvestigationID" = '20171021-0000-1111-2222-333333333333'::uuid
)