So following on from my last post (https://blog.flaphead.com/2013/04/23/using-powershell-to-run-a-sql-command/) I use this SQL query to dump BlackBerry User information in to a #Powershell array so I can export it out.
SELECT [ServerConfig]. [ServiceName]
, [UserConfig]. [DisplayName]
, [UserConfig]. [UserName]
, [userconfig]. [MailboxSMTPAddr] as [SMTPAddress]
, [SyncDeviceMgmtSummary]. [ModelName]
, [SyncDeviceMgmtSummary]. [PhoneNumber]
, [SyncDeviceMgmtSummary]. [PlatformVer]
, [SyncDeviceMgmtSummary]. [IMEI]
, [SyncDeviceMgmtSummary]. [HomeNetwork]
, [SyncDeviceMgmtSummary]. [AppsVer]
, [UserConfig]. [PIN]
, [ITPolicy2]. [PolicyName]
, [UserConfig]. [MailboxSMTPAddr]
, [UserConfig]. [MailboxDN]
, [UserConfig]. [ServerDN] as [ExchangeServer]
, [UserConfig]. [AgentId]
, [UserConfig]. [RoutingInfo]
, [UserStats]. [MsgsPending]
, [UserStats]. [LastFwdTime]
, [UserStats]. [LastSentTime]
, CASE [UserStats] . [Status]
WHEN 13 THEN ‘Stopped’
WHEN 12 THEN ‘Running’
WHEN 9 THEN ‘Redirection disabled’
WHEN 0 THEN ‘Initializing’
ELSE ‘Unknown [‘ + CONVERT (varchar , [UserStats]. [Status] ) + ‘]’
END AS UserStatus
FROM [dbo] . [UserConfig]
LEFT OUTER JOIN [dbo] .[UserStats]
ON [UserConfig]. [Id] =[UserStats] . [UserConfigId]
LEFT OUTER JOIN [dbo] .[ITPolicy2]
ON [UserConfig]. [ITPolicy2Id] =[ITPolicy2] . [Id]
LEFT OUTER JOIN [dbo] .[ServerConfig]
ON [UserConfig]. [ServerConfigId] =[ServerConfig] . [Id]
LEFT OUTER JOIN [dbo] .[SyncDeviceMgmtSummary]
ON [UserConfig]. [Id] =[SyncDeviceMgmtSummary] . [UserConfigId]
So setting the above at $SqlQuery and then running
$SqlServer = “BlackBerry SQL Server”
$SqlDb = “BlackBerry Database Name”
RunSqlQuery $SqlServer $SqlDb $SqlQuery
flaphead.com 