Add\Import multiple CSV to excel

# Now get a list of all csv files in current directory :)
$targetcsv=dir *.csv

#Create a new Excel object and add a new workbook. 
$Excel = New-Object -ComObject excel.application 
$Excel.visible = $true
$workbooks = $excel.Workbooks.Add()
$worksheets = $workbooks.worksheets
#Delete the extra worksheets and rename the first worksheet.
$worksheets.Item(3).delete()
$worksheets.Item(2).delete()
#Add worksheets based on the count of files
$count=1

foreach ( $CSVFile in $targetcsv ){
        IF ($count -ne 1){ $worksheets.Add()}
        #Write-Host $CSVFile.BaseName
        #Select worksheet 
        $worksheet = $worksheets.Item(1)
        #Give it a updated name
        $worksheet.Name = $CSVFile.BaseName
 
        #Grab the CSV
        $TxtConnector = ("TEXT;" + $CSVfile.fullname)
        $CellRef = $worksheet.Range("A1")
 
        #Import the text file
        $Connector = $worksheet.QueryTables.add($TxtConnector,$CellRef)
        $worksheet.QueryTables.item($Connector.name).TextFileCommaDelimiter = $True
        $worksheet.QueryTables.item($Connector.name).TextFileParseType  = 1
        $worksheet.QueryTables.item($Connector.name).Refresh()
        $worksheet.QueryTables.item($Connector.name).delete()
        
        #make pretty
        $worksheet.UsedRange.EntireColumn.AutoFit()
        #loop for fun!
        $count=$count+1        
        write-host $count

        }

Windows 2012 data dedupe antivirus exclusions

Since I couldn't find any on the interwebs, some monitoring lead to.

Process - fsdmhost.exe
Folder - <drive>\System Volume Information\Dedup\*

/NM

Backup Folder Security to CSV with Powershell

thanks to whoever I stole the rescurse depth limit from :)

# Get the folder security and save it to csv

# -------------------------

$Date= get-date -Format yyyyMMdd

#Group path already includes two '\' so add 2 to folder level required

$Depth=3

# Obtain the files

$Rfolders=Get-ChildItem E:\group -recurse -Attributes Directory | % {$_.FullName.ToString()} | foreach {$var=$_;$count=(0..($_.length - 1) | where {$var[$_] -eq "\"}).count;if($count -le $Depth) {$_}}

# Obtain the folder security information and log to file

$LogFile = 'E:\group\GroupSecurityBackup_'+$date+ '.log'

$Rfolders | Get-Acl | Export-Csv $LogFile -Force

# Restoring individual folder

#--------------------------

# 1st- Import Acl back

 $ResFolder = import-csv E:\group\GroupSecurityBackup_<date>.log

#

# 2nd- Check acl for a specific folder

$Resfolder |Get-Acl | where {$_.path -like "*E:\group\test1\test2"}

#

# 3rd- To restore acl for a specific folder (this example we are exporting to another folder)

$acl = get-acl E:\group\testme

$acl.SetSecurityDescriptorSddlForm(($Resfolder |Get-Acl | where {$_.path -like "*E:\group\test1\test2"}).sddl)

set-acl E:\group\testme $acl

# Restoring Complete Tree Rebuld and ReSecure

# ---------------------------

$ResFolder = import-csv E:\group\GroupSecurityBackup_<date>.log

 foreach ($folder in $ResFolder) {

   write-host $folder.Path

   mkdir $folder.Path

   $acl = get-acl $folder.Path

   $acl.SetSecurityDescriptorSddlForm($folder.Sddl)

   set-acl $folder.Path $acl

   } 

powershell test file date time

$testfile="d:\temp\testflie_"+(Get-Date -Format yyyyMMdd-HHmm).ToString()+".log"

result
d:\temp\testflie_20130506-1735.log

How to Create Custom Active Directory LDAP Searches

Cool stuff from
http://blogs.msdn.com/b/muaddib/archive/2011/10/24/active-directory-ldap-searches.aspx

Also see the post below on creating queries for individual UserAccountControl flags.

How to use the UserAccountControl flags to manipulate user account properties
http://support.microsoft.com/kb/305144 

http://blogs.msdn.com/b/muaddib/archive/2008/10/08/query-individual-properties-of-the-useraccountcontrol-active-directory-user-property.aspx

Now on to the queries.

 All XP ComputersAlthough this can be done easy enough with the GUI, I wanted to show the syntax so it can be used as a building block for more complex theories.  One thing to notice is the query parameter "objectCategory=computer".  By including this as part of our query we reduce the number of objects that have to be searched making for a faster query and less performance impact on the DC performing the query.
(&(objectCategory=computer)(operatingSystem=Windows XP*))

Windows XP Computers with Service Pack 2 Installed(&(objectCategory=computer)(operatingSystem=Windows XP Professional)(operatingSystemServicePack=Service Pack 2))

Windows XP Computers with Service Pack 1 Installed
(&(operatingSystem=Windows XP*l)(operatingSystemServicePack=Service Pack 1)))

Windows XP Computers with No Service Pack Installed
This one is structured a Little different.  Notice the "!" before operating SystemServicePack and the "*".  The "!" means NOT so the statement reads "NOT equal to anything" instead of NULL or empty quotes ("") like some other languages.
(&(operatingSystem=Windows XP Professional)(!operatingSystemServicePack=*))) 

Windows Server 2003 No Service Pack 1(&((objectCategory=computer))(operatingSystem=Windows Server 2003)(!operatingSystemServicePack=*)))

