Connect to MongoDB on Remote Linux Server with Powershell ... |
|
Depending on your view point, I have the lovely task, or not so lovely task or pulling data from 700+ MongoDBs on Linux into a SQL Server DB to build some reporting around a specific data point. Using POSH-SSH module I was able to accomplish this.
cls
cd c:\ps
$ErrorActionPreference = "stop"
$WarningPreference = "silentlycontinue"
import-module posh-ssh -disablenamechecking
import-module sqlps -DisableNameChecking
## using a repository to populate variable with list of servers with mongoDB instance
$Mongo = invoke-sqlcmd -ServerInstance <repo server> -Database DBstats -Query "SELECT Servername, pwd
FROM dbo.Mongo_Servers "
$collection_time = get-date
foreach($db in $Mongo)
{
try{
##write-host trying to connect to $DB.servername -foreground yellow
## convert passwords to secure string
$vPass = $db.pwd
$pass = $vPass | ConvertTo-SecureString -asplaintext -force
$user = "user name to server"
##create credential file
$mycred = new-object -typename System.Management.Automation.PSCredential -argumentlist $user,$pass
## create ssh session force it so no keygen issues
$session = New-SSHSession -ComputerName $DB.Servername -credential $mycred -AcceptKey -force -ConnectionTimeout 30
## command to execute in SSH session.. this is the path to mongo
$command = "/usr/local/mongodb/bin/mongo <dbname> --eval 'db.<collectionname>.find().pretty().forEach(printjson)'"
## run command on remote linux server
$invokeOutput = Invoke-SSHCommand -SessionId $($session.SessionId) -Command "$($command)"
##filter results with specific criteria
$mongoresult = $invokeOutput.Output -split '\n' | ?{$_ -match "DISASTER_RECOVERY"}
##parse results
$final = ($mongoresult.Replace("`"",'')) -replace ",",''
$final
}
catch [Exception]
{
#General Failure
Write-Host GENERAL FAILURE! for $db.vcentername -ForegroundColor Red
<#When I want to know the full exception type:#>
$errormessage = $Error[0].Exception.Message -replace "'",""
Write-Host $errormessage -BackgroundColor blue -ForegroundColor Yellow
}
cls
cd c:\ps
$ErrorActionPreference = "stop"
$WarningPreference = "silentlycontinue"
import-module posh-ssh -disablenamechecking
import-module sqlps -DisableNameChecking
## using a repository to populate variable with list of servers with mongoDB instance
$Mongo = invoke-sqlcmd -ServerInstance <repo server> -Database DBstats -Query "SELECT Servername, pwd
FROM dbo.Mongo_Servers "
$collection_time = get-date
foreach($db in $Mongo)
{
try{
##write-host trying to connect to $DB.servername -foreground yellow
## convert passwords to secure string
$vPass = $db.pwd
$pass = $vPass | ConvertTo-SecureString -asplaintext -force
$user = "user name to server"
##create credential file
$mycred = new-object -typename System.Management.Automation.PSCredential -argumentlist $user,$pass
## create ssh session force it so no keygen issues
$session = New-SSHSession -ComputerName $DB.Servername -credential $mycred -AcceptKey -force -ConnectionTimeout 30
## command to execute in SSH session.. this is the path to mongo
$command = "/usr/local/mongodb/bin/mongo <dbname> --eval 'db.<collectionname>.find().pretty().forEach(printjson)'"
## run command on remote linux server
$invokeOutput = Invoke-SSHCommand -SessionId $($session.SessionId) -Command "$($command)"
##filter results with specific criteria
$mongoresult = $invokeOutput.Output -split '\n' | ?{$_ -match "DISASTER_RECOVERY"}
##parse results
$final = ($mongoresult.Replace("`"",'')) -replace ",",''
$final
}
catch [Exception]
{
#General Failure
Write-Host GENERAL FAILURE! for $db.vcentername -ForegroundColor Red
<#When I want to know the full exception type:#>
$errormessage = $Error[0].Exception.Message -replace "'",""
Write-Host $errormessage -BackgroundColor blue -ForegroundColor Yellow
}