Wednesday, 20 November 2013

Powershell. Connect, select data in Oracle DB Server / MS SQL Server 2008 R2.

Tasks.
1. Need to connect to Oracle db with HOST = 10.14.2.7:1521.
2. Select data by query 
Select column1, l.* from schema.Table l where column1 > trunc(column2) and EventType = 'Error' order by l.column1 desc.

Before using this script check if you have right config of the connection string and established connection to oracle host, I wrote about in Configure and connect to remote Oracle DB Server using SQLPlus.

Here is two examples howto read data from query. In first we using DataSet and second is using ExecuteReader.

Connect to Oracle DB:

[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient");
$connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=  10.14.2.7)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MAINDB)));UserID=YOURLOGIN;Password=YOURPASSWORD"; 
$connection = New-Object System.Data.OracleClient.OracleConnection($connectionString);
$connection.Open();
$queryString = "Select column1, l.* from schema.Table l where column1 > trunc(column2) and EventType = 'Error' order by l.column1 desc"
$command = New-Object System.Data.OracleClient.OracleCommand($queryString, $connection);
$adapter = New-Object system.data.oracleclient.oracledataadapter
$adapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet;
$adapter.Fill($DataSet);
$connection.Close();
$metrics = $DataSet.Tables[0];

========================================================================

[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient");
$connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)( HOST=  10.14.2.7)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=OCSMAIN)));UserID=YOURLOGIN;Password=YOURPASSWORD";
$connection = New-Object System.Data.OracleClient.OracleConnection($connectionString);
$connection.Open();
$queryString = "Select column1, l.* from schema.Table l where column1 > trunc(column2) and EventType = 'Error' order by l.column1 desc"
$command = New-Object System.Data.OracleClient.OracleCommand($queryString, $connection);
$Reader = $Command.ExecuteReader()
$Counter = $Reader.FieldCount

while ($Reader.Read()) {
for ($i = 0; $i -lt $Counter; $i++) {
@{ $Reader.GetName($i) = $Reader.GetValue($i); }
}
}
$Connection.Close()

==================================================================

Connect to MS SQL Server 2008.

$SqlServer = "10.84.1.20";
$SqlCatalog = "YourDB";
$Connection = New-Object System.Data.SqlClient.SqlConnection;
$Connection.ConnectionString = "Server = $SqlServer; Database = $SqlCatalog; Integrated Security = True";
$Connection.Open();
$Command = New-Object System.Data.SQLClient.SQLCommand;
$Command.Connection = $Connection;
$Command.CommandText = "select * from stat.YourView";
$Reader = $Command.ExecuteReader();
$Counter = $Reader.FieldCount;

while ($Reader.Read()) {
for ($i = 0; $i -lt $Counter; $i++) {
@{ $Reader.GetName($i) = $Reader.GetValue($i); }
}
}

$Connection.Close()


Powershell. Get 'Email Quota', 'Password Expire' from Exchange in Active Directory.



Task: collect data about group of people from domain:
  • qoute size;
  • max qoute for e-mail (Exchange);
  • count of the days when password will be expired;
Information about users need to keep in XML file.
Here is example of XML file:

<?xml version="1.0"?>
<tree>
<sender id="yourEmail1@domain.net">
  <filename id="firstfile.out">
    <account>Igor.Pa</account>
    <account>Ka.G</account>
    <account>Alex.Kir</account>
    <account>Les.Samyl</account>
    <account>Serg.Klov</account>
    <account>Alex.Doro</account>
  </filename>
  <filename id="secondfile.out">
   <account>Serg.Kolo</account>
   <account>Serg.Berez</account>
   <account>Irena.Kolots</account>
   <account>Elena.Venko</account>
  </filename>
</sender>
</tree>

Those tags used another program that collect data from powershell script (below), create Excel file (tag <filename>) and send to customer email (tag <sender>). So for now we'll use only tag <account> in cycle

Developed and tested in Windows Server 2008 R2.

  1. Create function that will get list of the users ($accounts) and file name where it will be save ($filename).
  2. We should to create parser for our XML file.
Create function

function getAccountData {

param ([string]$account, [string]$filename)

#for using this package, you need to download Quest.ActiveRoles.ADManagement

Add-PSSnapin Quest.ActiveRoles.ADManagement;
$s = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri http://KV-MBX-02.YOURDOMAIN.COM/PowerShell/ -Authentication Kerberos;
Import-PSSession $s;

[string]$strProhibit = 'Could not get ProhibitQuota'
[string]$strTotal = 'Could not get TotalSize'

$user = Get-QADUser -Identity $account
$mailbox = $user.Mail
$prohibitQuota = Get-Mailbox -Identity $mailbox | select ProhibitSendQuota
$strProhibit = $prohibitQuota.ProhibitSendQuota.Substring(0, $prohibitQuota.ProhibitSendQuota.IndexOf("("));
$totalSize = Get-MailboxStatistics -Identity $mailbox | select TotalItemSize
$strTotal = $totalSize.TotalItemSize.Substring(0, $totalSize.TotalItemSize.IndexOf("("));
$passexp = $user.PasswordExpires - (Get-Date)
$name = $user.DisplayName
$post = $user.Title
$day = $passexp.Days
[string]$returnStr = $name + ';' + $post + ';' + $mailbox + ';' + $strProhibit + ';' + $strTotal + ';' + $day;
Remove-PSSnapin -Name 'Quest.ActiveRoles.ADManagement'; get-pssession | remove-pssession | Where-Object {$_.ConfigurationName -like 'Microsoft.Exchange' }
# $returnStr | Out-File $filename -append;
$returnStr >> $filename;

}

XML parser

[xml] $xml = Get-Content D:\data.xml
$filenames = $xml.tree.sender.filename
foreach($filename in $filenames) { 
$file = $filename.id;
$nodes = $filename.ChildNodes;
foreach($node in $nodes) {
getAccountData -account $node -filename $file
}
}

Friday, 26 October 2012

Powershell auto connection with Plink.exe



I got task to check counter of network connections by SCOM agent on HP-Unix servers. Nothing difficult but I got troubles with auto connection, all servers want me to accept store key in cache and I must input "y or n" :(
So ... '-batch' in help


# .\plink.exe -v -batch -pw 'Password123' 'userlogin@10.44.6.10' 'netstat | grep scom | wc -l'

$arr = @('10.44.6.6', '10.44.6.5','10.44.6.4','10.44.6.3','10.44.6.2', '10.44.6.12'');


$keys = ' -batch -pw ';
$cmd = 'netstat | grep scom | wc -l';

function run-SSH([string] $linuxHostname)
{
 $plink = "C:\plink.exe "
 $myConStr = "$plink $keys `'$pass`' `'$linuxHostname`' `'$cmd`'";
    return Invoke-Expression $myConStr
}

foreach($server in $hpux) {
$m =  run-SSH "scom@$server"
write-host "$server -> $m";
}

Thursday, 11 October 2012

Configure and connect to remote Oracle DB Server using SQLPlus

We have Oracle DB Server with IP 192.168.2.207 and port 1521.
You need to know: database, schema, table, user account <testuser> password <test> . After that you can concat your connection string:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=  192.168.2.207)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=TestDB)))

This connection string you'll need to add  tnsnames.ORA in file that often lay in: 

C:\Oracle\product\11.2.0\client_1\network\admin\tnsnames.ORA.

You should to give unique alias for you connection. Something like:
...
TestConneсt =
(DESCRIPTION=
  (ADDRESS =
    (PROTOCOL = TCP)(HOST =  192.168.2.207)(PORT = 1521)
  )
  (CONNECT_DATA =
    (SERVICE_NAME=TestDB)
  )
)
...

Save it. Now we can test our new connection. Open Run -> cmd in command line input command:

tnsping TestConnect

More information about keys of this command you can find at http://docs.oracle.com/cd/E19957-01/805-3808/6j3kacb36/index.html

You'll need to change password, if you make your first connection to DB. I use SQLPlus (must be installed  with Oracle client). In the command line enter:


sqlplus testuser/password@192.168.2.207/TestConnect
Password: <oldpass>
NewPassword: <newpass>

Thursday, 4 October 2012

OpsMgr: To disable rules in package (SCOM).

Overrides for the rules in one package.

We have package 'Microsoft.Exchange.2010' with a lot of different rules. Our task is to disable all rules in this package by overrides ( his name will be 'zOverrides_Exchange').
The first interesting script that I found in INTERNET was this one http://www.systemcentercentral.com/Default.aspx?TabId=143&IndexID=91008 , but it's grateful only for 1 rule. We must understand that rule's name can be the same in different package and extends by different classes.

# Part 3. This function look for the same rules in different classes
function searchDoubleRules() {
  $source = Get-ManagementPack | where-object { $_.Name -match 'Microsoft.Exchange.2010'}
  $rules = Get-Rule -ManagementPack $source
  $rulenames = @($rules);
  for($i=0; $i -le $rules.Length-1; $i++) {
    $rulenames.SetValue($rules[$i].DisplayName, $i)
  }

  $doublerules = @{}
  for($i=0; $i -le $rulenames.Length-1; $i++) {
    $x = 1;
    $rule = $rulenames[$i];
  for($j = $i+1; $j -le $rulenames.Length-1; $j++) {
    if($rule -eq $rulenames[$j]) {
       $x++;
    }
  }

  if(!$doublerules.ContainsKey($rule)) {
    $doublerules.Add($rule, $x);
  }
 }
 $doublerules.getenumerator() | where-object { $_.Value -gt 1 } | Out-File exchange_overlapping_rules.txt
 $double = $doublerules.getenumerator() | where-object { $_.Value -eq 1 } | select -expand key
 return $double
}


# Part 4 Make overrides on array of the rules
function setOverrideRules([string]$r, [string]$c, [int]$i ) {
  $name = "Dead_$i";
  'Rule ['+$r+'];'
  'Class ['+$c+'];'
  'Override ['+$name+'];'
  $SourceMP = Get-ManagementPack | where-object { $_.Name -match 'Microsoft.Exchange.2010'}
  $mp = Get-ManagementPack | Where-Object {$_.FriendlyName -match 'zOverrides_Exchange' }
  $rule = Get-Rule -ManagementPack $sourceMP | Where-Object { $_.DisplayName -eq $r }
  $Target = Get-MonitoringClass | Where-Object { $_.Name -eq $c }
  $override = New-Object Microsoft.EnterpriseManagement.Configuration.ManagementPackRulePropertyOverride($mp, $name);
  $Rule = [Microsoft.EnterpriseManagement.Configuration.ManagementPackElementReference``1[Microsoft.EnterpriseManagement.Configuration.ManagementPackRule]]::op_Implicit($Rule);
  $override.Rule = $Rule;
  $override.Description = 'Test';
  $override.Property = 'Enabled';
  $override.Value = 'false';
  $override.Context = $Target;
  $override.DisplayName = $r;
  $mp.Verify();
  $mp.AcceptChanges();
}


# Part 1
# Create connection to SCOM console and make 'st' to the namespace "OperationsManagerMonitoring::"
Add-PSSnapin "Microsoft.EnterpriseManagement.OperationsManager.Client" -ErrorVariable errSnapin ;
Set-Location "OperationsManagerMonitoring::" -ErrorVariable errSnapin ;
new-managementGroupConnection -ConnectionString:<Your RMS> -ErrorVariable errSnapin ;
Set-Location <Your RMS> -ErrorVariable errSnapin;


# Part 2. Looking for  'Microsoft.Exchange.2010'  package
$SourceMP = Get-ManagementPack | where-object { $_.Name -match 'Microsoft.Exchange.2010'}
$rules = Get-Rule -ManagementPack $sourceMP # get all rules in this package
$arr = searchDoubleRules; # Goto part 2
$arr.Count

# Part 3. So it interesting part. We save all TargetId of MSExchange rules in $ruleId  and that we use it for search in scope of SCOM Monitor Classes by same Id.
for($i=0; $i -le $rules.Length-1; $i++) {
   [string]$ruleId = $rules[$i].Target.Id
   $t = Get-MonitoringClass | Where-Object {$_.Id -eq $ruleId }
   [string]$rDN = $rules[$i].DisplayName;
   [string]$tN = $t.Name;


   foreach($a in $arr) {
      if($a -eq $rDN) {
      setOverrideRules -r $rDN -c $tN -i $i; 
# Part 4. 
      'Overrided!'
      break;
   }
 }
}


# Part 5. Remove connection to SCOM console.
remove-pssnapin "Microsoft.EnterpriseManagement.OperationsManager.Client" -ErrorVariable errSnapin;

Ping Server List Availability (ICMP)

Task:
Please make sure that all servers are ping from your PC. Here is 'serverlist.txt'.

So:
# get file content
$file = Get-Content 'C:\serverlist.txt'
# convert file content into string type
[string]$lines = $file
# split our string
$arrlines = $lines.Split(' ')

foreach ($Server in $arrlines) {
  if (test-Connection -ComputerName $Server -Count 2 -Quiet ) {         
    write-Host "$Server is alive and Pinging " -ForegroundColor Green
  } else {
Write-Warning "$Server seems dead not pinging"
  }  
}

Something like this:



end