Powershell

From Slothx.net

Jump to: navigation, search

Contents

Powershell General Info

Powershell Studio 2012 Tips

Import-Module GroupPolicy Issue

Edit all the config files in the following folder:

C:\Program Files (x86)\Common Files\SAPIEN Debugger 2012\

Replace the xml with the following:

<?xml version="1.0" encoding="utf-8" ?> 
<configuration> 
<startup useLegacyV2RuntimeActivationPolicy="true"> 
<supportedRuntime version="v2.0" />
 <supportedRuntime version="v4.0" /> 
</startup> 
</configuration>

Powershell Downloads

Powershell Tips

  • get-command
  • get-help
  • get-drives
  • get-members
  • get-command *object
  • How to run scripts when security is an issue: Set-ExecutionPolicy UnRestricted

Powershell Articles / Blogs

Powershell Microsoft Downloads

List file changes with owner info

$file_path = "D:\Data"
$a = Get-ChildItem -Path $file_path -rec -file -force | Where-Object { $_.LastWriteTime -gt '3/21/2019' } | `
select fullname,`
  @{Name="Size In KB";Expression={ "{0:N0}" -f ($_.Length / 1KB)}},`
  @{Name="Size";Expression={"{0:N0}" -f $_.Length}}, `
  LastWriteTime,CreationTime,LastAccessTime,@{N='Owner';E={$_.GetAccessControl().Owner}}

$file_path = "D:\Data"
$csv_file = "DataAnalysis.csv"
Get-ChildItem -Path $file_path -rec -file -force | Where-Object { $_.LastWriteTime -gt '3/21/2019' } | `
select fullname, `
  @{Name="Size In KB";Expression={ "{0:N0}" -f ($_.Length / 1KB)}},
  @{Name="Size";Expression={"{0:N0}" -f $_.Length}}, `
  LastWriteTime,CreationTime,LastAccessTime,Length,@{N='Owner';E={$_.GetAccessControl().Owner}} | `
Export-Csv -Path $csv_file -NoTypeInformation

