Tag Archives: Export

Microsoft, Powershell, Storingen

Search for specific text in files on a location

Sometimes a administrator is forced to do some cleaning up that should have been done a million years ago. When previous administrators have left you a nice bucket of history in your environment. A customer of mine used to have a few fileservers around that aren’t there anymore. In DNS those names are resolving to a new CIFS server. However that location is going to change again and now we are forced to clean up that old mess. Of course I’m not going to do the job of opening a few hundred thousand .ini, .config, .cfg and so forth myself. I created a script that does the job and can be delegated to someone else who has more time than me.

Clear
$Outputdir = \\Outputdirpath


$Location = Read-Host "Voer het volledige DFS pad in, in UNC Format bijvoorbeeld:\\DFSFolder"
$TestResult = Test-Path $Location
While ($Testresult -ne $true){
write-host "De opgegeven locatie bestaat niet. Voor opnieuw in" -b Black -f Red
$Location = Read-Host "Voer de DFS Locatie in in UNC Format bijvoorbeeld:\\DFSFolder"
$TestResult = Test-Path $Location}

$Extensions =@(Read-Host 'Voer een extensie in.De Extensie moet een . bevatten')
While ($Extensions -notmatch "."){$Extensions =@(Read-Host 'Voer een extensie in.De Extensie moet een . bevatten')}
$Choice = $null
While ($Choice -notmatch "[y|n]"){$Choice = read-host "Wilt u nog een Extensie toevoegen (Y/N)"
If ($Choice -match "Y"){While ($Extension -notmatch "."){$Extension =(Read-Host 'Voer een extensie in')}
$Extension += $Extension
$Choice = $Null}}

Remove-Variable Choice
$MatchList =@(Read-Host 'Voer een waarde in waaraan voldaan moet worden. Voor een drive mapping is een dubbele backslash vereist')
$Choice = $null
While ($Choice -notmatch "[y|n]"){$Choice = read-host "Wilt u waarde opgeven (Y/N)"
If ($Choice -match "Y"){$MatchItem =(Read-Host 'Voer een waarde in waaraan voldaan moet worden. Voor een drive mapping is een dubbele backslash vereist')
$MatchList += $MatchItem
$Choice = $null}}

$PathName = GI $Location
If ($PathName.Parent.Name -eq $Null){$ExportFile = "$OutputDir\ContentExport-"+ $PathName.Name + ".csv"}
If ($PathName.Parent.Name -ne $Null){$ExportFile = "$OutputDir\ContentExport-"+ $PathName.Parent.Name +"-"+ $PathName.Name +".csv"}

$List = gci $Location -Recurse | Where {$Extensions -match $_.Extension}
Foreach ($Item in $List){$Content = Get-Content $Item.fullname
$Pad = $item.fullname
Foreach ($Extension in $Extensions){If ($Content -match $Extension){Add-Content -Value  "Er is een verwijzing naar $Extension gevonden in: $Pad" -Path $Exportfile}}}

For those who do not speak Dutch very well. If you are searching for a drive mapping you should enter a double slash. The rest you’ll have to find out yourself 😉

Powershell

Get file size of old files on a fileserver

So I was at a costumer. They wanted an export of the file size that files older than 7 years populate on the file server. I made a very simple script that does the job.

$Locatie = "D:\"
#Verander het cijfer om het aantal jaar aan te passen
$Datum = (Get-Date).AddYears(-7)
$Outfile = ".\Output.txt"
$List = @(GCI $Locatie -Recurse | Where {$_.LastWriteTime -lt $Datum})
$Outinfo = "De lijst bevat "+ $list.count +" bestanden"
Add-Content -Path $Outfile -Value $Outinfo
$Bytes = 0
ForEach ($Item in $List){($Bytes=$Bytes + [int]$Item.length)}
$Outinfo = "De Bestanden bevatten $Bytes bytes"
Add-Content -Path $Outfile -Value $Outinfo
$Outinfo = "Dat is "+ $Bytes/1048576 +" MegaBytes"
Add-Content -Path $Outfile -Value $Outinfo
Storingen

Users being disconnected randomly

Some users reported that their sessions where being disconnected. The warning that was stated was that the peer disconnected. On the The XenApp servers reported error 4105. The Terminal Server License server was member of the group Terminal Server License Server. There’s a KB article for this problem.

If you want to export the users that could expierence this problem you can run the script below. In order to export the right group you need to have the default SID for this built in group. This SID is: “S-1-5-32-561″. The right value that the user must have is:”5805bc62-bdc9-4428-a5e2-856a0f4c185e”.

 

Import-module ActiveDirectory
$List2 =@()
$File = ".\Export.csv"
$Header = "DN;ActiveDirectoryRights;InheritanceType;ObjectType;InheritedObjectType;ObjectFlags;AccessControlType;IdentityReference;IsInherited;InheritanceFlags;PropagationFlags"
Add-Content -Value $Header -path $file
$list = get-aduser -Filter * | select DistinguishedName
foreach ($item in $list){$list2 +=$item.DistinguishedName}
foreach ($item in $list2){
$ACLs =@((get-Acl "AD:\$item")| ForEach-Object {$_.Access} | Where {$_.Identityreference -eq "S-1-5-32-561"})
IF ($ACLs.count -gt "0"){
[int]$Count = 0
Foreach ($ACL in $ACLS){IF ($ACL.Objecttype -match "5805bc62-bdc9-4428-a5e2-856a0f4c185e"){$count++}}
IF ($Count -eq "0"){
Foreach ($ACL in $ACLs){
$OutInfo = $item  + ";" + $ACL.ActiveDirectoryRights  + ";" + $ACL.InheritanceType + ";" + $ACL.ObjectType + ";" + $ACL.InheritedObjectType + ";" + $ACL.ObjectFlags + ";" + $ACL.AccessControlType  + ";" + $ACL.IdentityReference   + ";" + $ACL.IsInherited   + ";" + $ACL.InheritanceFlags   + ";" + $ACL.PropagationFlags
Add-content -Value $outinfo -path $File}}}}
Overige

Back-up Databases in Plesk remotely

Some databases are more important then others. In some cases you want to back-up just a couple database more often. I have a Synology Diskstation DS413J at home. To have an off-site back-up for my most important databases is extremely important.  Some database must be retained for seven years for tax services here in the Netherlands. That is why I made a little shell script which exports databases and then, ftp explicit SSL, transfer it to the NAS device. First get the server ready transfer files over FTP(e)S.

sudo apt-get install lftp

The above code installs a lftp client on the in this case Ubuntu machine.

Next create a dir in which you want to dump the sql files in. I consider that you use Plesk 9+. In my case the folder is called /dumps. Create two .sh files with the corresponding code:

run.sh

	date=$(date +%x)
	exportdir="/dumps"
	while read database; do
	  filename="$exportdir/$database-$date.sql"
	  mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` $database > $filename
	done </root/databases.txt
	lftp -f /root/ftps.sh

ftps.sh

debug 10
set ftp:ssl-auth TLS
set ssl:verify-certificate no
set ftp:ssl-protect-data yes
open USER:PASSWORD@SERVER.TLD:21/DATADIRECTORY
mput /dumps/*
close

In databases.txt you list the databases that will be back-upped. The FTPS script copies any file in the Dump directory.

Then make a cron job which runs this task.

Overige

Create Tax report of OrangeHRM with Powershell

This code is only usefull if there is only one employee.

Export four seperate CSV files out of the database of OrangeHRM. The tables you should export are the following:

ohrm_customer
ohrm_project
ohrm_project_acitivity
ohrm_timesheet_item

You should use the following settings for the export ni PHPMyAdmin:

OrangeHRM

Export each table with the following names:

ohrm_customer                  – Customers.csv
ohrm_project                       – Projects.csv
ohrm_project_acitivity   – Activities.csv
ohrm_timesheet_item     – Items.csv

Place the CSV files and the script below in the same directory.

$Activities = import-csv ".\Activities.csv" -Delimiter ";"
$Customers = import-csv ".\Customers.csv" -Delimiter ";"
$Items = import-csv ".\Items.csv" -Delimiter ";"
$Projects = import-csv ".\Projects.csv" -Delimiter ";"
$Export = @()
Foreach ($item in $Projects){[int]$count = $item.customer_id
$count--
$item.customer_id = $Customers[$count].name
}
Foreach ($item in $items){
[int]$count = $item.Activity_id
$count--
$item.Activity_id = $Activities[$count].name

[int]$count = $item.project_id
$count--
$item.Employee_id = $Projects[$count].customer_id
[int]$count = $item.project_id
$count--
$item.project_id = $Projects[$count].name
}
$items | Export-CSV -path ".\Export.csv" -Delimiter ";"

This will export a CSV file in the same directory. The only thing you have to do now is replace the header of employee_id to something more meaningful. In fact you will probably have to replace all the headers to something a bit more meaning full.

 

Microsoft, Powershell

Using filters to find groups with mail address

You can export a list of all groups with the field mail enabled by running the following code:

$list = Get-ADGroup -Filter * -Properties * | Where {$_.mail -ne $null -and $_.GroupScope -match "Global"}
$list | export-csv -Path Globalmetmail.csv -Delimiter ";"

To find groups inside a certain OU for example if you have a distribution OU you may want to export certain Grouptypes:

$list2 = Get-ADGroup -Filter * -Properties * | Where {$_.CanonicalName -match "Distribution" -and $_.GroupScope -match "Global"}
$list2 |export-csv -Path GlobalEnInDistributionOU.csv -Delimiter ";"

 

 

Powershell

NTFS Security Inheritance Export Script

import-module Ntfssecurity
$lokatie = "\Domain.localdfs"
$header = "Fullname;InheritanceEnabled"
$CSV = "C:file.csv"
add-content -value $header -path $CSV
$list = Get-Childitem $lokatie -recurse | where {$_.psiscontainer -eq $true}
foreach ($Item in $list){
$export = get-inheritance -path $item.fullname
foreach ($object in $export){
$outinfo = $item.fullname + ";" + $object.inheritanceEnabled
add-content -value $outinfo -path $CSV}}

 

—

NTFSSecurity