Enable Active Directory PowerShell commands on Windows 7

Download Remote server administration tools from this location :
http://www.microsoft.com/en-us/download/confirmation.aspx?id=7887

Now Enable Active directory module for windows PowerShell by following below these steps:

1. Navigate to Control Panel
2. Go to Programs and Features
3. Select “Turn on Windows feature on or off”
4. Expand node “Remote Server Administration tools”
5. Expand node “AD DS and AD LDS tools”
4. Select Active Directory Module for Windows PowerShell

5. After the feature is enabled, open PowerShell and run command

Import-Module ActiveDirectory


Method to write into excel files using powershell and OLEDB

Below is a function to modify the excel files using OLEDB.
You can modify the variable $strQuery to get different results based on your requirement.


Function UpdateExcelForResults($strSheetName,$colName,$colValue,$strFileName)
{
$strProvider = "Provider=Microsoft.ACE.OLEDB.12.0"
$strDataSource = "Data Source = $strFileName"
$strExtend = "Extended Properties=Excel 8.0"
$strQuery = "Update [$strSheetName] set $colName = '$colValue' "
$objConn = New-Object System.Data.OleDb.OleDbConnection("$strProvider;$strDataSource;$strExtend")
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)
$sqlCommand.Connection = $objConn
$objConn.open()
$sqlCommand.ExecuteNonQuery()
$objConn.Close()
$objConn.Dispose()
}


Powershell to read excel file

Below is a method to read data from the Excel file using the Powershell scripting capability.


Function ReadExcelForTestScenarios($strQuery,$strFileName)
{
$strProvider = "Provider=Microsoft.ACE.OLEDB.12.0"
$strDataSource = "Data Source = $strFileName"
$strExtend = "Extended Properties=Excel 8.0"
$objConn = New-Object System.Data.OleDb.OleDbConnection("$strProvider;$strDataSource;$strExtend")
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)
$sqlCommand.Connection = $objConn
$objConn.open()
$sqlReader = $sqlCommand.ExecuteReader()
$Datatable = New-Object System.Data.DataTable
$DataTable.Load($SqlReader)
$sqlReader.Close()
$objConn.Close()
return $DataTable
}

Method Call


$strFileName = "c:\DataFile\File.xlsx"
$strQuery = "Select * From [Sheet1$] where Column1='SomeValue' and Column2='SomeValue1'"
$CommonData = ReadExcelForTestScenarios $strQuery $strFileName

Get values from $CommonData if it has values for more than one rows


Foreach($Item in $CommonData)
{
$ValueForCol = $Item["Column Name"]
#......Can extract values similarly for other columns
}

Supress credentials pop up window for Powershell scripts

This is how you can pass the credentials and suppress the credentials pop up window for Powershell scripts


$username = "Domain\UserName"
$password = convertto-securestring "Password" -asplaintext -force
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $password


Cannot find an overload for “getElementById” and the argument count: “1”

The problem statement :

Suppose we are trying to run the below code :


$ie = new-object -com "InternetExplorer.Application"
$ie.navigate($url)
$ie.visible = $true
$Doc = ie.Document
$Object1 = $Doc.getElementByID("UserName")

Now if you try to see what $Doc contains it returns System.__ComObject which is not correct.
Or you even might get this error :
Cannot find an overload for “getElementById” and the argument count: “1”

Do the following to solve this problem:

1. Check for the Microsoft.mshtml.dll on your machine. It should be available at location C:\Program Files (x86)\Microsoft.NET\Primary Interop Assemblies on a x64bit machine. If you don’t find it at this location, It might be the reason for this issue.

2. If you find the dll on the same machine then there might be a problem in getting it loaded, So try to copy this dll and put it at the same location as your script and try to load it at runtime.


Function LoadAssembly ($Path)
{
$fs = ([System.IO.FileInfo] (Get-Item -Path $Path)).OpenRead()
$buffer = New-Object Byte[] $fs.Length
$n = $fs.Read($buffer, 0, $fs.Length)
$fs.Close()

if ( $n -gt 0 )
{
[System.Reflection.Assembly]::Load($buffer)
}
else
{
Write-Warning "Cannot load an assembly from an empty file."
}
}


3. If you don’t find the assembly on the same machine, Then try to find it on other machines you might have or online. Once you find it you can place the dll at any location on you machine and load it at run time.

Reference Links:
Below is the link to a method to load assembly at run time.

Comments in Powershell

How to comment the code in Powershell

This is done using ‘#’ in Powershell.


#Comment 1
#Comment 2
#Comment 3


If you want to comment multiple lines, do it is using ‘#’ with ”

This is how commenting works for Powershell.