$file_path = "D:\Data"
$text_file = "DataAnalysis.txt"
Get-ChildItem -Path $file_path -rec -file -force | Where-Object { $_.LastWriteTime -gt '3/21/2019' } | `
select fullname, `
  @{Name="Size In KB";Expression={ "{0:N0}" -f ($_.Length / 1KB)}},`
  @{Name="Size";Expression={"{0:N0}" -f $_.Length}}, `
  LastWriteTime,CreationTime,LastAccessTime,Length,@{N='Owner';E={$_.GetAccessControl().Owner}} | `
Out-File -Path $text_file

Update Powershell Help with Proxy Server

$webclient = New-Object System.Net.WebClient
$creds = Get-Credential
$webclient.Proxy.Credentials = $creds
update-help

Powershell Binary Serialization

# References
#
# Basic Serialization
# http://msdn.microsoft.com/en-us/library/4abbf6k0(v=vs.110).aspx

# -----------------------------------------------------------------
# Serialize/Deserialize object to memory (binary)
# -----------------------------------------------------------------

function fnSerialize_to_Memory($object)
{
    $ms = New-Object System.IO.MemoryStream
    $bf = New-Object System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
    $bf.Serialize($ms, $object)
    
    return $ms
}

function fnDeserialize_from_Memory($stream)
{

    $bf = New-Object System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
    $stream.Seek(0, [System.IO.SeekOrigin]::Begin)
    $memory_object = $bf.Deserialize($stream)
    $stream.Close()

    return $memory_object
}

# -----------------------------------------------------------------
# Serialize/Deserialize object to file (binary)
# -----------------------------------------------------------------

function fnSerialize_to_File($object,$filename)
{    
    $fs = new-object System.IO.FileStream $filename, "Create"
    $bf = New-Object System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
    $bf.Serialize($fs,$object)
    $fs.Close()    
}

function fnDeserialize_From_File($filename)
{    
    $fs = new-object System.IO.FileStream $filename, "Open"
    $bf = New-Object System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
    $file_object = $bf.Deserialize($fs)
    $fs.Close()
    return $file_object
}

$data1 = @{
    First = 'Fred';
    Last = 'Smith';
    StartDate = Get-Date '2002-03-23';
}

$stream = fnSerialize_to_Memory $data1
$data2 = fnDeserialize_from_Memory $stream

fnSerialize_to_File $data1 "test.dat"
$data3 = fnDeserialize_From_File "test.dat"

write-host "Data1: " 
$data1
Write-Host ""

write-host "Data2: "
$data2
Write-Host ""

write-host "Data3: "
$data3
Write-Host ""

Powershell Remote Machine Reboot

$computer = "XXX"
Get-EventLog -ComputerName $computer System | where-object {$_.EventID -eq "6005"} | sort -desc TimeGenerated

Using Powershell with SQL


$ServerInstance = "localhost"
$Database = "SMS_XXX"
$ConnectionTimeout = 30
$Query = "SELECT DISTINCT DisplayName0 as Product ,COUNT(*) as Count FROM v_GS_ADD_REMOVE_PROGRAMS WHERE (DisplayName0 LIKE '% Visio %') OR (DisplayName0 LIKE '% Project %') Group BY DisplayName0"

$QueryTimeout = 120

$conn=new-object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables | format-table -AutoSize

Example 2


$ServerInstance = "localhost"
$Database = "SMS_XXX"
$ConnectionTimeout = 30
$Query = "SELECT DISTINCT DisplayName0 as Product ,COUNT(*) as Count FROM v_GS_ADD_REMOVE_PROGRAMS WHERE (DisplayName0 LIKE '% Visio %') OR (DisplayName0 LIKE '% Project %') Group BY DisplayName0"

$Query = @"
SELECT DISTINCT 
                      v_R_System.ResourceID, v_R_System.Netbios_Name0, v_GS_ADD_REMOVE_PROGRAMS.DisplayName0, v_GS_WORKSTATION_STATUS.LastHWScan
FROM         v_GS_ADD_REMOVE_PROGRAMS INNER JOIN
                      v_R_System ON v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceID INNER JOIN
                      v_GS_WORKSTATION_STATUS ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID
WHERE     (v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '% Visio %') OR
                      (v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '% Project %')
ORDER BY v_R_System.Netbios_Name0
"@

$Query = @"
SELECT DISTINCT 
                      v_R_System.ResourceID, v_R_System.Netbios_Name0, v_GS_ADD_REMOVE_PROGRAMS.DisplayName0, v_GS_WORKSTATION_STATUS.LastHWScan
FROM         v_GS_ADD_REMOVE_PROGRAMS INNER JOIN
                      v_R_System ON v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceID INNER JOIN
                      v_GS_WORKSTATION_STATUS ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID
Where (DisplayName0 Like '% Visio %' OR DisplayName0 Like '% Project %')
ORDER BY v_R_System.Netbios_Name0
"@

$QueryTimeout = 120

$conn=new-object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables | format-table -AutoSize

Example 3



<#
USE [TestDB]
GO

/****** Object:  Table [dbo].[Computers]    Script Date: 30/07/2014 8:45:12 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Computers](
	[IndexNumber] [int] NULL,
	[ComputerName] [nvarchar](50) NULL,
	[InvDate] [datetime] NULL
) ON [PRIMARY]

GO
#>

clear


function fnADO_Test($number_of_records)
{

    $adOpenKeyset = 3
    $adLockOptimistic = 3

    $objConnection = New-Object -com "ADODB.Connection"
    $objRecordSet = New-Object -com "ADODB.Recordset"

    $connection_string = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=TestDB;Integrated Security=SSPI;" 

    $objConnection.Open($connection_string)

    $objRecordset.Open("Select * From Computers", $objConnection,$adOpenKeyset ,$adLockOptimistic)

    for ($i = 1; $i -le $number_of_records ; $i++)
    {
        $objRecordSet.AddNew()
        $objRecordSet.Fields.Item("IndexNumber").Value = $i
        $objRecordSet.Fields.Item("ComputerName").Value = "PC" + $i
        $objRecordSet.Fields.Item("InvDate").Value = [DateTime]::Now
        $objRecordSet.Update()
    }

    $objRecordSet.Close()
    $objConnection.Close()
}


function fnClear_Table()
{
    $connection_string = "Data Source=localhost;Initial Catalog=TestDB;Integrated Security=SSPI;" 
    $sqlconnection = new-object system.data.SqlClient.SQLConnection($connection_string);
    $sqlcmd = new-object system.data.sqlclient.sqlcommand("delete from Computers", $sqlconnection);
    $sqlconnection.Open()
    $sqlcmd.ExecuteNonQuery() | Out-Null
    $sqlconnection.Close();
}


function fnInsert_Test($number_of_records)
{

    $connection_string = "Data Source=localhost;Initial Catalog=TestDB;Integrated Security=SSPI;" 
    $sqlconnection = new-object system.data.SqlClient.SQLConnection($connection_string);
    $sqlcmd = new-object system.data.sqlclient.sqlcommand
    $sqlconnection.Open()
    $sqlcmd.Connection = $sqlconnection

    $sql = "Insert into dbo.Computers (IndexNumber,ComputerName,InvDate) Values (@IndexNumber,@ComputerName,@InvDate);"

    $sqlcmd.CommandText = $sql    

    $sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@IndexNumber",[Data.SQLDBType]::Int))) | Out-Null
    $sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@ComputerName",[Data.SQLDBType]::NVarChar, 50))) | Out-Null
    $sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@InvDate",[Data.SQLDBType]::DateTime))) | Out-Null
    

    for ($i = 1; $i -le $number_of_records ; $i++)
    {
        $sqlcmd.Parameters[0].Value = $i
        $sqlcmd.Parameters[1].Value = "PC" + $i
        $sqlcmd.Parameters[2].Value = [DateTime]::Now
        $sqlcmd.ExecuteNonQuery() | Out-Null
    }

    $sqlconnection.Close();

}

function fnUpdate_Test($number_of_records)
{
    $connection_string = "Data Source=localhost;Initial Catalog=TestDB;Integrated Security=SSPI;" 
    $sqlconnection = new-object system.data.SqlClient.SQLConnection($connection_string);
    $sqlcmd = new-object system.data.sqlclient.sqlcommand
    $sqlconnection.Open()
    $sqlcmd.Connection = $sqlconnection

    # Update Computers set IndexNumber = -1, ComputerName = 'Hello' Where IndexNumber = 1
    $sql = "Update Computers set IndexNumber = @p1, ComputerName = @p2 Where IndexNumber = @p3"

    $sqlcmd.CommandText = $sql    

    $sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@p1",[Data.SQLDBType]::Int))) | Out-Null
    $sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@p2",[Data.SQLDBType]::NVarChar, 50))) | Out-Null
    $sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@p3",[Data.SQLDBType]::Int))) | Out-Null
    

    for ($i = 1; $i -le $number_of_records ; $i++)
    {
        $sqlcmd.Parameters[0].Value = -1
        $sqlcmd.Parameters[1].Value = "Hello"
        $sqlcmd.Parameters[2].Value = 1
        $sqlcmd.ExecuteNonQuery() | Out-Null
    }

    $sqlconnection.Close();


}


fnClear_Table
$time1 = Measure-Command {fnADO_Test 1000}
fnClear_Table
$time2 = Measure-Command {fnInsert_Test 1000}
fnUpdate_Test 1
$time1 
$time2
exit

Using Powershell with Excel 2007

# Inport file into excel via powershell
# Needs lots of work :)

# Extracts from...
# http://www.vistax64.com/powershell/27171-powershell-excel.html
# Roman Kuzmin



function Invoke([object]$m, [string]$method, $parameters)
{
 $m.PSBase.GetType().InvokeMember(
 $method, [Reflection.BindingFlags]::InvokeMethod, $null, $m, $parameters,
 $ciUS)
}

function GetProperty([object]$m, [string]$property, $parameters)
{
 $m.PSBase.GetType().InvokeMember(
 $property, [Reflection.BindingFlags]::GetProperty, $null, $m, $parameters,
 $ciUS)
}

function SetProperty([object]$m, [string]$property, $parameters)
{
 $m.PSBase.GetType().InvokeMember(
 $property, [Reflection.BindingFlags]::SetProperty, $null, $m, $parameters,
 $ciUS)
}

# US culture info
$ciUS = [System.Globalization.CultureInfo]'en-US'

$oExcel = New-Object -COM Excel.Application
$oExcel.Visible = $true
$oBooks = $oExcel.Workbooks 
$oBook = Invoke $oBooks Add
$oSheet = $oBook.Worksheets.Item(1)
$oTables = $oSheet.QueryTables

SetProperty $oSheet Name Ward1;

$a = getProperty $oSheet Name;

get-process | export-csv c:\temp\output.csv -noTypeInformation;

Invoke $oBooks open("c:\temp\output.csv");

$sheet = $oExcel.ActiveSheet;


List Domain Controllers

# -------------------------------------------------------
# test.ps1
# -------------------------------------------------------

# Get the Active Directory name of the forest.


# -----------------------------------------------------------------
# fnGet_Domain_controller_list: 
#
# This will query the current AD forest running in the current user
# context.
#
# Parameters:
#
# None
#
# Returns:
#
# A list of domain controllers.
#
# -----------------------------------------------------------------

function fnGet_Domain_controller_list
{
  # Get the current name of the AD Forest.

  $forest = [system.directoryservices.activedirectory.Forest]::GetCurrentForest()
  $domain = $forest.Domains[0]
  write-host "Processing Domain: " $domain
  $domain_controller_list = $forest.domains | %{$_.DomainControllers} | %{$_.Name}
  return $domain_controller_list;

}


# -----------------------------------------------------------------
# fnGet_dns_info
#
# This will retrieve the DNSServerSearchOrder for the specified
# machine name. This will connect to the root\CIMV2 name space 
# of the specified computer and query the 
# "Win32_NetworkAdapterConfiguration" object.
#
# Parameters:
#
# $computer_name: The name of the passed computer.
#
# Returns:
#
# A list of domain controllers.
#
# -----------------------------------------------------------------


function fnGet_dns_info ($computer_name)
{
 $computer_details = get-wmiobject -class "Win32_NetworkAdapterConfiguration" `
     -namespace "root\CIMV2" -computername $computer_name -filter "IPEnabled = true";
  $dns_list = $computer_details.DNSServerSearchOrder
  return $dns_list;
}


