Tuesday, December 2, 2014

[MSMQ] Using powershell to PEEK at several thousand records

A bit of a detour today - delving into stuff not easily done with T-SQL.

I needed to look in some MSMQ queues, without removing the messages, pulling out some details.  After multiple attempts (and a couple questions on StackOverflow) I got exactly what I was looking for. 

  • PEEK 500 messages off a queue (PEEK differs from SELECT because it leaves them in the queue - for you T-SQL types, the difference between SELECT and RECEIVE in Service Broker)
  • Pull out the (binary) bodystream 
  • Convert bodystream to ascii
  • Strip off the non-XML header
  • Get the ID from the message (both inside and before the XML is included below)
  • Return it all to a grid.  
Not elegant, but works pretty well (and pretty fast for my limited set - 10 seconds for 500)

 Here's what my header looks like, FWIW, since it drives several parts of the code (the split around the colon, for instance)
#MSMQ.bodystream is in decimal format, so this is our converter.
$enc = [System.Text.Encoding]::ASCII

#get-msmqqueue finds that one queue (it accepts wildcards).  
$msmq = Get-MsmqQueue -Name "my_msmq" `

#now that we have the queue as an object, PEEK at the first 500 messages.  
#Give up after 5 seconds (since it will hang or crash if there are none)
#We assign the end results to a variable so that we can return in one set.  

#If writing to a file, putting the write inside the loop might speed things up, would def save memory usage

$var = Receive-MsmqQueue -input $msmq -Peek -Count 500 -Timeout 5000 -RetrieveBody | foreach {

#BodyStream is an object of decimals that we need to convert to an array, then a string.

#Was originally, but in this case I can combine
# $sr = $_.BodyStream.ToArray()
# $pr = $enc.GetString($sr)

$pr = $enc.GetString($_.BodyStream.ToArray())

#Clean is an XML version of the object, minus the crappy pre-xml header  

#MANY thanks to Matt & Swonkie for the speed boost.
[xml]$clean = $pr.Substring($pr.IndexOf('<'))

#parsing the swtxnum - the select-xml works and takes about the same time, 

#but showing other options via the pre-xml header.
#this would work too: [string]$swtxnum = select-xml -xml $clean -XPath "/msmg/Data/myid"
$myid = $pr.Split(":",5)[2]

select-xml -xml $clean -XPath "/msmg/Data" | foreach {$_.node } | select @{Name="myid"; Expression={$myid}} , fielda, fieldb, fieldc, fieldd, fielde  #| ` # select is optional



No comments: