Extracting SQL Queries from .NET Apps with WinDbg

In analysing an application that demonstrated symptoms of the Chatty I/O Antipattern I wanted to extract all the SQL queries performed by application.

This can be done with packet capture tools such as netsh trace and WireShark

If using Wireshark you can use filter tds

You can then expand the TDS query packet and select the relevant item and press Ctrl+Shift+I and right click Apply as Column to see the SQL queries

This will work if the SQL queries are not encrypted.

wireshar

From a .NET application we can also easily get this information using WinDbg.

In this case we use SOSEX extension to simplify creating the breakpoints http://stevestechspot.com/SOSEXV40NowAvailable.aspx

We can then set breakpoints for SQL commands before they are executed then dump and inspect the parameters.

Manually this would look something like this. Typed commands in bold.

0:018> .loadby sos clr
0:018> .load c:\support\sosex.dll
0:018> !mbm *!System.Data.SqlClient.SqlCommand.ExecuteReader
Breakpoint set at System.Data.SqlClient.SqlCommand.ExecuteReader() in AppDomain 033332b0.
Breakpoint set at System.Data.SqlClient.SqlCommand.ExecuteReader(System.Data.CommandBehavior) in AppDomain 033332b0.
Breakpoint set at System.Data.SqlClient.SqlCommand.ExecuteReader(System.Data.CommandBehavior, System.String) in AppDomain 033332b0.
0:018> !mbm *!System.Data.SqlClient.SqlCommand.ExecuteNonQuery
Breakpoint set at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() in AppDomain 033332b0.
*** WARNING: Unable to verify checksum for C:\WINDOWS\assembly\NativeImages_v4.0.30319_32\System.Data\8eebbeb9e37bb40cf99a4cd06c26e65e\System.Data.ni.dll
*** ERROR: Module load completed but symbols could not be loaded for C:\WINDOWS\assembly\NativeImages_v4.0.30319_32\System.Data\8eebbeb9e37bb40cf99a4cd06c26e65e\System.Data.ni.dll
0:018> g
ModLoad: 15af0000 15f94000 DevExpress.XtraBars.v14.2.dll
ModLoad: 15fa0000 16444000 DevExpress.XtraBars.v14.2.dll
Breakpoint 2 hit
eax=00000000 ebx=1340b228 ecx=5696bfac edx=010fdb0c esi=00000000 edi=010fdb10
eip=56d7be06 esp=010fdad4 ebp=010fdb18 iopl=0 nv up ei ng nz ac pe cy
cs=0023 ss=002b ds=002b es=002b fs=0053 gs=002b efl=00200297
System_Data_ni+0x42be06:
56d7be06 8b0d2c139556 mov ecx,dword ptr [System_Data_ni+0x132c (5695132c)] ds:002b:5695132c=08b93648
*** ERROR: Module load completed but symbols could not be loaded for C:\WINDOWS\assembly\NativeImages_v4.0.30319_32\System.Core\9be722d8c68270a743a0aea762f40e2d\System.Core.ni.dll
*** ERROR: Module load completed but symbols could not be loaded for C:\WINDOWS\assembly\NativeImages_v4.0.30319_32\mscorlib\f42a27fd30d9a54b9eb8cba239c2611f\mscorlib.ni.dll
*** WARNING: Unable to verify checksum for C:\WINDOWS\assembly\NativeImages_v4.0.30319_32\System.Windows.Forms\c9287eaf630bc35ce3ac0111feec2bdb\System.Windows.Forms.ni.dll
*** ERROR: Module load completed but symbols could not be loaded for C:\WINDOWS\assembly\NativeImages_v4.0.30319_32\System.Windows.Forms\c9287eaf630bc35ce3ac0111feec2bdb\System.Windows.Forms.ni.dll
0:000> !clrstack -p
OS Thread Id: 0x2884 (0)
Child SP IP Call Site
010fdad4 56d7be06 System.Data.SqlClient.SqlCommand.ExecuteReader(System.Data.CommandBehavior, System.String)
PARAMETERS:
this (0x010fdae0) = 0x1340b228
behavior (<CLR reg>) = 0x00000000
method (0x010fdb20) = 0x052b64ac

010fdb24 56d7bc1e System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(System.Data.CommandBehavior)
PARAMETERS:
this = <no data>
behavior = <no data>

010fdb34 56e63e4d System.Data.Common.DbCommand.ExecuteReader(System.Data.CommandBehavior)
PARAMETERS:
this = <no data>
behavior = <no data>