Windows Server 2003 Service Pack 1 Installed (&(objectCategory=computer)(operatingSystem=Windows Server 2003)(operatingSystemServicePack=Service Pack 1)) 

Windows 2000 Professional (&(objectCategory=computer)(operatingSystem=Windows 2000 Professional))

Windows 2000 Server (&(objectCategory=computer)(operatingSystem=Windows 2000 Server))

All Windows Server 2003 Servers
(&((objectCategory=computer))(operatingSystem=Windows Server 2003))

SQL Servers (running on Windows 2003) (please verify in your environment)
(&(objectCategory=computer)(servicePrincipalName=MSSQLSvc*)(operatingSystem=Windows Server 2003))

SQL Servers any Windows Server OS(&(objectCategory=computer)(servicePrincipalName=MSSQLSvc*)(operatingSystem=Windows Server*))

Windows Vista SP1(&(objectCategory=computer)(operatingSystem=Windows Vista*)(operatingSystemServicePack=Service Pack 1))

Windows Server 2008 Enterprise(&(objectCategory=computer)(operatingSystem=Windows Server® 2008 Enterprise)(operatingSystemServicePack=Service Pack 1))

Windows Server 2008 (all versions)
(&(objectCategory=computer)(operatingSystem=Windows Server® 2008*))

Windows Server 2008 R2 Enterprise
(&(objectCategory=computer)(operatingSystem=Windows Server 2008 R2 Enterprise))

Sample User Attribute Query (ExtensionAtrribute5)
(&(objectCategory=user)(&(extensionAttribute5>=20080101)(extensionAttribute5<=20080520)))

WIndows Server 2008 ALL
(&(objectCategory=computer)(operatingSystem=Windows Server 2008*))

Windows Server 2008 RTM
(&(objectCategory=computer)(operatingSystem=Windows Server 2008 *)(!operatingSystemServicePack=*))

Windows Server 2008 SP1
(&(objectCategory=computer)(operatingSystem=Windows Server 2008*)(operatingSystemServicePack=Service Pack 1))

Windows 7 RTM(&(objectCategory=computer)(operatingSystem=Windows 7*)(!operatingSystemServicePack=Service Pack 1))

Windows 7 SP1(&(objectCategory=computer)(operatingSystem=Windows 7*)(operatingSystemServicePack=Service Pack 1))

Troubleshooting Server unable to look up internal FDQN "result too large", unable to RDP to server, but can browse and read eventlogs

 nslookup internaldomain.local

 Troubleshooting Server unable to look up internal FDQN "result too large", unable to RDP to server, but can browse and read eventlogs

Quick answer: port exhaustion, kb out there to fix this stuff.

nslookup

 internalserver.internaldomain.local can't find internaldomain.local: unspecified error

1. clear dns servers

2. added dns servers in Core Data centre

3. tried different dns servers in nslookup

3a. tried local dns server

4. modified host file with internal.local to server

fail

Check firewall (disable)

netsh advfirewall set allprofiles state off

result:

ok

Check Group Policy

Gpupdate /force

result:

Updating Policy...User Policy update has completed successfully.Computer policy could not be updated successfully. The following errors were encountered:The processing of Group Policy failed. Windows could not resolve the computer name. This could be caused by one of more of the following: a) Name Resolution failure on the current domain controller. b) Active Directory Replication Latency (an account created on another domain controller has not replicated to the current domain controller).

To diagnose the failure, review the event log or run GPRESULT /H GPReport.html from the command line to access information about Group Policy results.

check time

w32tm /query /peers

more nslookup

set debug

internaldomain.local

result

-------------

truncated answer

connect failed: result too large

-------------

check server's services

net stop dnscache

net start dnscache

net stop workstation

net start netlogon

Check Ports!

netstat -a -n

problem located - port exhaustion !!! 10000's of time_wait

----Hotfix Time---- (with bonus hotfix)

Windows6.1-KB2553549-v3-x64

Windows6.1-KB2264080-x64

there is a better rollup available(windows6.1-kb2775511-x64) but my server is not SP1. 

enjoy.

Change CD DVD drive letter RAGE - task sequence

FOR /F "tokens=1 delims=:" %%A IN ('WMIC logicaldisk get drivetype^,deviceID^| FIND "5"') DO (
echo select volume %%A >%temp%\movecdrom.txt
echo assign letter z noerr >>%temp%\movecdrom.txt
diskpart /s %temp%\movecdrom.txt
)

uses wmic to locate which drive letter is actually the cdrom\dvd then moves it to Z drive.