Create a SQL Table of Known Good File Hash Values

Uses Microsoft’s free Log Parser and Microsoft’s free SQL Server edition.

Load a SQL table with hash codes (known good files).

Logparser "SELECT HASHMD5_FILE(Path), Name, Size, FileVersion, ProductVersion, InternalName, ProductName, CompanyName, FileDescription, OriginalFilename INTO KNOWNGOOD FROM C:\*.exe, C:\*.dll" -e:100 -i:FS -recurse:-1 -preserveLastAccTime:ON -o:SQL -server:MORIARTY\SQLEXPRESS -database:HASHCODES -driver:"SQL Server" -createTable:ON

This allows for 100 parse errors (“Access is denied”).

Task completed with parse errors.
Parse errors:
Error retrieving files: Error searching for files in folder <folder>: Access is denied.

Where <folder> is:

C:\Documents and Settings
C:\ProgramData\Application Data
C:\ProgramData\Desktop
C:\ProgramData\Documents
C:\ProgramData\Favorites
C:\ProgramData\Start Menu
C:\ProgramData\Templates
C:\System Volume Information
C:\Users\[user]\AppData\Local\Application Data
C:\Users\[user]\AppData\Local\History
C:\Users\[user]\AppData\Local\Temporary Internet Files
C:\Users\[user]\Application Data
C:\Users\[user]\Cookies
C:\Users\[user]\Documents\My Music
C:\Users\[user]\Documents\My Pictures
C:\Users\[user]\Documents\My Videos
C:\Users\[user]\Local Settings
C:\Users\[user]\My Documents
C:\Users\[user]\NetHood
C:\Users\[user]\PrintHood
C:\Users\[user]\Recent
C:\Users\[user]\SendTo
C:\Users\[user]\Start Menu
C:\Users\[user]\Templates
C:\Users\All Users\Application Data
C:\Users\All Users\Desktop
C:\Users\All Users\Documents
C:\Users\All Users\Favorites
C:\Users\All Users\Start Menu
C:\Users\All Users\Templates
C:\Users\Default User
C:\Users\Default\AppData\Local\Application Data
C:\Users\Default\AppData\Local\History
C:\Users\Default\AppData\Local\Temporary Internet Files
C:\Users\Default\Application Data
C:\Users\Default\Cookies
C:\Users\Default\Documents\My Music
C:\Users\Default\Documents\My Pictures
C:\Users\Default\Documents\My Videos
C:\Users\Default\Local Settings
C:\Users\Default\My Documents
C:\Users\Default\NetHood
C:\Users\Default\PrintHood
C:\Users\Default\Recent
C:\Users\Default\SendTo
C:\Users\Default\Start Menu
C:\Users\Default\Templates
C:\Users\Public\Documents\My Music
C:\Users\Public\Documents\My Pictures
C:\Users\Public\Documents\My Videos
C:\Windows\CSC\v2.0.6
C:\Windows\System32\LogFiles\WMI\RtBackup

Delete any NULL hash values.

DELETE KNOWNGOOD FROM [HASHCODES].[dbo].[KNOWNGOOD] WHERE KNOWNGOOD.HASHMD5_FILEPath IS NULL

Select the duplicate key values into a holding table.

SELECT [HASHMD5_FILEPath], Count=count(*) INTO [HASHCODES].[dbo].[HOLDKEY] FROM [HASHCODES].[dbo].[KNOWNGOOD] GROUP BY [HASHMD5_FILEPath] HAVING count(*) > 1

Select the duplicate rows into a holding table, eliminating duplicates in the process.

SELECT DISTINCT KNOWNGOOD.* INTO [HASHCODES].[dbo].[holddups] FROM [HASHCODES].[dbo].[KNOWNGOOD], [HASHCODES].[dbo].[holdkey] WHERE KNOWNGOOD.HASHMD5_FILEPath = holdkey.HASHMD5_FILEPath

Delete the duplicate rows from the original table.

DELETE KNOWNGOOD FROM [HASHCODES].[dbo].[KNOWNGOOD], [HASHCODES].[dbo].[holdkey] WHERE KNOWNGOOD.HASHMD5_FILEPath = holdkey.HASHMD5_FILEPath

Put the unique rows back in the original table.

INSERT [HASHCODES].[dbo].[KNOWNGOOD] SELECT * FROM [HASHCODES].[dbo].[holddups]

Delete the temporary tables.

DROP TABLE [HASHCODES].[dbo].[holdkey]

DROP TABLE [HASHCODES].[dbo].[holddups]

See Hash Code Verifier v1.0 for verifying published hash codes.

See PowerShell MD5 Hash Integrity Verifier To Detect File System Changes for an alternate approach.

Advertisements

One Response to Create a SQL Table of Known Good File Hash Values

  1. […] Maintain your own set of hash codes of known good software. See Create a SQL Table of Known Good File Hash Values […]