# -----------------------------------------------------------------
# fnSet_dns_info
#
# This will set the DNSServerSearchOrder for the specified
# machine name. This will connect to the root\CIMV2 name space 
# of the specified computer and update the 
# "Win32_NetworkAdapterConfiguration" object.
#
# Parameters:
#
# $computer_name:        The name of the passed computer.
# $primary_dns_Server:   The IP address of the new Primary DNS Server. (e.g. 192.168.1.50)
# $secondary_dns_server: The IP address of the new Secondary DNS Server. (e.g. 192.168.1.51)
#
# Returns:
#
# Nothing
#
# Sample Usage: 
#
# fnSet_dns_info "." "192.168.1.50" "192.168.1.51"
#
# Example Class:
#
# instance of Win32_NetworkAdapterConfiguration
#
#
# -----------------------------------------------------------------

function fnSet_dns_info ($computer_name,$dns_search_order)
{
   write-host "Computer Name: " $computer_name
   write-host "DNS Search Order: " $dns_search_order
   $global:Nic = Get-WMIObject Win32_NetworkAdapter -computername $computer_name |
         where{$_.NetConnectionID -eq "Local Area Connection"};

   $global:Config = Get-WMIObject Win32_NetworkAdapterConfiguration -computername $computer_name |
            where{$_.MACAddress -eq $Nic.MACAddress -and $_.IPEnabled};
   $global:Config.SetDNSServerSearchOrder($dns_search_order)  | out-null;

}


