Useful SQL Queries

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

Database Server Information

Number of Connections

SELECT COUNT(DISTINCT(numbackends)) FROM pg_stat_database

Maximum Number of Connections

Run this query as an administrator.

SHOW max_connections

All Connection Information

SELECT * FROM
(SELECT COUNT(*) "Current Number of Connections" FROM pg_stat_activity) q1,
(SELECT setting::int "Reserved for Superuser" FROM pg_settings WHERE name=$$superuser_reserved_connections$$) q2,
(SELECT setting::int "Maximum Number of Connections" FROM pg_settings WHERE name=$$max_connections$$) q3;

Current Activity

Ever wonder what the server is doing?

SELECT
state_change,
age(localtimestamp, state_change),
datname,
application_name,
state,
query
FROM pg_stat_activity 
ORDER BY
state,
age DESC,
datname ASC

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

SELECT 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 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"
)

URI in Filename Column

To detect when an ETL has put bad data into the [Depot] table.

SELECT "ID", "Filename", "URI", "DepotTypeID", "MaximumSize", "Size", "IsLongTerm", "CryptoKey", "DepotStatusID"
FROM "Depot"
where "Filename" like '\\%'

Change Depot URI

When you move depots to a new share, the database needs to be fixed up. This sample is what you would run if you need to change the machine name SOFS-01 to a fully qualified DNS name.

UPDATE "Depot"
SET "URI" = REPLACE( "URI", '\\SOFS-01\', '\\sofs-01.truxton.lab\Depot\')

Change Depot Path

If you move depots to a new folder, you must update the [Filename] column of the [Depot] table. This sample is what you would do if you moved the depot folder from C:\TruxtonData to C:\Storage

UPDATE "Depot"
SET "Filename" = REPLACE( "Filename", 'C:\Truxton Data\Depot\', 'C:\Storage\Depot\')

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

Loads

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 "Event"
WHERE 
 "EventTypeID" = 1002
 AND "MediaID" IN
 (
   SELECT "MediaID" FROM "InvestigationMedia" WHERE "InvestigationID" = '20171021-0000-1111-2222-333333333333'::uuid
 )

Links

The following links have proven useful:

• PGTune
• PostgreSQL Configurator