Hey CheckYourLogs fans!

While working on a recent engagement, we wanted to populate a 1E Shopping site with a rather lengthy list of icons for client-facing applications.  The admin had already painstakingly added all the icons into Software Center, but we couldn’t find a method to pull them out en masse.  We found a script on https://www.sqlshack.com that could be used to extract icons from a SQL database, and my client made a few tweaks to make it work with Configuration Manager.  Thanks for letting me share it, Mike!

## https://www.sqlshack.com/export-sql-server-filestream-objects-with-powershell-and-ssis/

$Server = "SCCM.SERVER.FQDN";
$Database = "cm_ps1";
$Dest = "C:\Drivers\Icons\";
$bufferSize = 8192;

$Sql = "SELECT
Distinct([Icon]), [Title]
FROM [CM_QD1].[dbo].[CI_LocalizedCIClientProperties]
where icon is not null
";

$con = New-Object Data.SqlClient.SqlConnection;
$con.ConnectionString = "Data Source=$Server;" +
"Integrated Security=True;" +
"Initial Catalog=$Database";
$con.Open();

Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Export ICON objects Started ...");

$cmd = New-Object Data.SqlClient.SqlCommand $Sql, $con;
$rd = $cmd.ExecuteReader();

$out = [array]::CreateInstance('Byte', $bufferSize)

While ($rd.Read())
{
try
{
Write-Output ("Exporting Objects from FILESTREAM container: {0}" -f $rd.GetString(1));
# New BinaryWriter
$fs = New-Object System.IO.FileStream ($Dest + $rd.GetString(1) + ".png"), Create, Write;
$bw = New-Object System.IO.BinaryWriter $fs;

$start = 0;
# Read first byte stream
$received = $rd.GetBytes(0, $start, $out, 0, $bufferSize - 1);
While ($received -gt 0)
{
$bw.Write($out, 0, $received);
$bw.Flush();
$start += $received;
# Read next byte stream
$received = $rd.GetBytes(0, $start, $out, 0, $bufferSize - 1);
}
$bw.Close();
$fs.Close();
}
catch
{
Write-Output ($_.Exception.Message)
}
finally
{
$fs.Dispose();
}
}

$rd.Close();
$cmd.Dispose();
$con.Close();

Write-Output ("Finished");
Read-Host -Prompt "Press Enter to exit"

Hope this helps!

É