Get a Loaded DLL for a process on Remote machine

The method below with get you the loaded dll for a process running on a remote machine.
The language used in C# and powershell cmdlet is used to get the information

you will need these two namespaces to be used for the same.
using System.Management.Automation;
using System.Management.Automation.Runspaces;

public bool getDLLLoadedOnRemoteMachine(string processName, string dLLToCheck, string MachineName, string strDomainAdminUser, string domainUserPassword)
{

var runspace = RunspaceFactory.CreateRunspace();
runspace.Open();
var pipe = runspace.CreatePipeline();
pipe.Commands.AddScript("$PSComputer = '" + MachineName + "'");
pipe.Commands.AddScript("$username = \"" + strDomainAdminUser + "\"");
pipe.Commands.AddScript("$password = convertto-securestring \"" + domainUserPassword + "\" -asplaintext -force");
pipe.Commands.AddScript("$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $password");
pipe.Commands.AddScript("$Session = New-PSSession -ComputerName $PSComputer -Credential $cred -ConfigurationName \"microsoft.powershell32\"");
pipe.Commands.AddScript("$AllModules = Invoke-Command -Session $Session -ScriptBlock{(Get-Process " + processName + ").Modules }");
pipe.Commands.AddScript("Remove-PSSession $Session");
pipe.Commands.AddScript("$AllModules");

try
{
foreach (PSObject result in pipe.Invoke())
{
var str = result.ToString();
if (str.ToUpper().IndexOf(dLLToCheck.ToUpper()) != -1)
{
return true;
}
}
return false;
}
catch (Exception e)
{
if (e.InnerException != null)
{
Console.WriteLine("Invoke Exception: " + e.InnerException.ToString());
return false;
}
else
{
Console.WriteLine("Invoke Exception: " + e.Message.ToString());
return false;
}
}
}


You will need to add a reference to your project.

For Win7/2008r2 you need to install Windows Management Framework 3.0 Please find the link to the download provided below.
http://www.microsoft.com/en-us/download/details.aspx?id=34595

User logon modes for Terminal Servers

How can you get the User Logon modes for Terminal Server.

Navigate to Server Manager -> Roles -> Remote Desktop Services -> RD Session Host Configuration and look at the value for User Logon mode as shown in the image below.

How to obtain the user logon mode settings using powershell:


param($ComputerName, $username ,$password)
$namespace = "root\CIMV2\TerminalServices"
$secstr = ConvertTo-SecureString $password -asplaintext -force
$Creds = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $secstr
$CurrentMachine = gc env:computername
if($ComputerName -eq $CurrentMachine)
{
$Obj = Get-WmiObject -class Win32_TerminalServiceSetting -computername $computer -Authentication PacketPrivacy -Impersonation Impersonate -namespace namespace
}
else
{
$Obj = Get-WmiObject -class Win32_TerminalServiceSetting -computername $computer -Authentication PacketPrivacy -Impersonation Impersonate -namespace $namespace -Credential $Creds
}
$Obj
$SessionBrokerDrainMode = $Obj.SessionBrokerDrainMode
$Logons = $Obj.Logons
$SessionBrokerDrainMode
$Logons


Now if you get the variables values as either 1 or 0 for logons
0 : New sessions are allowed.
1 : New sessions are not allowed.

For SessionBrokerDrainMode could have three values:
0 : Allow all connections.
1 : Allow reconnections, but prevent new logons until the server is restarted.
2 : Allow reconnections, but prevent new logons.

Getting/Setting the User logon mode settings using registry keys:


public void GetUserLogonMode(string strMachineName, string logFile, string userLogonMode)
{
RegistryKey rkey = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine, strMachineName, RegistryView.Registry64);
RegistryKey rkeySessionBrokerDrainMode = rkey.OpenSubKey(@"SYSTEM\CurrentControlSet\Control\Terminal Server");
string SessionBrokerDrainMode = rkeySessionBrokerDrainMode.GetValue("TSServerDrainMode").ToString();
RegistryKey rkeyWinLogOn = rkey.OpenSubKey(@"SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon",true);
string strLogons = rkeyWinLogOn.GetValue("WinStationsDisabled").ToString();
}

The variables SessionBrokerDrainMode and strLogons will hold the values for the SessionBrokerDrainMode and logons as in the above case.

I have noticed some issues with powershell while extracting the values for Logons, so I think working through the registry key is a better way to do it.

For editing the values for User Logon mode you can change the value in the registry keys.


rkeySessionBrokerDrainMode.SetValue("TSServerDrainMode", 0);
rkeyWinLogOn.SetValue("WinStationsDisabled", 0);


Getting/Setting the User logon mode settings using command prompt: