Powershell

Get-ChildItem -Recurse C:\MeinPfad | Select-String 'suchbegriff'

Set-StrictMode -Version 4.0

[string] $inputfile = "H:\Data\Source\Powershell\Input\input.csv"
[string] $outputfile = "H:\Data\Source\Powershell\Output\output.csv"

[array] $rows = Import-Csv -Path $inputfile -Delimiter ";" -Encoding "UTF8" -Header 'id', 'firstname', 'lastname'

foreach ($row in $rows){
#add new column with id enclosed in single quotes, e.g. for SQl statement
$newvalue = " '" + $row.id + "', "
$row | Add-Member -MemberType NoteProperty -Name 'ID_Select' -Value $newvalue
}

#convert PSObject to CSV and write to output file
if (Test-Path $outputfile){ Remove-Item $outputfile}

$rows | ConvertTo-Csv -Delimiter ";" -NoTypeInformation | Out-File -FilePath $outputfile -Encoding "UTF8" -Append

Set-StrictMode - Version 4.0
clear

$inputfile = "H:\Data\Source\Powershell\Input\input.xlsx"
$outputfile = "H:\Data\Source\Powershell\Output\output.xml"

$excel = New-Object -ComObject excel.application
# do not start excel to show file
$excel.Visible = $false
$workbook = $excel.Workbooks.Open($inputfile)
$sheet =$workbook.Worksheets.Item("Sheet1")

#if we have header record, otherwise set to 0
[int] $rownum = 1

if (Test-Path $outputfile){ Remove-Item $outputfile}
$xml = New-Object System.Xml.XmlTextWriter($outputfile,[System.Text.Encoding]::UTF8);
$xml.Formatting = "Indented"
$xml.Indentation = 1
$xml.IndentChar = "`t"
$xml.WriteStartDocument()
$xml.WriteStartElement("mystarttag")

do {
if($rownum -gt 1){
$xml.WriteStartElement("put")
$xml.WriteAttributeString("type","myfunction")
$xml.WriteAttributeString("operationcode","create")

$xml.WriteElementString("userid",$sheet.Cells.Item($rownum,1).Text)
$xml.WriteElementString("firstname",$sheet.Cells.Item($rownum,2).Text)
$xml.WriteElementString("lastname",$sheet.Cells.Item($rownum,3).Text)
}
$rownum++

} while($sheet.Cells.Item($rownum,1).Text.Length -gt 0)

$xml.WriteEndElement()
$xml.WriteEndDocument()
$xml.Flush()
$xml.Close()
$excel.Quit()

clear
Set-StrictMode -Version 4.0

$url= 'https://servername/path/type/params/namefirst=xxxx'
[string] $username = 'xxxxxxx'
[string] $password = 'xxxxxxx'

$pwd = [System.Text.Encoding]::ASCII.GetBytes($username+':'+$password)
$pwdbase64 = [Convert]::ToBase64String($pwd)

$headers = @{
Authorization = "Basic " + $pwdbase64
Accept = 'application/json'
}

$res = Invoke-WebRequest -Uri $url -Headers $headers | Select-Object -ExpandProperty Content
echo $res

[string] $splitme = "{`"upnsuffix`":`"gi-de.com`",`"comment`":`"Kommentar`"}"
$splitme
$res = [regex]::split($splitme,'^{"upnsuffix":"([a-zA-z.\-]*)","comment":"([a-zA-z.\-]*)"}$')
foreach ($result in $res) {
    $result.trim()
}
exit

[string] $splitme = "1,2 ,3,   4"
$res = [regex]::split($splitme,'^([0-9 ]{1,}),([0-9 ]{1,}),([0-9 ]{1,}),([0-9 ]{1,})')
foreach ($result in $res) {
    $result.trim()

XLST

1. Dot Sourcing all classes used
check $PROFILE in ISE to find out path for script that will be run on start of ISE (C:\Users\YourUserName\Documents\WindowsPowerShell\Microsoft.PowerShellISE_profile.ps1)

. ("H:\Data\Source\Powershell\TABLES\AD_ACCOUNT.ps1") -Force
. ("H:\Data\Source\Powershell\TABLES\USER.ps1") -Force
. ("H:\Data\Source\Powershell\UTILS\OracleUtils.ps1") -Force
. ("H:\Data\Source\Powershell\UTILS\LdapUtils.ps1") -Force

###################

# RUN

########

Set-StrictMode -Version 4.0

$ldaputils = $null
$ldaputils = [LdapUtils]::new("ACCOUNTS.INTERN")
$attrs = "samaccountname","sn","givenname", "lastlogon", "lastlogontimestamp", "useraccountcontrol"
$res = $ldaputils.searchSamAccountName("beiermat", $attrs)
$res.sn

$oracle = $null
$oracle = [OracleUtils]::new("PROD")

#[USER] $user = $oracle.getUser(127395)
$ix=0
$users = $oracle.getUserByCompany(865)
foreach($user in $users){
$ix++
$user.name
}
"Found $ix"

##################

# LDAP UTILS

#########################

Set-StrictMode -Version 4.0
. ("H:\Data\Source\Powershell\TABLES\AD_ACCOUNT.ps1") -Force

class LdapUtils{

$connection = $null
[string] $filter
#static [array] $datetimeattrs = "lastlogon","lastlogontimestamp","whencreated","lastlogoff"

#Constructor
LdapUtils( $environment){
if ($environment.ToUpper() -eq "YOUR.SERVER"){
$root = New-Object System.DirectoryServices.DirectoryEntry("LDAP://xxxxxx.xxxxx.xxxx")
$this.connection = New-Object System.DirectoryServices.DirectorySearcher($root)
$this.connection.PageSize = 100
$this.connection.SearchScope = "subtree"
} else {
throw "Environment $environment not known"
}
}

[AD_ACCOUNT] searchSamAccountName( [string] $samaccountname, [array] $attributes) {

[AD_ACCOUNT] $result = $null
$this.filter = "(&(objectclass=person)(samaccountname=$samaccountname))"
$this.connection.Filter = $this.filter
if ($attributes -eq $null) {
$this.getDefaultAttributes() | foreach { [void]$this.connection.PropertiesToLoad.Add($_) }
} else {
$attributes | foreach { [void]$this.connection.PropertiesToLoad.Add($_) }
}
$account = $this.connection.FindAll()
if ($account -ne $null){
$account.getType()
$result = $this.getAttributeValues($account, $attributes)

}
return $result
}

[array] getDefaultAttributes() {

$attrs = @(
"accountexpires",
"cn",
"department",
"displayname",
"distinguishedname",
"employeeID",
"givenname",
"lastlogoff",
"lastlogon",
"lastlogontimestamp",
"mail",
"memberof",
"proxyaddresses",
"pwdlastset",
"samaccountname",
"sn",
"useraccountcontrol",
"userprincipalname",
"whenchanged",
"whencreated"
)
return $attrs

}

[AD_ACCOUNT] getAttributeValues($account, $attributes) {

[AD_ACCOUNT] $result = [AD_ACCOUNT]::new()
foreach ($prop in $account.Properties.PropertyNames) {

#DateTimeAttributes
if([AD_ACCOUNT]::datetimeattrs -match $prop) {
try {
$result.$prop = [DateTime]::FromFileTime($account.Properties.$prop[0]).ToString('yyyy-MM-dd HH:mm:ss')
} catch {
#write-host $_
$result.$prop = "never"
}
} elseif ($prop -eq "useraccountcontrol"){
$result.$prop = $account.Properties.$prop[0]
$result.enabled = (($account.Properties.$prop[0] -band 2) -eq 0)
#{$temp = [int][string]$account.Properties.useraccountcontrol
#$enabled =

} else {
$result.$prop = $account.Properties.$prop
}
}
Write-HOst $result.lastlogontimestamp
return $result
} # end getAttributes()

} # end class

#####################

# AD ACCOUNT

##################

Set-StrictMode -Version 4.0

class AD_ACCOUNT {

[string] $accountexpires
[string] $cn
[string] $department
[string] $displayname
[string] $distinguishedname
[string] $employeeid
[string] $givenname
[string] $lastlogoff
[string] $lastlogon
[string] $lastlogontimestamp
[string] $mail
[array] $memberof
[array] $proxyaddresses
[string] $pwdlastset
[string] $samaccountname
[string] $sn
[int] $useraccountcontrol
[string] $userprincipalname
[string] $whenchanged
[string] $whencreated

#custom attributes
[string] $adspath
[boolean] $enabled

static [array] $datetimeattrs = (
"accountexpires",
"lastlogoff",
"lastlogon",
"lastlogontimestamp",
"pwdlastset",
"whenchanged",
"whencreated"
)
} # end class

###################

# USERTAB

########

clear
Set-StrictMode -Version 4.0

class USER {

[int32] $usernr
[string] $lastname
[string] $firstname
[datetime] $entrydate
}

######################

# Oracle Utils

##########

Set-StrictMode -Version 4.0

Add-Type -Path "C:\your\path\Downloads\ODP.NET_Managed_ODAC122cR1\odp.net\managed\common\Oracle.ManagedDataAccess.dll"

class OracleUtils{

$connection = $null

#Constructor

OracleUtils( $environment){

if ($environment.ToUpper() -eq "PROD"){

$username = "myuser"

$password = "mypassword"

$server   = "myserver"

$port     = "1521"

$service  =  "myservice"

} elseif ($environment.ToUpper() -eq "DEV") {

$username = "myuser"

$password = "mypassword"

$server   = "myserver"

$port     = "1521"

$service  =  "myservice"

} else {

throw "Environment $environment not known"

}

$data_source = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$server)(PORT=$port))(CONNECT_DATA = (SERVER=dedicated)(SERVICE_NAME=$service)))"

$connection_string = "User Id=$username;Password=$password;Data Source=$data_source"

try{

$this.connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connection_string)

$this.connection.Open()

} catch {

Write-Error (“Can’t open connection: {0}`n{1}” -f `

$scipt:oracle.ConnectionString, $_.Exception.ToString())

}

} #end OracleUtils($environment)

[array] getUserByCompany([int] $firma) {

$result = $null

$sqlcmd = $this.connection.CreateCommand()

[String] $sql = "select usernr from user where firma = :firma and status = 1"

$sqlcmd.CommandText = $sql

$sqlcmd.Parameters.Add(":firma",$firma),

$sqlcmd.Prepare()

$rows = $sqlcmd.ExecuteReader()

if ($rows.hasRows) {

$result = @()

while ($rows.Read()) {

[USER] $user = $this.getUserByID($rows[0])

$result += $user

}

}

$rows.dispose()

$sqlcmd.dispose()

return $result

}

[USER] getUserByID([int] $usernr) {

$result = $null

$sqlcmd = $this.connection.CreateCommand()

[String] $sql = "select

USERNR,

LASTNAME,

FIRSTNAME,

ENTRYDATE

from user

where usernr = :usernr"

$sqlcmd.CommandText = $sql

$sqlcmd.Parameters.Add(":usernr",$usernr),

$sqlcmd.Prepare()

$rows = $sqlcmd.ExecuteReader()

while ($rows.Read()) {

$result = new-object USER

$result.usernr                = if( $rows[0] -is [DBNull]) { $null} else { $rows[0]}

$result.lastname              = if( $rows[1] -is [DBNull]) { $null} else { $rows[1]}

$result.firstname             = if( $rows[2] -is [DBNull]) { $null} else { $rows[3]}

$result.entrydate             = if( $rows[3] -is [DBNull]) { [datetime]0 } else { $rows[4]}

}

$rows.dispose()

$sqlcmd.dispose()

return $result

}

} #end class OracleUtils

clear

function Main{
[int] $script:count_total = 0
[int] $script:count_notfound_enabled = 0
[int] $script:count_notfound_disabled = 0
[int] $script:count_found = 0

# Database

Add-Type -Path "C:\Users\Downloads\ODP.NET_Managed_ODAC122cR1\odp.net\managed\common\Oracle.ManagedDataAccess.dll"

$username = "xxxxxx
$password = "xxxxxx"
$data_source = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=your.server.name)(PORT=1521))(CONNECT_DATA = (SERVER=dedicated)(SERVICE_NAME=yourservicename)))"
$connection_string = "User Id=$username;Password=$password;Data Source=$data_source"

[Oracle.ManagedDataAccess.Client.OracleConnection] $script:oracle = $null
try{
$script:oracle = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connection_string)
$script:oracle.Open()
} catch {
Write-Error (“Can’t open connection: {0}`n{1}” -f `
$scipt:oracle.ConnectionString, $_.Exception.ToString())
}

#LDAP
$root = New-Object System.DirectoryServices.DirectoryEntry("LDAP://your.server.name")
$searcher = New-Object System.DirectoryServices.DirectorySearcher($root)
#$attributes = "samaccountname","sn","givenname","useraccountcontrol","lastlogon","lastlogontimestamp"
#$attributes | foreach { [void]$searcher.PropertiesToLoad.Add($_) }
$searcher.PageSize = 100
$searcher.SearchScope = "subtree"

Remove-Item -Path "H:\Data\Source\Powershell\Output\myoutput.csv"

$users = Get-DBUser

$ix=0

foreach ($user in $users){
$ix++
if($ix%50 -eq 0){$ix}
$samaccountname = $user.ad_account
if ($samaccountname -ne "missing") {
$filter = "(&(objectclass=person)(samaccountname=$samaccountname))"
$searcher.Filter = $filter
$account = $searcher.FindAll()
if ($account -ne $null) {
$temp = [int][string]$account.Properties.useraccountcontrol
$enabled = (($temp -band 2) -eq 0)
if ($enabled) {
Add-Member -InputObject $user -Type NoteProperty -name "ENABLED" -value "yes"
} else {
Add-Member -InputObject $user -Type NoteProperty -name "ENABLED" -value "no"
}
$uac = $account.Properties.useraccountcontrol[0]
Add-Member -InputObject $user -Type NoteProperty -name "UAC" -value $uac
try {
$lastlogon = ([datetime]::FromFileTime($account.Properties.lastlogon[0]).toString('yyyy-MM-dd'))
} catch {
$lastlogon = "never"
}
if ($lastlogon -eq "1601-01-01" -or $lastlogon -eq " "){$lastlogon = "never"}
Add-Member -InputObject $user -Type NoteProperty -name "LASTLOGON" -value $lastlogon
try {
$lastlogontimestamp = ([datetime]::FromFileTime($account.Properties.lastlogontimestamp[0]).toString('yyyy-MM-dd'))
} catch {
$lastlogontimestamp = "never"
}
if ($lastlogontimestamp -eq "1601-01-01" -or $lastlogontimestamp -eq " " ){$lastlogontimestamp = "never"}
Add-Member -InputObject $user -Type NoteProperty -name "LASTLOGONTIMESTAMP" -value $lastlogontimestamp
} else {
$user.ad_account += " (deleted)"
Add-Member -InputObject $user -Type NoteProperty -name "ENABLED" -value " "
Add-Member -InputObject $user -Type NoteProperty -name "UAC" -value " "
Add-Member -InputObject $user -Type NoteProperty -name "LASTLOGON" -value " "
Add-Member -InputObject $user -Type NoteProperty -name "LASTLOGONTIMESTAMP" -value " "
}
} else {
$user.ad_account = " "
Add-Member -InputObject $user -Type NoteProperty -name "ENABLED" -value " "
Add-Member -InputObject $user -Type NoteProperty -name "UAC" -value " "
Add-Member -InputObject $user -Type NoteProperty -name "LASTLOGON" -value " "
Add-Member -InputObject $user -Type NoteProperty -name "LASTLOGONTIMESTAMP" -value " "
}
Export-Csv -InputObject $user -Path "H:\Data\Source\Powershell\Output\myoutput.csv" -Append -Delimiter ";"
}

"Processed $ix users"
if ($script:oracle.State -eq ‘Open’) {
$oracle.close()
}

} # end function Main

function Get-DBUser {

$results = @()
$sqlcmd = $oracle.CreateCommand()

[String] $sql = @"
select u.usernr, u.name, u.vorname,
coalesce(lower(a.name),'missing') as ad_account
from user u
left outer join account a
on a.usernr = u.usernr
order by firma, name, vorname
"@

$sqlcmd.CommandText = $sql
$users = $sqlcmd.ExecuteReader()
$ix = 0;

while ($users.Read()) {
$result1 = [ordered] @{
"USERNR" = $users.GetInt32(0)
"LASTNAME" = $users.GetString(1)
"FIRSTNAME" = $users.GetString(2)
"AD_ACCOUNT" = $users.GetString(3).toLower()
}
$result = New-Object pscustomobject -Property $result1
$results += $result
}
return $results
$users.dispose()
$sqlcmd.dispose()
} #end function Get-EdataUser

#call Main Function
Main
exit

Set-StrictMode -Version 4.0

clear

trap [Exception]

{

Write-Host $_.Exception;

}

$inputfile  = "H:\Data\Source\Powershell\Input\input.xml"

$outputfile = "H:\Data\Source\Powershell\Output\xslt1.html"

$xsltfile   = "H:\Data\Source\Powershell\XSLT\test1.xslt"

$xslt = New-Object System.Xml.Xsl.XslCompiledTransform;

#Register-ObjectEvent -InputObject $xslt -EventName XsltMessageEncountered -Action { Write-Host ("Message: " + $EventArgs.Message) };

$xslt.load( $xsltfile )

$xslt.Transform( $inputfile, $outputfile )

Get-Content $outputfile

Get-ChildItem -Recurse -Path "H:\data\Source\Powershell" | select-String searchSam