SCCM Database Queries: Find Executable Files by Name, Version and Install Count

Find all instances of a particular file by its distinct Version number (e.g. "FileVersion" property).  In this example, I'm looking for what versions of Sysinternals' PsExec.exe are in the environment, and how many instances were found for each version.

[Begin T-SQL]
SELECT DISTINCT 
[ExecutableName0],[FileVersion0], COUNT(*) AS QTY
FROM [dbo].[v_GS_INSTALLED_EXECUTABLE]
WHERE ExecutableName0 = 'psexec.exe'
GROUP BY ExecutableName0, FileVersion0
ORDER BY FileVersion0
[End T-SQL]

Script to Auto-Update Your Sysinternals Tools

Jason Faulkner posted a very cool Batch script to freshen your library of Sysinternals tools over at SysAdminGeek.com.  It queries the live.sysinternals.com\tools repository and downloads matching tools in your library.  If you haven't already checked it out, you should (click here).

Here's a version I've done in VBScript.  You can tweak it for your needs and even schedule it to run periodically to keep your tools up to date.

Const livetools = "\\live.sysinternals.com\tools"
Const localtools = "c:\sysinternals"

Set objFSO = CreateObject("Scripting.FileSystemObject")

On Error Resume Next
wscript.echo "info: connecting to live.sysinternal.com..."
Set objLiveFolder = objFSO.GetFolder(livetools)
If err.Number <> 0 Then
	wscript.echo "fail: unable to connect"
	wscript.quit(1)
End If

If objFSO.FolderExists(localTools) = False Then
	wscript.echo "fail: local folder not found"
	wscript.quit(2)
End If

For each objFile in objLiveFolder.Files
	fileName = objFile.Name
	If objFSO.FileExists(localtools & "\" & fileName) Then
		wscript.echo "info: updating file " & fileName & "..."
		sourceFile = liveTools & "\" & fileName
		targetFile = localTools & "\" & fileName
		objFSO.CopyFile sourceFile, targetFile, True
	End If
Next

Set objFolder = Nothing
Set objFSO = Nothing

If I had more time I'd do this in KiXtart and Powershell, but I'll leave that to someone else.  Enjoy!