010fdb3c 0dfb840a System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.b__c(System.Data.Common.DbCommand, System.Data.Entity.Infrastructure.Interception.DbCommandInterceptionContext`1)
PARAMETERS:
t = <no data>
c = <no data>

etc, etc…

0:000> !DumpObj /d 1340b228
Name: System.Data.SqlClient.SqlCommand
pparamMethodTable: 56a67388
EEClass: 56969038
Size: 164(0xa4) bytes
File: C:\WINDOWS\Microsoft.Net\assembly\GAC_32\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll
Fields:
MT Field Offset Type VT Attr Value Name
7211fe74 40005a4 4 System.Object 0 instance 00000000 __identity
713c0bfc 40028cc 8 …ponentModel.ISite 0 instance 00000000 site
713b7490 40028cd c ….EventHandlerList 0 instance 00000000 events
7211fe74 40028cb 8d4 System.Object 0 static 055ef010 EventDisposed
72121974 4000ead 60 System.Int32 1 instance 42 ObjectID
7211fad4 4000eae 10 System.String 0 instance 13404ac4 _commandText
56a670e0 4000eaf 64 System.Int32 1 instance 0 _commandType
72121974 4000eb0 68 System.Int32 1 instance 3600 _commandTimeout
56a63cd8 4000eb1 6c System.Int32 1 instance 3 _updatedRowSource
72113798 4000eb2 94 System.Boolean 1 instance 0 _designTimeInvisible
72113798 4000eb3 95 System.Boolean 1 instance 0 _wasBatchModeColumnEncryptionSettingSetOnce
56a64784 4000eb4 70 System.Int32 1 instance 0 _columnEncryptionSetting
56a5d08c 4000eb5 14 …ent.SqlDependency 0 instance 00000000 _sqlDep
72113798 4000eb6 96 System.Boolean 1 instance 0 _inPrepare
72121974 4000eb7 74 System.Int32 1 instance -1 _prepareHandle
72113798 4000eb8 97 System.Boolean 1 instance 0 _hiddenPrepare
72121974 4000eb9 78 System.Int32 1 instance -1 _preparedConnectionCloseCount
72121974 4000eba 7c System.Int32 1 instance -1 _preparedConnectionReconnectCount
56a66b78 4000ebb 18 …rameterCollection 0 instance 1340b2f0 _parameters
56a66cd4 4000ebc 1c …ent.SqlConnection 0 instance 13405fc8 _activeConnection
72113798 4000ebd 98 System.Boolean 1 instance 0 _dirty
56a55e48 4000ebe 80 System.Int32 1 instance 0 _execType
56f66b20 4000ebf 20 …lClient._SqlRPC[] 0 instance 00000000 _rpcArrayOf1
56a63a24 4000ec0 24 …SqlClient._SqlRPC 0 instance 00000000 _rpcForEncryption
56a63d24 4000ec1 28 …t._SqlMetaDataSet 0 instance 00000000 _cachedMetaData
72107a74 4000ec2 2c …bject, mscorlib]] 0 instance 00000000 _reconnectionCompletionSource
56a5dccc 4000ec3 30 …+CachedAsyncState 0 instance 00000000 _cachedAsyncState
72121974 4000ec4 84 System.Int32 1 instance -1 _rowsAffected
72121974 4000ec5 88 System.Int32 1 instance -1 _rowsAffectedBySpDescribeParameterEncryption
56a6678c 4000ec6 34 …tificationRequest 0 instance 00000000 _notification
72113798 4000ec7 99 System.Boolean 1 instance 1 _notificationAutoEnlist
56a54a68 4000ec8 38 …nt.SqlTransaction 0 instance 00000000 _transaction
56a5a538 4000ec9 3c …letedEventHandler 0 instance 00000000 _statementCompletedEventHandler
56a68540 4000eca 40 …ParserStateObject 0 instance 00000000 _stateObj
72113798 4000ecb 9a System.Boolean 1 instance 0 _pendingCancel
72113798 4000ecc 9b System.Boolean 1 instance 0 _batchRPCMode
56962480 4000ecd 44 …PC, System.Data]] 0 instance 00000000 _RPCList
56f66b20 4000ece 48 …lClient._SqlRPC[] 0 instance 00000000 _SqlRPCBatchArray
56f66b20 4000ecf 4c …lClient._SqlRPC[] 0 instance 00000000 _sqlRPCParameterEncryptionReqArray
56957a30 4000ed0 50 …on, System.Data]] 0 instance 00000000 _parameterCollectionList
72121974 4000ed1 8c System.Int32 1 instance 0 _currentlyExecutingBatch
72121974 4000ed2 90 System.Int32 1 instance 0 _currentlyExecutingDescribeParameterEncryptionRPC
72113798 4000ed3 9c System.Boolean 1 instance 0 _isDescribeParameterEncryptionRPCCurrentlyInProgress
72113798 4000ed4 9d System.Boolean 1 instance 0 _internalEndExecuteInitiated
72113798 4000ed5 9e System.Boolean 1 instance 0 <CachingQueryMetadataPostponed>k__BackingField
56a625bc 4000ed6 54 …Server.SmiContext 0 instance 00000000 _smiRequestContext
56a5c79c 4000ed7 58 …+CommandEventSink 0 instance 00000000 _smiEventSink
56a5ab08 4000ed8 5c …DeferedProcessing 0 instance 00000000 _outParamEventSink
72121974 4000eac 678 System.Int32 1 static 42 _objectTypeCount
7212062c 4000ed9 3d4 System.String[] 0 static 051d3e7c PreKatmaiProcParamsNames
7212062c 4000eda 3d8 System.String[] 0 static 051d3ec4 KatmaiProcParamsNames
0:000> !DumpObj /d 13404ac4
Name: System.String
MethodTable: 7211fad4
EEClass: 71ca8d78
Size: 120(0x78) bytes
File: C:\WINDOWS\Microsoft.Net\assembly\GAC_32\mscorlib\v4.0_4.0.0.0__b77a5c561934e089\mscorlib.dll
String: Select * from abctax.[FN_GetLockDetails](@DocumentId)
Fields:
MT Field Offset Type VT Attr Value Name
72121974 400026f 4 System.Int32 1 instance 53 m_stringLength
7212051c 4000270 8 System.Char 1 instance 53 m_firstChar
7211fad4 4000274 48 System.String 0 shared static Empty
>> Domain:Value 033332b0:NotInit <<
0:000> !DumpObj /d 1340b2f0
!DumpObj /d 1340b2f0
Name: System.Data.SqlClient.SqlParameterCollection
MethodTable: 56a66b78
EEClass: 56968f2c
Size: 20(0x14) bytes
File: C:\WINDOWS\Microsoft.Net\assembly\GAC_32\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll
Fields:
MT Field Offset Type VT Attr Value Name
7211fe74 40005a4 4 System.Object 0 instance 00000000 __identity
72113798 4001110 c System.Boolean 1 instance 1 _isDirty
56952d90 4001112 8 …er, System.Data]] 0 instance 1340b32c _items
72120cd0 4001111 4f4 System.Type 0 static 0556405c ItemType
0:000> !DumpObj /d 1340b32c
Name: System.Collections.Generic.List`1[[System.Data.SqlClient.SqlParameter, System.Data]]
MethodTable: 56952d90
EEClass: 71ca56a0
Size: 24(0x18) bytes
File: C:\WINDOWS\Microsoft.Net\assembly\GAC_32\mscorlib\v4.0_4.0.0.0__b77a5c561934e089\mscorlib.dll
Fields:
MT Field Offset Type VT Attr Value Name
720ad5fc 400187c 4 System.__Canon[] 0 instance 1340b344 _items
72121974 400187d c System.Int32 1 instance 1 _size
72121974 400187e 10 System.Int32 1 instance 1 _version
7211fe74 400187f 8 System.Object 0 instance 00000000 _syncRoot
720ad5fc 4001880 4 System.__Canon[] 0 static <no information>
0:000> !DumpArray /d 1340b344
Name: System.Data.SqlClient.SqlParameter[]
MethodTable: 56a69c20
EEClass: 71ca8ea8
Size: 28(0x1c) bytes
Array: Rank 1, Number of elements 4, Type CLASS
Element Methodtable: 56a67980
[0] 13404b3c
[1] null
[2] null
[3] null
0:000> !DumpObj /d 13404b3c
Name: System.Data.SqlClient.SqlParameter
MethodTable: 56a67980
EEClass: 569690a8
Size: 116(0x74) bytes
File: C:\WINDOWS\Microsoft.Net\assembly\GAC_32\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll
Fields:
MT Field Offset Type VT Attr Value Name
7211fe74 40005a4 4 System.Object 0 instance 00000000 __identity
56a68674 40010ee 8 …qlClient.MetaType 0 instance 00000000 _metaType
56a55a90 40010ef c …ient.SqlCollation 0 instance 00000000 _collation
7211fad4 40010f0 10 System.String 0 instance 00000000 _xmlSchemaCollectionDatabase
7211fad4 40010f1 14 System.String 0 instance 00000000 _xmlSchemaCollectionOwningSchema
7211fad4 40010f2 18 System.String 0 instance 00000000 _xmlSchemaCollectionName
7211fad4 40010f3 1c System.String 0 instance 00000000 _udtTypeName
7211fad4 40010f4 20 System.String 0 instance 00000000 _typeName
72120cd0 40010f5 24 System.Type 0 instance 00000000 _udtType
7211fc1c 40010f6 28 System.Exception 0 instance 00000000 _udtLoadError
7211fad4 40010f7 2c System.String 0 instance 056ce0d8 _parameterName
72120fa4 40010f8 64 System.Byte 1 instance 0 _precision
72120fa4 40010f9 65 System.Byte 1 instance 0 _scale
72113798 40010fa 66 System.Boolean 1 instance 0 _hasScale
56a68674 40010fb 30 …qlClient.MetaType 0 instance 00000000 _internalMetaType
56a64ae4 40010fc 34 …lClient.SqlBuffer 0 instance 00000000 _sqlBufferReturnValue
56a6b70c 40010fd 38 …qlTypes.INullable 0 instance 00000000 _valueAsINullable
72113798 40010fe 67 System.Boolean 1 instance 0 _isSqlParameterSqlType
72113798 40010ff 68 System.Boolean 1 instance 0 _isNull
72113798 4001100 69 System.Boolean 1 instance 0 _coercedValueIsSqlType
72113798 4001101 6a System.Boolean 1 instance 0 _coercedValueIsDataFeed
72121974 4001102 50 System.Int32 1 instance -1 _actualSize
56a5d704 4001103 3c …SqlCipherMetadata 0 instance 00000000 _columnEncryptionCipherMetadata
72113798 4001104 6b System.Boolean 1 instance 0 <HasReceivedMetadata>k__BackingField
72113798 4001105 6c System.Boolean 1 instance 0 <ForceColumnEncryption>k__BackingField
7211fe74 4001106 40 System.Object 0 instance 13404bb0 _value
7211fe74 4001107 44 System.Object 0 instance 1340b2f0 _parent
56a67934 4001108 54 System.Int32 1 instance 0 _direction
72121974 4001109 58 System.Int32 1 instance 0 _size
72121974 400110a 5c System.Int32 1 instance 0 _offset
7211fad4 400110b 48 System.String 0 instance 00000000 _sourceColumn
56a55e10 400110c 60 System.Int32 1 instance 0 _sourceVersion
72113798 400110d 6d System.Boolean 1 instance 0 _sourceColumnNullMapping
72113798 400110e 6e System.Boolean 1 instance 0 _isNullable
7211fe74 400110f 4c System.Object 0 instance 00000000 _coercedValue
0:000> !DumpObj /d 13404bb0
Name: System.Int32
MethodTable: 72121974
EEClass: 71c068fc
Size: 12(0xc) bytes
File: C:\WINDOWS\Microsoft.Net\assembly\GAC_32\mscorlib\v4.0_4.0.0.0__b77a5c561934e089\mscorlib.dll
Fields:
MT Field Offset Type VT Attr Value Name
72121974 400058c 4 System.Int32 1 instance 1234738 m_value
0:000> !DumpObj /d 056ce0d8
Name: System.String
MethodTable: 7211fad4
EEClass: 71ca8d78
Size: 34(0x22) bytes
File: C:\WINDOWS\Microsoft.Net\assembly\GAC_32\mscorlib\v4.0_4.0.0.0__b77a5c561934e089\mscorlib.dll
String: DocumentId
Fields:
MT Field Offset Type VT Attr Value Name
72121974 400026f 4 System.Int32 1 instance 10 m_stringLength
7212051c 4000270 8 System.Char 1 instance 44 m_firstChar
7211fad4 4000274 48 System.String 0 shared static Empty

To automate this I created a JavaScript which can be run from WinDbg in the Windows 10 store . This will automatically dump the SQL command and parameters. This code was written just as a prototype so may need adjustments to meet your exact requirements.


"use strict";

function initializeScript()
{
return [new host.apiVersionSupport(1, 3)];
}

function GetDatabaseQuery()
{
var ctl = host.namespace.Debugger.Utility.Control;
var output = ctl.ExecuteCommand("!clrstack -p");
var sqlCommand = false;

var commandReference = "";

for (var line of output)
{
if (line.indexOf("System.Data.SqlClient.SqlCommand.ExecuteReader") >=0 ||
line.indexOf("System.Data.SqlClient.SqlCommand.ExecuteNonQuery") >=0 )
{
sqlCommand = true;
}

if (line.indexOf("this (") >= 0 && sqlCommand == true)
{
commandReference = line.split("=")[1].trim();
break;
}
}

if (commandReference != "")
{
var commandtext = "";
var parameters = "";
output = ctl.ExecuteCommand("!DumpObj /d " + commandReference);
for (var line of output)
{
if (line.indexOf("_commandText")>=0)
{
commandtext = line.substring(60,68);
}

if (line.indexOf("_parameters")>=0)
{
parameters = line.substring(60,68);
}
}

if (commandtext != "")
{
var output = ctl.ExecuteCommand("!DumpObj /d " + commandtext);
var sqlCommand = "";
for (var line of output)
{
if (sqlCommand != "")
{
if (line.indexOf("Fields:") >= 0)
{
break;
}

sqlCommand+="\n" + line;
}
if (line.indexOf("String:") >= 0)
{
sqlCommand = line.substring(13);
}
}
host.diagnostics.debugLog("SqlCommand=" + sqlCommand + "\n");
}

host.diagnostics.debugLog("SqlParameters=");
if (parameters == "00000000" || parameters == "")
{
host.diagnostics.debugLog("NULL\n");
}
else
{
var output = ctl.ExecuteCommand("!DumpObj /d " + parameters);
var items = "";
for (var line of output)
{
if (line.indexOf("_items") >= 0)
{
items = line.substring(60,68);
break;
}
}

if (items != "")
{
var output = ctl.ExecuteCommand("!DumpObj /d " + items );
for (var line of output)
{
if (line.indexOf("_items") >= 0)
{
items = line.substring(60,68);
break;
}
}

var output = ctl.ExecuteCommand("!DumpArray /d " + items );
for (var line of output)
{
if (line.startsWith("["))
{
var val = line.split(" ")[1].trim();
if (val != "null")
{
var arrayOutput = ctl.ExecuteCommand("!DumpObj /d " + val )
var _paramName = "";
var _value = "";
for (var item of arrayOutput)
{
if (item.indexOf("_parameterName") >= 0)
{
_paramName = item.substring(60,68);
}

if (item.indexOf("_value") >= 0)
{
_value = item.substring(60,68);
}
}

arrayOutput = ctl.ExecuteCommand("!DumpObj /d " + _paramName);
var ParameterName = "";
var ParameterValue = "";
for (var item of arrayOutput)
{
if (item.indexOf("String:") >= 0)
{
ParameterName = item.substring(13);
break;
}
}

arrayOutput = ctl.ExecuteCommand("!DumpObj /d " + _value);
for (var item of arrayOutput)
{
if (item.indexOf("String:") >= 0)
{
ParameterValue = item.substring(13);
break;
}

if (item.indexOf("instance") >= 0)
{
ParameterValue = item.substring(60,68).trim();
break;
}
}

host.diagnostics.debugLog(ParameterName + "=" + ParameterValue + ";");

}
}
}
host.diagnostics.debugLog("\n");
}

}

}

}
function invokeScript()
{
//
// Insert your script content here. This method will be called whenever the script is
// invoked from a client.
//
// See the following for more details:
//
// https://aka.ms/JsDbgExt
//
GetDatabaseQuery()
}
;

This can then be fully automated to collect all the SQL queries into a log file. This log file can then be processed so data is easier to view , such as in excel.

Example usage for this scenario:

0:020> .logopen C:\support\sql.log
Opened log file ‘c:\support\sql.log’
0:020> .loadby sos clr
0:020> .load c:\support\sosex.dll
0:020> !mbm *!System.Data.SqlClient.SqlCommand.ExecuteReader “.scriptrun C:\support\SqlExtractors.js;!clrstack;g”
Breakpoint set at System.Data.SqlClient.SqlCommand.ExecuteReader() in AppDomain 02683c10.
Breakpoint set at System.Data.SqlClient.SqlCommand.ExecuteReader(System.Data.CommandBehavior) in AppDomain 02683c10.
Breakpoint set at System.Data.SqlClient.SqlCommand.ExecuteReader(System.Data.CommandBehavior, System.String) in AppDomain 02683c10.
*** WARNING: Unable to verify checksum for C:\WINDOWS\assembly\NativeImages_v4.0.30319_32\System.Data\8eebbeb9e37bb40cf99a4cd06c26e65e\System.Data.ni.dll
*** ERROR: Module load completed but symbols could not be loaded for C:\WINDOWS\assembly\NativeImages_v4.0.30319_32\System.Data\8eebbeb9e37bb40cf99a4cd06c26e65e\System.Data.ni.dll
0:020> !mbm *!System.Data.SqlClient.SqlCommand.ExecuteNonQuery “.scriptrun C:\support\SqlExtractors.js;!clrstack;g”
Breakpoint set at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() in AppDomain 02683c10.
0:020> g

< Reproduce Problem>

0:0020> .logclose

PowerShell script to analyse the generated log file and produce a CSV:

</pre>
$data = Get-Content C:\support\sql.log
$outputFileName = C:\support\sql.csv
$table = New-Object System.Data.DataTable
$table.Columns.Add("Query")
$table.Columns.Add("Parameters")
$table.Columns.Add("Code Location")

$currentSQLCommand = ""

$currentStack = ""
$currentSQLParam = ""
$sqlParam = $false
$stack = $false
$sqlCommand = ""
$c = 0
ForEach ($line in $data)
{
$c++
Write-Progress -Activity "Extracting SQL data" -PercentComplete ($c* (100/$data.Count))
if ($stack -eq $true)
{
if ($line.Contains("JavaScript script"))
{
$stack = $false
#uncomment these 3 lines and change "Abc." to prefix of desired DLL, if you want only the first item in stack matching
#$i = $currentStack.IndexOf("Abc.")
#$j = $currentStack.IndexOf("`r",$i)
#$currentStack = $currentStack.Substring($i,$j-$i)
[void]$table.Rows.Add($sqlCommand,$currentSQLParam,$currentStack)
}
else
{
if ($line.Length -gt 18)
{
$currentStack += "`r`n" + $line.Substring(18)
}
}
}
if ($line.Contains("Child SP IP Call Site"))
{
$stack = $true
$currentStack = ""
}

if ($sqlParam -eq $true)
{
if ($line.Contains("OS Thread Id"))
{
$sqlParam = $false
}
else
{
if ($line.Contains("="))
{
$currentSQLParam += $line.Substring(0,$line.IndexOf(";")+1)
}
}
}

if ($line.StartsWith("SqlParameters="))
{
$sqlCommand = $currentSQLCommand
$currentSQLParam = ""
$currentSQLCommand = ""
$sqlParam = $true
}
if ($currentSQLCommand -ne "")
{
$currentSQLCommand+="`r`n" + $line
}
if ($line.StartsWith("SqlCommand="))
{
$stack = ""
$currentSQLCommand = $line.Substring(11)
}
}

$table | Export-Csv -NoTypeInformation -Path $outputFilename
<pre>

 

 

 

Posted in Uncategorized | Leave a comment

Extract DNS Records to CSV with PowerShell

In some cases to do an analysis of DNS records it may be desirable to extract records into CSV format. Unfortunately PowerShell cmdlet Get-DnsServerResourceRecord has the important data in a field called “RecordData” which stores relevant data in different properties depending on the resource record type. This prevents outputting any useful information directly to CSV.
This script will add an additional field “Data” which formats RecordData based on resource record type.

Currently this script supports following resource record types:

A
NS
SOA
CNAME
SRV
AAAA
PTR
MX
TXT

Unsupported record types will still be logged but data field will have message “Unsupported Record Type”. Update the switch statement to support additional record types as requried.

To use

Get-AllDnsResourceRecords -DnsServer 8.8.8.8 | Export-Csv -NoTypeInformation -Path c:\support\DnsRecords.csv

Script is available here https://github.com/chentiangemalc/PowerShellScripts/blob/master/Get-AllDnsResourceRecords.ps1

 
[CmdletBinding()]
param(
[Parameter(Position=0)]
[String]$DNSServer)

$Zones = @(Get-DnsServerZone -ComputerName $DNSServer)
$Data = @() 
ForEach ($Zone in $Zones) {
	($Zone | Get-DnsServerResourceRecord -ComputerName $DNSServer) | `
        Select-Object -Property `
            @{Label="Zone Name";expression={( $Zone.ZoneName )}},`
            DistinguishedName,`
            HostName,`
            RecordClass,`
            RecordType,`
            Timestamp,`
            TimeToLive,`
            @{label="Data";expression={
                $r = $_.RecordData
                switch ($_.RecordType)
                {
                    "A" { $r.IPv4Address.IPAddressToString }
                    "NS" { $r.NameServer }
                    "SOA" { 
                        "ExpireLimit=$($r.ExpireLimit);"+
                        "MinimumTimeToLive=$($r.MinimumTimeToLive);"+
                        "PrimaryServer=$($r.PrimaryServer);"+
                        "RefreshInterval=$($r.RefreshInterval);"+
                        "ResponsiblePerson=$($r.ResponsiblePerson);"+
                        "RetryDelay=$($r.RetryDelay);"+
                        "SerialNumber=$($r.SerialNumber)"

                    }
                    "CNAME" {  $r.HostNameAlias }
                    "SRV"{ 
                        "DomainName=$($r.DomainName);"+
                        "Port=$($r.Port);"+
                        "Priority=$($r.Priority);"+
                        "Weight=$($r.Weight)"
                    }
                    "AAAA" { $r.IPv6Address.IPAddressToString }
                    "PTR" { $r.PtrDomainName } 
                    "MX" {
                        "MailExchange=$($r.MailExchange);"+
                        "Prefreence=$($r.Preference)"
                    }
                    "TXT" { $r.DescriptiveText }
                    Default { "Unsupported Record Type" }
                }}
            }
}
Posted in Uncategorized | 2 Comments

Export OU Structure To Image with PowerShell

Here is a PowerShell script that will generate an image of OU structure for the domain user running script is a part of.

Does not require Remote Server Administration Tools Installed.

Image can be in formats

.BMP
.GIF
.JPG
.PNG
.TIFF

Script is on GitHub here https://raw.githubusercontent.com/chentiangemalc/PowerShellScripts/master/Export-OUsToImage.ps1

 
[CmdletBinding()]
param(
[Parameter(Position=0)]
[ValidateScript({
    if( -Not ($_.DirectoryName | Test-Path) ){
        throw "File or folder does not exist"
    }
    return $true
})]
[System.IO.FileInfo]$Path)

Add-Type -AssemblyName 'System.Windows.Forms'
Add-Type -AssemblyName 'System.Drawing'
Add-Type -AssemblyName 'System.DirectoryServices'

# Folder icono, this code generated with following code
#$image = [System.Drawing.Image]::FromFile("C:\support\scripts\AD\folder.png")
#$ms = New-Object System.IO.MemoryStream
#$image.Save($ms,$image.RawFormat)
#$arr = $ms.ToArray()
#$result = "$FolderImage = @(```r`n"
#$c = 0
#ForEach ($a in $arr)
#{
#    $c++
#    $result+=("0x{0:x}," -f $a )
#    if ($c -gt 20)
#    {
#        $c =0
#        $result+="```r`n"
#    }
#
#}
#$result += ")"

[Byte[]]$folderImageBytes = @(`
0x89,0x50,0x4e,0x47,0xd,0xa,0x1a,0xa,0x0,0x0,0x0,0xd,0x49,0x48,0x44,0x52,0x0,0x0,0x0,0x30,0x0,`
0x0,0x0,0x30,0x8,0x6,0x0,0x0,0x0,0x57,0x2,0xf9,0x87,0x0,0x0,0x0,0x4,0x67,0x41,0x4d,0x41,0x0,`
0x0,0xb1,0x8f,0xb,0xfc,0x61,0x5,0x0,0x0,0x0,0x9,0x70,0x48,0x59,0x73,0x0,0x0,0xe,0xc2,0x0,0x0,`
0xe,0xc2,0x1,0x15,0x28,0x4a,0x80,0x0,0x0,0x1,0x61,0x49,0x44,0x41,0x54,0x68,0x43,0xed,0x92,0xb1,0x4a,`
0x3,0x41,0x14,0x45,0xa7,0xd0,0x3f,0x10,0x9b,0xa8,0x85,0xb8,0xc9,0x6a,0xeb,0x37,0xf8,0x5d,0xfe,0x89,0x6,`
0xd4,0x42,0x8c,0x9d,0x76,0x82,0x5d,0x36,0xbb,0xe0,0x4f,0x8,0x96,0x11,0x8d,0xfd,0x7b,0x4e,0xc2,0xad,0xe4,`
0xde,0x18,0x1b,0xf7,0x15,0x73,0xe0,0x74,0xb9,0xc3,0x99,0x9d,0xa4,0x42,0xa1,0x50,0x28,0x14,0x7e,0x62,0x17,`
0x69,0xdf,0xc6,0x69,0x92,0x5d,0xf8,0x38,0xf9,0x26,0xe6,0xdf,0x9e,0x63,0xde,0x2f,0x76,0x95,0xf6,0x72,0xcc,`
0x9c,0x45,0xfe,0x66,0x88,0x4b,0xd8,0x65,0xba,0x63,0x71,0x9b,0xda,0xfb,0x25,0xf2,0x5,0xbe,0x58,0xd8,0x5f,`
0xec,0xf5,0x12,0xab,0x88,0x9b,0x6d,0xf7,0xa7,0x81,0xfb,0x6c,0xe8,0xde,0xd5,0xbd,0x6a,0xb3,0xa1,0xd9,0xf3,`
0xc1,0x9b,0x3d,0xee,0x9e,0x21,0x71,0x3d,0x7e,0xbd,0xe5,0xde,0x54,0xf4,0xb0,0x3e,0xb5,0x69,0x65,0xfe,0xb0,`
0x73,0x8a,0x4c,0xcd,0xea,0xcb,0x93,0x3,0x22,0x98,0x5f,0xe2,0x15,0x99,0x9a,0x8,0x7f,0x1b,0xa5,0x35,0x43,`
0x43,0xa6,0x86,0xd,0x23,0x89,0x4c,0xd,0x1b,0x45,0x12,0x99,0x1a,0x36,0x8a,0x24,0x32,0x35,0x6c,0x14,0x49,`
0x64,0x6a,0xd8,0x28,0x92,0xc8,0xd4,0xb0,0x51,0x24,0x91,0xa9,0x61,0xa3,0x48,0x22,0x53,0xc3,0x46,0x91,0x44,`
0xa6,0x86,0x8d,0x22,0x89,0x4c,0xd,0x1b,0x45,0x12,0x99,0x1a,0x36,0x8a,0x24,0x32,0x35,0x6c,0x14,0x49,0x64,`
0x6a,0xd8,0x28,0x92,0xc8,0xd4,0xb0,0x51,0x24,0x91,0xa9,0x61,0xa3,0x48,0x22,0x53,0xc3,0x46,0x91,0x44,0xa6,`
0x86,0x8d,0x22,0x89,0x4c,0xd,0x1b,0x45,0x12,0x99,0x1a,0x36,0x8a,0x24,0x32,0x35,0x6c,0x14,0x49,0x64,0x6a,`
0xac,0xab,0x17,0x6c,0x18,0xc1,0xdc,0xf6,0x89,0x4c,0x8d,0xb5,0xa3,0x7b,0x36,0x8e,0xa0,0xb5,0xf5,0x4,0x99,`
0x1a,0x6b,0x8e,0x2b,0xeb,0x46,0x73,0x76,0x40,0x9f,0xe6,0xa6,0x77,0xef,0xaa,0x43,0x64,0xae,0xc7,0xa6,0xd5,`
0x20,0x3f,0xd7,0xed,0xf2,0xc9,0xd8,0x61,0xff,0x6a,0x5b,0x7f,0x2c,0xbf,0xbc,0xbd,0x9c,0x1c,0x21,0xaf,0x50,`
0x28,0x14,0xa,0x5,0x90,0xd2,0x37,0x8f,0x8a,0x6e,0xbd,0x1e,0x85,0x16,0xcf,0x0,0x0,0x0,0x0,0x49,0x45,`
0x4e,0x44,0xae,0x42,0x60,0x82)

$ms = New-Object System.IO.MemoryStream($folderImageBytes,0,$folderImageBytes.Length)
$ms.Position = 0
$folderImage = [System.Drawing.Image]::FromStream($ms,$true)
$ms.Close()

# todo: calculate dynamically depending on OU structure
$IMAGE_WIDTH = 800

# easy way $OUs = Get-ADOrganizationalUnit -Filter *
# but needs RSAT installed, so using System.DirectoryServices Instead
$rootDE = [System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain().GetDirectoryEntry()
$searcher = New-Object System.DirectoryServices.DirectorySearcher($rootDE)
$searcher.Filter = "(objectCategory=organizationalUnit)";

$OUs = @()
foreach ($result in $searcher.FindAll()) 
{
    $OUs += $result.Properties.distinguishedname
}

$treeView = New-Object System.Windows.Forms.TreeView
$imageList = New-Object System.Windows.Forms.ImageList
$imageList.Images.Add($folderImage)
$treeView.ImageList = $imageList
$treeView.Top =0
$treeView.Left =0

ForEach ($ou in $OUs)
{

    $domain =  $OU.Substring($OU.IndexOf("DC=")+3).Replace(",DC=",".")
    $SplitDN = $OU.Substring(0,$OU.IndexOf(",DC=")).`
        Replace(",OU=","\").`
        Replace(",CN=","\").`
        Replace("OU=","").`
        Replace("CN=","").Split("\")

    $ReversedDN = New-Object System.Text.StringBuilder
    [void]$reversedDn.Append($domain)
    For ($i=$SplitDN.Count-1;$i -ge 0;$i--)
    {
        [void]$ReversedDn.Append("\$($SplitDN[$i])")
    }

    $currentNode = $null
    
    Write-Host $ReversedDN.ToString()

    ForEach ($item in $ReversedDN.ToString().Split("\"))
    { 
        if ($currentNode -eq $null)
        {
            $currentNode = $treeView.Nodes[$item]
            if ($currentNode -eq $null)
            {
                $currentNode = $treeView.Nodes.Add($item)
                $currentNode.Name = $item
            }
    
        }
        else
        {
            if ($currentNode.Nodes[$item] -eq $null)
            {
                $currentNode = $currentNode.Nodes.Add($item)
                $currentNode.Name = $item 
            }
            else
            {
                $currentNode = $currentNode.Nodes[$item]
            }
        }
    }
}

[void]$treeView.ExpandAll()

$treeView.Width = $IMAGE_WIDTH
$treeView.Height = ($treeView.GetNodeCount($true)+1) * ($treeView.ItemHeight)

$bmp = New-Object System.Drawing.Bitmap($treeView.Width, $treeView.Height)
[void]$treeView.DrawToBitmap($bmp,(New-Object System.Drawing.Rectangle(0, 0, $bmp.Width, $bmp.Height)))
[void]$bmp.Save($Path.FullName)
Posted in Uncategorized | Leave a comment

Convert NetSh Trace ETL to PCAP with PowerShell

In-built packet capture in Windows 7 / Server 2008 R2 and later operating systems is incredibly useful tool with netsh trace. Unfortunately large ETL files are super slow to load in Microsoft Message Analyzer, and for many network analysis scenarios I prefer Wireshark’s layout. While Message Analyzer can open the ETL files and convert them to suitable format for Wireshark it is quite a bit of waiting, I just want to open the logs.

Here is a script I wrote to transform the .ETL file into a .PCAP file which can be opened in Wireshark and network packets inspected.

Currently I have only tested this with .ETL files generated from Windows 10.

In this case I had to use inline C# as using native PowerShell was far too slow.

Run this script then you can perform conversions with this command:

./ConvertEtl-ToPcap.ps1 -Path C:\support\NetTrace.etl -Destination C:\support\NetTrace.pcap

A version in C# for Visual Studio is available here https://github.com/chentiangemalc/EtlToCap

The compiled version of that is located here https://github.com/chentiangemalc/EtlToCap/raw/master/EtlToCap/bin/Debug/EtlToCap.exe

Utilise compiled version:
EtlToCap NetTrace.Etl NetTrace.pcap

Script is available here on GitHub https://github.com/chentiangemalc/PowerShellScripts/blob/master/ConvertEtl-ToPcap.ps1

[CmdletBinding()]
param(
[Parameter(Position=0)]
[ValidateScript({
    if( -Not ($_ | Test-Path) ){
        throw "File or folder $_ does not exist"
    }

    if($_.Extension -ne ".etl"){
        throw "Source file must be .etl file"
    }
    return $true
})]
[System.IO.FileInfo]$Path,

[Parameter(Position=1)]
[ValidateScript({
    if( -Not ($path.DirectoryName | Test-Path) ){
        throw "File or folder does not exist"
    }

    if($_.Extension -ne ".pcap") {
        throw "Estination file must be .pcap file"
    }
    return $true
})]
[System.IO.FileInfo]$Destination,

[Parameter(Position=2)]
[Uint32]$MaxPacketSizeBytes = 65536)


$csharp_code = @'
using System;
using System.Collections.Generic;
using System.Diagnostics.Eventing.Reader;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace chentiangemalc
{
    public static class NetworkRoutines
    {
    public static long ConvertEtlToPcap(string source, string destination, UInt32 maxPacketSize)
        {
            int result = 0;
            using (BinaryWriter writer = new BinaryWriter(File.Open(destination, FileMode.Create)))
            {

                UInt32 magic_number = 0xa1b2c3d4;
                UInt16 version_major = 2;
                UInt16 version_minor = 4;
                Int32 thiszone = 0;
                UInt32 sigfigs = 0;
                UInt32 snaplen = maxPacketSize;
                UInt32 network = 1; // LINKTYPE_ETHERNET

                writer.Write(magic_number);
                writer.Write(version_major);
                writer.Write(version_minor);
                writer.Write(thiszone);
                writer.Write(sigfigs);
                writer.Write(snaplen);
                writer.Write(network);

                long c = 0;
                long t = 0;
                using (var reader = new EventLogReader(source, PathType.FilePath))
                {
                    EventRecord record;
                    while ((record = reader.ReadEvent()) != null)
                    {
                        c++;
                        t++;
                        if (c == 10000)
                        {
                            Console.WriteLine(String.Format("Processed {0} events with {1} packets processed",t,result));
                            c = 0;
                        }
                        using (record)
                        {
                            if (record.ProviderName == "Microsoft-Windows-NDIS-PacketCapture")
                            {
                                result++;
                                DateTime timeCreated = (DateTime)record.TimeCreated;
                                UInt32 ts_sec = (UInt32)((timeCreated.Subtract(new DateTime(1970, 1, 1))).TotalSeconds);
                                UInt32 ts_usec = (UInt32)(((timeCreated.Subtract(new DateTime(1970, 1, 1))).TotalMilliseconds) - ((UInt32)((timeCreated.Subtract(new DateTime(1970, 1, 1))).TotalSeconds * 1000))) * 1000;
                                UInt32 incl_len = (UInt32)record.Properties[2].Value;
                                if (incl_len > maxPacketSize)
                                {
                                   Console.WriteLine(String.Format("Packet size of {0} exceeded max packet size {1}, packet ignored",incl_len,maxPacketSize));
                                }
                                UInt32 orig_len = incl_len;

                                writer.Write(ts_sec);
                                writer.Write(ts_usec);
                                writer.Write(incl_len);
                                writer.Write(orig_len);
                                writer.Write((byte[])record.Properties[3].Value);

                            }
                        }
                    }
                }
            }
            return result;
        }
    }
}
'@

Add-Type -Type $csharp_code

$result = [chentiangemalc.NetworkRoutines]::ConvertEtlToPcap($Path.FullName,$Destination.FullName,$MaxPacketSizeBytes)

Write-Host "$result packets converted."

Warning: IF you are a regular NetSh Trace user please remember to store log files in a path outside the user profile, or you may get numbers such as .000 appended to your user profile on Windows in-place upgrades.

Posted in Uncategorized | Leave a comment

Unable to Connect to iSCSI Targets with Authentication Type in PowerShell

Attempting to connect to iSCSI targets in PowerShell was failing when specifying Authentication Type. The authentication type was being specified as per Microsoft documentation here :

-AuthenticationType.
Specifies the type of authentication to use when logging into the target. The acceptable values for this parameter are:
  • None
  • OneWayCHAP
  • MutualCHAP

The default value is None.

Let’s check how the command makes a connection. Using PowerShell cmd

Get-Command -Type All -Name Connect-IscsiTarget | Format-List *

We can see in ScriptBlock it is using WMI Microsoft.Management.Infrastructure.CimInstance#MSFT_iSCSISession

Using cmd

find "MSFT_iSCSISession" C:\windows\system32\wbem\*.mof

We can see this is defined in MOF file C:\WINDOWS\SYSTEM32\WBEM\ISCSIWMIV2.MOF
We can open the MOF file in a text editor and note the GUID here

ClsId = “{52C84ACA-027A-4536-A74A-E0BB50C44782}”;

We can now use this to find the DLL that implements the WMI method:

C:\Windows>reg query hkcr\clsid\{52C84ACA-027A-4536-A74A-E0BB50C44782}\InProcServer32

HKEY_CLASSES_ROOT\clsid\{52C84ACA-027A-4536-A74A-E0BB50C44782}\InProcServer32
    (Default)    REG_EXPAND_SZ    %systemroot%\system32\iscsiwmiv2.dll
    ThreadingModel    REG_SZ    Both

Analysis of DLL %systemroot%\system32\iscsiwmiv2.dll shows the authentication types are all in upper case.

  • NONE
  • ONEWAYCHAP
  • MUTUALCHAP

.text:1000E437 loc_1000E437: ; CODE XREF: MSFT_iSCSITarget_Invoke_Connect(x,x,x,x,x,x,x)+27Bj
.text:1000E437 ; MSFT_iSCSITarget_Invoke_Connect(x,x,x,x,x,x,x)+280j
.text:1000E437 cmp byte ptr [ebx+54h], 0
.text:1000E43B jz loc_1000E50F
.text:1000E441 mov edx, [ebx+50h]
.text:1000E444 or eax, 80h
.text:1000E449 mov [esp+0B38h+var_B10], eax
.text:1000E44D mov esi, offset Str2 ; “NONE”
.text:1000E452 mov [esp+0B38h+Memory], edi
.text:1000E456 mov ecx, edx
.text:1000E458
.text:1000E458 loc_1000E458: ; CODE XREF: MSFT_iSCSITarget_Invoke_Connect(x,x,x,x,x,x,x)+2C8j
.text:1000E458 mov di, [ecx]
.text:1000E45B cmp di, [esi]
.text:1000E45E jnz short loc_1000E480
.text:1000E460 test di, di
.text:1000E463 jz short loc_1000E47A
.text:1000E465 mov di, [ecx+2]
.text:1000E469 cmp di, [esi+2]
.text:1000E46D jnz short loc_1000E480
.text:1000E46F add ecx, 4
.text:1000E472 add esi, 4
.text:1000E475 test di, di
.text:1000E478 jnz short loc_1000E458
.text:1000E47A
.text:1000E47A loc_1000E47A: ; CODE XREF: MSFT_iSCSITarget_Invoke_Connect(x,x,x,x,x,x,x)+2B3j
.text:1000E47A xor esi, esi
.text:1000E47C mov ecx, esi
.text:1000E47E jmp short loc_1000E487
.text:1000E480 ; —————————————————————————
.text:1000E480
.text:1000E480 loc_1000E480: ; CODE XREF: MSFT_iSCSITarget_Invoke_Connect(x,x,x,x,x,x,x)+2AEj
.text:1000E480 ; MSFT_iSCSITarget_Invoke_Connect(x,x,x,x,x,x,x)+2BDj
.text:1000E480 sbb ecx, ecx
.text:1000E482 or ecx, 1
.text:1000E485 xor esi, esi
.text:1000E487
.text:1000E487 loc_1000E487: ; CODE XREF: MSFT_iSCSITarget_Invoke_Connect(x,x,x,x,x,x,x)+2CEj
.text:1000E487 neg ecx
.text:1000E489 sbb ecx, ecx
.text:1000E48B and ecx, esi
.text:1000E48D mov esi, offset aMutualchap ; “MUTUALCHAP”
.text:1000E492 mov [esp+0B38h+Memory], ecx
.text:1000E496 mov ecx, edx
.text:1000E498
.text:1000E498 loc_1000E498: ; CODE XREF: MSFT_iSCSITarget_Invoke_Connect(x,x,x,x,x,x,x)+308j
.text:1000E498 mov di, [ecx]
.text:1000E49B cmp di, [esi]
.text:1000E49E jnz short loc_1000E4C0
.text:1000E4A0 test di, di
.text:1000E4A3 jz short loc_1000E4BA
.text:1000E4A5 mov di, [ecx+2]
.text:1000E4A9 cmp di, [esi+2]
.text:1000E4AD jnz short loc_1000E4C0
.text:1000E4AF add ecx, 4
.text:1000E4B2 add esi, 4
.text:1000E4B5 test di, di
.text:1000E4B8 jnz short loc_1000E498
.text:1000E4BA
.text:1000E4BA loc_1000E4BA: ; CODE XREF: MSFT_iSCSITarget_Invoke_Connect(x,x,x,x,x,x,x)+2F3j
.text:1000E4BA xor edi, edi
.text:1000E4BC mov ecx, edi
.text:1000E4BE jmp short loc_1000E4C7
.text:1000E4C0 ; —————————————————————————
.text:1000E4C0
.text:1000E4C0 loc_1000E4C0: ; CODE XREF: MSFT_iSCSITarget_Invoke_Connect(x,x,x,x,x,x,x)+2EEj
.text:1000E4C0 ; MSFT_iSCSITarget_Invoke_Connect(x,x,x,x,x,x,x)+2FDj
.text:1000E4C0 sbb ecx, ecx
.text:1000E4C2 or ecx, 1
.text:1000E4C5 xor edi, edi
.text:1000E4C7
.text:1000E4C7 loc_1000E4C7: ; CODE XREF: MSFT_iSCSITarget_Invoke_Connect(x,x,x,x,x,x,x)+30Ej
.text:1000E4C7 test ecx, ecx
.text:1000E4C9 jnz short loc_1000E4D3
.text:1000E4CB mov [esp+0B38h+Memory], 2
.text:1000E4D3
.text:1000E4D3 loc_1000E4D3: ; CODE XREF: MSFT_iSCSITarget_Invoke_Connect(x,x,x,x,x,x,x)+319j
.text:1000E4D3 mov ecx, offset aOnewaychap ; “ONEWAYCHAP”
.text:1000E4D8
.text:1000E4D8 loc_1000E4D8: ; CODE XREF: MSFT_iSCSITarget_Invoke_Connect(x,x,x,x,x,x,x)+348j
.text:1000E4D8 mov si, [edx]
.text:1000E4DB cmp si, [ecx]
.text:1000E4DE jnz short loc_1000E4FE
.text:1000E4E0 test si, si
.text:1000E4E3 jz short loc_1000E4FA
.text:1000E4E5 mov si, [edx+2]
.text:1000E4E9 cmp si, [ecx+2]
.text:1000E4ED jnz short loc_1000E4FE
.text:1000E4EF add edx, 4
.text:1000E4F2 add ecx, 4
.text:1000E4F5 test si, si
.text:1000E4F8 jnz short loc_1000E4D8
.text:1000E4FA
.text:1000E4FA loc_1000E4FA: ; CODE XREF: MSFT_iSCSITarget_Invoke_Connect(x,x,x,x,x,x,x)+333j
.text:1000E4FA mov ecx, edi
.text:1000E4FC jmp short loc_1000E503
.text:1000E4FE ; —————————————————————————
.text:1000E4FE
.text:1000E4FE loc_1000E4FE: ; CODE XREF: MSFT_iSCSITarget_Invoke_Connect(x,x,x,x,x,x,x)+32Ej
.text:1000E4FE ; MSFT_iSCSITarget_Invoke_Connect(x,x,x,x,x,x,x)+33Dj
.text:1000E4FE sbb ecx, ecx
.text:1000E500 or ecx, 1
.text:1000E503
.text:1000E503 loc_1000E503: ; CODE XREF: MSFT_iSCSITarget_Invoke_Connect(x,x,x,x,x,x,x)+34Cj
.text:1000E503 test ecx, ecx
.text:1000E505 jnz short loc_1000E50F
.text:1000E507 mov [esp+0B38h+Memory], 1
.text:1000E50F
.text:1000E50F loc_1000E50F: ; CODE XREF: MSFT_iSCSITarget_Invoke_Connect(x,x,x,x,x,x,x)+28Bj
.text:1000E50F ; MSFT_iSCSITarget_Invoke_Connect(x,x,x,x,x,x,x)+355j
.text:1000E50F cmp byte ptr [ebx+5Ch], 0
.text:1000E513 jz short loc_1000E54A
.text:1000E515 or eax, 20h
.text:1000E518 mov [esp+50h], edi
.text:1000E51C push ecx
.text:1000E51D mov ecx, [ebx+58h]
.text:1000E520 lea edx, [esp+54h]
.text:1000E524 mov [esp+0B3Ch+var_B10], eax
.text:1000E528 call _DiscpUnicodeToAnsi@12 ; DiscpUnicodeToAnsi(x,x,x)
.text:1000E52D mov ecx, [ebx+58h]
.text:1000E530 lea edx, [ecx+2]
.text:1000E533
.text:1000E533 loc_1000E533: ; CODE XREF: MSFT_iSCSITarget_Invoke_Connect(x,x,x,x,x,x,x)+38Cj
.text:1000E533 mov ax, [ecx]
.text:1000E536 add ecx, 2
.text:1000E539 cmp ax, di
.text:1000E53C jnz short loc_1000E533
.text:1000E53E mov eax, [esp+0B38h+var_B10]
.text:1000E542 sub ecx, edx
.text:1000E544 sar ecx, 1
.text:1000E546 mov dword ptr [esp+0B38h+var_AF0], ecx
.text:1000E54A
.text:1000E54A loc_1000E54A: ; CODE XREF: MSFT_iSCSITarget_Invoke_Connect(x,x,x,x,x,x,x)+363j
.text:1000E54A cmp byte ptr [ebx+64h], 0
.text:1000E54E jz short loc_1000E581
.text:1000E550 or eax, 40h
.text:1000E553 mov [esp+54h], edi
.text:1000E557 push ecx
.text:1000E558 mov ecx, [ebx+60h]
.text:1000E55B lea edx, [esp+58h]
.text:1000E55F mov [esp+0B3Ch+var_B10], eax
.text:1000E563 call _DiscpUnicodeToAnsi@12 ; DiscpUnicodeToAnsi(x,x,x)
.text:1000E568 mov ecx, [ebx+60h]
.text:1000E56B lea edx, [ecx+2]
.text:1000E56E
.text:1000E56E loc_1000E56E: ; CODE XREF: MSFT_iSCSITarget_Invoke_Connect(x,x,x,x,x,x,x)+3C7j
.text:1000E56E mov ax, [ecx]
.text:1000E571 add ecx, 2
.text:1000E574 cmp ax, di
.text:1000E577 jnz short loc_1000E56E
.text:1000E579 sub ecx, edx
.text:1000E57B sar ecx, 1
.text:1000E57D mov [esp+4Ch], ecx
.text:1000E581
.text:1000E581 loc_1000E581: ; CODE XREF: MSFT_iSCSITarget_Invoke_Connect(x,x,x,x,x,x,x)+39Ej
.text:1000E581 cmp byte ptr [ebx+4Bh], 0
.text:1000E585 mov al, 1
.text:1000E587 jz short loc_1000E58C
.text:1000E589 mov al, [ebx+4Ah]

Changing the PowerShell cmd to specify AuthenticationType in all upper case resolves the connection problem.

This can also be observed if running PowerShell cmd

Get-IscsiSession

And noting that AuthenticationType is always listed in UPPERCASE.

Posted in Uncategorized | Leave a comment

Extracting Office Upload Center Info with PowerShell

Office 2016 Upload Center stores data about currently syncing files and errors in an Access Database file (.accdb)

With Click2Run Office installed by default we can’t programatically query Microsoft Access database, so we need to install the Access Database engines. However with Click2Run Office installed these will fail to install with an error similar to this:

 You cannot install the 64-bit version of Microsoft Access Database Engine 2016 because you currently have 32-bit Office products installed. If you want to install the 64-bit Microsoft Access Datbase Engine 2016, you will first need to remove the 32-bit installation of Office products. After uninstalling the following product(s), rerun setup in order to install 64-bit version of Microsoft Access Database Engine 2016:
Office 16 Click-to-Run Extensibility Component

You will get a similar error if you try the 32-bit installer.

Download Microsoft Access Database Engine 2016 Redistributable from https://www.microsoft.com/en-us/download/details.aspx?id=54920

To successfully install with Click2Run Office isntalled use the /quiet switch. Run the following commands from an elevated command prompt.

For use with 64-bit PowerShell:

AccessDatabaseEngine_X64.exe /quiet

For use with 32-bit PowerShell:

AccessDatabaseEngine.exe /quiet

With the engine installed we can onw query the upload center datbase. The purpose of each database table and column is left as an excercise for the reader.

$path = Join-Path -Path $env:LOCALAPPDATA `
    -ChildPath "Microsoft\Office\16.0\OfficeFileCache\CentralTable.accdb"

$tables = @( 
    "CacheProperties", 
    "EventClients",
    "EventMetaInfo"
    "IncomingEvents",
    "MasterFile",
    "OutgoingEvents",
    "ServerTarget",
    "Subcache" )

$results = @{}
$conn = New-Object System.Data.Odbc.OdbcConnection
$conn.ConnectionString = `
    "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=$path"

$conn.Open()
$cmd = New-Object System.Data.Odbc.OdbcCommand
$cmd.Connection = $conn
ForEach ($table in $tables)
{
    Write-Host "Reading $table"
    $cmd.CommandText = "SELECT * FROM $table"
    $reader = $cmd.ExecuteReader()
    $results[$table] = New-Object System.Data.DataTable
    $results[$table].Load($reader)
}

ForEach ($table in $tables)
{
    Write-Host "Saving to $($table).csv"
    $results[$table] | Export-Csv -NoTypeInformation -Path "$($table).csv"
}
Posted in Uncategorized | Leave a comment

PowerShell Script to Backup / Restore Clipboard Contents

When Windows programs save data to the clipboard, the data can be saved in multiple formats. Analysing this process is easy with API Monitor and monitoring the Clipboard APIs such as OpenClipboard, CloseClipboard, SetClipboardData, GetClipboardData. An example of using this to diagnose a clipboard problem is here

In investigating an application that on some machines when pasting selected an undesired format, despite the correct format also being on the clipboard, I wanted to analyse the clipboard contents more closely and repeat testing on my own test machine. As I didn’t have access to the source application that put the content onto the clipboard I wrote this PowerShell script which will save the clipboard contents for all formats into an XML file which can be restored at a later time to the clipboard.

Usage is:

Backup Clipboard

Save-Clipboard -Path C:\support\clipboard_data.xml

Restore Clipboard

Load-Clipboard -Path C:\support\clipboard_data.xml

This can also be used to analyse the raw contents of the clipboard if required.

Add-Type @'
    using System;
    using System.Text;
    using System.Runtime.InteropServices;
    using System.Collections.Generic;

public static class NativeMethods
{       

    [DllImport("user32.dll", SetLastError=true)]
    public static extern int SetClipboardData(uint uFormat, IntPtr hMem);

    [DllImport("user32.dll", SetLastError=true)]
    public static extern uint EnumClipboardFormats(uint format);

    [DllImport("user32.dll")]
    public static extern bool IsClipboardFormatAvailable(uint format);

    [DllImport("user32.dll")]
    public static extern IntPtr GetClipboardData(uint uFormat);

    [DllImport("user32.dll", SetLastError=true)]
    public static extern bool OpenClipboard(IntPtr hWndNewOwner);

    [DllImport("kernel32.dll")]
    public static extern IntPtr GlobalSize(IntPtr hMemory);

    [DllImport("kernel32.dll")]
    public static extern IntPtr GlobalLock(IntPtr hMem);

    [DllImport("user32.dll", SetLastError=true)]
    public static extern bool CloseClipboard();

    public static List GetClipboardFormats()
    {
        List formats = new List();
        uint currentFormat = 0;

        IntPtr Handle = IntPtr.Zero;
        OpenClipboard(Handle);

        do
        {
            currentFormat = EnumClipboardFormats(currentFormat);
            if (currentFormat!=0)
            {
                formats.Add(currentFormat);
            }
        } while (currentFormat != 0);
        return formats;
    }

    public static byte[] GetClipboardDataBytes(uint SelectedFormat)
    {
         if (SelectedFormat != 0)
         {
         IntPtr Handle = IntPtr.Zero;
         OpenClipboard(Handle);

         //Get pointer to clipboard data in the selected format
         IntPtr ClipboardDataPointer = GetClipboardData(SelectedFormat);

         //Do a bunch of crap necessary to copy the data from the memory
         //the above pointer points at to a place we can access it.
         IntPtr Length = GlobalSize(ClipboardDataPointer);
         IntPtr gLock = GlobalLock(ClipboardDataPointer);

         //Init a buffer which will contain the clipboard data
         byte[] Buffer = new byte[(int)Length];

         //Copy clipboard data to buffer
         Marshal.Copy(gLock, Buffer, 0, (int)Length);
         CloseClipboard();

         return Buffer;
         }

         return new byte[] {};
    }
}
'@

Function Load-Clipboard
{
    param([string]$Path)
    [xml]$Doc = Get-Content $Path
    $handle = [System.IntPtr]::Zero
    [NativeMethods]::OpenClipboard($handle)
    ForEach ($clipboard in $doc.Clipboards.Clipboard)
    {
        [UInt32]$format = $clipboard.Format
        $data = [Convert]::FromBase64String($clipboard.Data)
        Write-Host "Loading $($data.Length) bytes into clipboard format $format"
        $ptr = [System.Runtime.InteropServices.Marshal]::AllocHGlobal($data.Length)
        [void][System.Runtime.InteropServices.Marshal]::Copy($data, 0, $ptr, $data.Length);
        [NativeMethods]::SetClipboardData($format,$ptr)
        [System.Runtime.InteropServices.Marshal]::FreeHGlobal($ptr)

    }
    [NativeMethods]::CloseClipboard()

}

Function Save-Clipboard
{
    param([string]$Path)
    [xml]$Doc = New-Object System.Xml.XmlDocument
    $dec = $Doc.CreateXmlDeclaration("1.0","UTF-8",$null)
    [void]$doc.AppendChild($dec)
    [void]$doc.CreateComment("Clipboard Data from $($env:COMPUTERNAME) for $($env:USERNAME) on $(Get-Date)")
    $root = $doc.CreateNode("element","Clipboards",$null)
    $formats = [NativeMethods]::GetClipboardFormats()

    ForEach($format in $formats)
    {
        Write-Host "Capturing clipboard format $format"
        $data = [NativeMethods]::GetClipboardDataBytes($format)
        $c = $doc.CreateNode("element","Clipboard",$null)
        [void]$c.SetAttribute("Format",$format)
        [void]$c.SetAttribute("Data",[Convert]::ToBase64String($data))
        [void]$root.AppendChild($c)
    }

    $doc.AppendChild($root)
    $doc.Save($Path)
}
Posted in Uncategorized | Leave a comment