Day: April 23, 2013

SQL query to get #BlackBerry User information

by ucinfo, posted in BlackBerry, Powershell

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

 

Using #Powershell to run a SQL Command

by ucinfo, posted in Powershell

So I have a hell of a lot of stuff where I am pushing or pulling data from SQL and wanted to share the function I use:

Function RunSqlQuery([string]$SqlServer, [string]$SqlDb, [string]$SqlCmd){
$Error.Clear()
Write-Host “SQL Server…: ” $SqlServer
Write-Host “Sql Database.: ” $SqlDb

$Connection = New-Object System.Data.SQLClient.SQLConnection

$Connection.ConnectionString =”server=$SqlServer;database=$SqlDb;trusted_connection=true;”

$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.CommandType = [System.Data.CommandType]”Text”
$Command.Connection = $Connection
If($Error.Count -gt 0){Exit}
$Command.CommandText = $SQLCmd

$tmpDT = New-Object “System.Data.DataTable”
$Connection.Open()
$Reader = $Command.ExecuteReader()
$tmpDT.Load($Reader)
$Connection.Close()

$i=0;$tmpDT | ForEach{$i++}
Write-Host “Query Result size: ” $i
Write-Host “`n”

Return $tmpDT
}

The function assumes the account using the function has rights on the SQL database.

Typically I set the following:

$SqlServer = “SQLServer”
$SqlDB = “SQLDatabase”
$SqlQuery = “Select * from something”

RunSqlQuery $SqlServer $sqldb $SQLQuery

-or-

$SqlData = RunSqlQuery $SqlServer $sqldb $SQLQuery

 

Finding a computer in the AD using #Powershell

by ucinfo, posted in Powershell

So I need to search the AD today for computer objects to see if a description has been set.

$Domain = ([System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain()).Name
$Dom  = “LDAP://DC=” + $Domain.Replace(“.”,”,DC=”)
$strComputer = “<computer2search4>”
$strFilter  = “(cn=$strComputer)”
$Root       = New-Object DirectoryServices.DirectoryEntry $Dom
$selector   = New-Object DirectoryServices.DirectorySearcher
$selector.PageSize    = 1000
$selector.Filter      = $strFilter
$selector.SearchRoot  = $root
$selector.SearchScope = “Subtree”
$Objs = $selector.findall()
$Objs.count

 

The results are in the variable $Objs