Access Database using Powershell

function Invoke-SQL {
param(
[Parameter(Mandatory)]
[string]$dataSource,
[Parameter(Mandatory)]
[string]$database,
[Parameter(Mandatory)]
[string]$sqlCommand
)
Begin {
$connectionString = “Data Source=$($dataSource); Integrated Security=SSPI; Initial Catalog=$($database)”

$retVal = “”

$connection = New-Object system.data.SqlClient.SQLConnection($connectionString)
$command = New-Object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
$command.CommandTimeout = 0
try {
$retVal = $connection.Open()
} catch {
print “Invoke-SQL: connectionString=[$($connectionString)] Error=[$($Error[0])] retVal=[$($retVal)]”

}

$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
try {
$retVal = $adapter.Fill($dataSet)
} catch {
print “Invoke-SQL: sqlCommand=[$($sqlCommand)] Error=[$($Error[0])] retVal=[$($retVal)]”

}

$connection.Close() > $null
$dataSet.Tables
}
}

Update Excel file using Powershell

Function UpdateExcel($strQuery,$strFileName)
{
$strProvider = “Provider=Microsoft.ACE.OLEDB.12.0”
$strDataSource = “Data Source = $strFileName”
$strExtend = “Extended Properties=Excel 8.0”
#$strQuery = “Update [$strSheetName] set Name = ‘Som’ ”
$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()
}

Read Excel using Powershell

Function ReadExcel($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
}

Get Cluster nodes using Powershell

function Get-ClusterNode
{
param($cluster)
$ClusterNode = @()
$ObjNode = gwmi -class MSCluster_Node -namespace “root\mscluster” -computername $cluster | add-member -pass NoteProperty Cluster $cluster | Select-Object Name
foreach($Item in $ObjNode)
{
$CommonName = $Item.Name
$CompObj = Get-ADComputer -Identity $CommonName
$DistinguishedName = $CompObj.DistinguishedName
$QualifiedName = $CompObj.DNSHostName
$ClusterNode += $DistinguishedName+”:” + $CommonName +”:” + $QualifiedName
}
return $ClusterNode
}

Get the associated Groups for a User using Powershell

$domain = “”
$username = “$domain\UserName”
Add-Type -AssemblyName System.DirectoryServices.AccountManagement
$ct = [System.DirectoryServices.AccountManagement.ContextType]::Domain
$user = [System.DirectoryServices.AccountManagement.Principal]::FindByIdentity($ct,$userName)
$groupSamAccountName = $user.GetAuthorizationGroups() | select-object -expandproperty SamAccountName
$groupDistinguishedName = $user.GetAuthorizationGroups() | select-object -expandproperty DistinguishedName

Get list of installed applications from a machine

$domain = “”
$UserName = “”
$username = “$domain\$UserName”
$password = convertto-securestring “” -asplaintext -force
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $password
$App = GetAppListInstalledonMachine MachineName $cred

 

function GetAppListInstalledonMachine{
param(
$MachineName,
$credentials
)
Begin
{
$Apps = Get-WmiObject -ComputerName $MachineName -Class Win32_Product -credential $credentials | Select-Object -Property Name
return $Apps
}
}

 

Get Groups from Active Directory using Powershell

$groups = Get-ADGroup -Filter *
$groupSamAccountName = $groups | select-object -expandproperty SamAccountName
$groupDistinguishedName = $groups | select-object -expandproperty DistinguishedName
$groupMemberCount = $groups
$groupDescription = $groups | select-object -expandproperty Description

Get Users from Active Directory using Powershell

$AllUserDetails = Get-ADUser -Filter * -Properties DisplayName,EmailAddress,SamAccountName,DistinguishedName
$usersSAMAccount = $AllUserDetails| Select-Object -ExpandProperty SamAccountName
$usersDistinguishedName = $AllUserDetails| Select-Object -ExpandProperty DistinguishedName
$usersName = $AllUserDetails| Select-Object -ExpandProperty DisplayName
$usersUserPrincipalName = $AllUserDetails| Select-Object -ExpandProperty EmailAddress

Get Computers from Active Directory Using Powershell

$ComputerDetails = Get-ADComputer -Filter *
$computer = $ComputerDetails | select-object -expandproperty name
$computerDistinguishedName = $ComputerDetails | select-object -expandproperty DistinguishedName
$computerDNSHostName = $ComputerDetails | select-object -expandproperty DNSHostName
$WebAPIComputerName = $DomainObj | Select-Object -expandproperty CommonName
$WebAPIComputerQualifiedName = $DomainObj | Select-Object -expandproperty QualifiedName
$WebAPIComputerDistinguishedName = $DomainObj | Select-Object -expandproperty DistinguishedName

Get Domain From Active Directory Using Powershell

$DomainForest = Get-ADForest
$DomainList = $DomainForest.Domains