#
#
# MAINLINE ----------------------------------------------------------
# 
# Execution starts here.
#

# $dc_list = fnGet_Domain_controller_list;

# foreach ($dc in $domain_controller_list)
# {
#  write-host "DC: " $dc;
# }
#
# $global:pc = fnGet_dns_info ".";
#
# $dns_search_order = "192.168.1.51","192.168.1.52";
# fnSet_dns_info "." $dns_search_order;


Misc (To fix up)

Powershell Tools

Powershell TODO list

# Decode a Win32 Error Code. Example below x5A8.

$err_msg = (new-object System.ComponentModel.Win32Exception(0x5A8));
$err_msg;
Scroll bar range cannot be greater than MAXLONG

Read event logs from a remote machine

function fnGetRemoteEventLogs($computer_name,$credentials)
{
	write-host "";
	write-host "Connecting to: " $computer_name -foregroundcolor yellow;
	write-host "";
	$event_list = get-wmiobject –class win32_ntlogevent –computerName $computer_name -Credential ($credentials);
	$event_list;
}

$credentials = Get-Credential;
$computer_name = read-host -prompt "Remote Computer?: ";
#$computer_name = "Test1";
$event_logs = fnGetRemoteEventLogs $computer_name, $credentials;

Assemblies loaded in PowerShell

[appdomain]::currentdomain.getassemblies() | sort -property fullname | format-table fullname 

Site Changes

Site Navigation

Site Changes

Site Navigation

Personal tools