Wednesday, March 19, 2014

Powershell Script to convert Excel file to CSV




#--------------Script Start-----------------------------

#Convert Excel file to CSV $xlCSV=6
$Excelfilename = "C:\Temp\file.xlsx"
$CSVfilename = "C:\Temp\file.csv"
$Excel = New-Object -comobject Excel.Application
$Excel.Visible = $False
$Excel.displayalerts=$False
$Workbook = $Excel.Workbooks.Open($ExcelFileName)
$Workbook.SaveAs($CSVfilename,$xlCSV)
$Excel.Quit()
If(ps excel){kill -name excel}

#--------------Script End-----------------------------

Wednesday, February 20, 2013

Powershell Script to Send Password expiry notification to users









Hi All,

I am looking for a right script to send a password change notification users whose password is expiring in less than 15 days time. So I Google around found few but not all are that convincing.

Hope somebody can use below code.

[code]#********************************************************************************* #
# #
# PowerShell script to gather Password expiry date for all users and --- #
# send e-mail notification to user with Password expiry date less than 15 Days #
# #
# Created by Vipin Vasudevan #
# #
#********************************************************************************* #


# Set the target OU that will be searched for user accounts
param ($OUDNPath = "CN=USERSDC=DOMAIN,DC=COM")

              $objuser= $null
$objou= [ADSI]"LDAP://$OUDNPATH"
                $searchad= [System.DirectoryServices.DirectorySearcher]$objou

# LDAP Filter for searching user account
                $searchad.Filter ="(&(objectcategory=person)(objectclass=user)(!useraccountcontrol=514))"
                $searchad.PageSize= 1000
                $searchad.searchscope= "Subtree"

# Fill in the user variables
                [void]$searchad.PropertiesToLoad.Add("samaccountname")
                [void]$searchad.PropertiesToLoad.Add("lastlogontimestamp")
[void]$searchad.PropertiesToLoad.Add("pwdlastset")
[void]$searchad.PropertiesToLoad.Add("name")
[void]$searchad.PropertiesToLoad.Add("mail")
$objusers = $searchad.findall()

# Take each users to calculate las passowrd reset time and Expire date
foreach ($evuser in $objusers)
{
# Calculating the Last password reset date
$lastpwdchng = $($evuser.properties.pwdlastset[0])

# Convert the 'pwdlastset' NT system time into a readable format
        $lastpwdchng = [datetime]::fromfiletime($lastpwdchng)

# Enumerate "Name, Mail and Samaccountname" attribute value for each users
        $name = $($evuser.properties.name)
        $mail = $($evuser.properties.mail)
        $sam = $($evuser.properties.samaccountname)

# Convert SAMaccountName to UserprincipalName
        $upn = $sam + "@DOMAIN.com"

# Define the default password age ( maximumPasswordAge ) and get current date to '$today'
        $policy = 45
        $today = Get-Date

# Identify the current password age to '$daystoexpiry'
        $daystoexpiry = ($today - $lastpwdchng).Days

# Identify the days remaining to expire password checking password age with default password age.
        $balance = $policy - $daystoexpiry

# Identify Date and time passsword is going to expire by adding 45 days to Last password set date.
        $expireyday= $lastpwdchng.Adddays(45)

# Check users that have a password expiring in 15 days or less      
        If ($balance -lt 15 -and $balance -gt 0)
            {
           # Write-Host $name with $balance days to resset his password mail need to send to $mail

# E-mail structure with message body



                        $WarnMsg = "


                                    <p><u><span style='font-size:10.0pt;font-family:Century Gothic'>This is an automatically generated message from the Active Directory System</span></u> </p>

                                    <p style='font-family:Century Gothic'>Dear $name,</p>
                                   
                                    <p><span style='font-size:10.0pt;font-family:Century Gothic'>This is to inform you, that your AD account ( $upn ) password is due to expire <span class=GramE>on $expireyday  IST</span>.  (<%pwdExpiryDate+pst%>) <br> If you are directly logging to the <b><span style='color:red'>DOMAIN.com</span></b> Windows active directory Network, please change the password using built-in windows feature<br> (Use ctrl-alt-del and select change password). <br> If not please go to the site <a href='https://portal.DOMAIN.com'>https://portal.DOMAIN.com</a> use your existing credentials to login and change your password. <br>  </span></p>


                                    <p><span style='font-size:10.0pt;font-family:Century Gothic'>If you have problems changing your AD account password please raise a ticket by contacting <span class=SpellE>ISHelpDesk</span>  at <br> From US       :  + 1-949-600-5511 <br> From India  :  0471–4041111/ 0471 4041112 <br> Email:<span style='color:blue'> <a href='mailto:ISHelpdesk@DOMAIN.com'>ISHelpdesk@DOMAIN.com</a></span> </span></p>


                                    <p><span style='font-size:10.0pt;font-family:Century Gothic'>Your Password was last changed on $lastpwdchng IST.  (<%pwdLastSet+pst%>)</span></p>

                                    <p><span style='font-size:10.0pt;font-family:Century Gothic'>Your AD credentials are used to access all corporate services and an account lock out will lead to loss of productive time.</span></strong> </p>

<p class=MsoNormal><u><span style='font-size:10.0pt;font-family:Century Gothic'>Your password should meet the following conditions.</span></u></p>

<ul type=disc>

 <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;
     mso-list:l1 level1 lfo3;tab-stops:list .5in'><span style='font-size:10.0pt;
     font-family:Century Gothic'>Your password must contain a minimum of 8 characters</span></li>
 <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;
     mso-list:l1 level1 lfo3;tab-stops:list .5in'><span style='font-size:10.0pt;
     font-family:Century Gothic'>Password must be a combination of upper and lower case characters, numerals (0 - 9) and non-alphabetic characters (!,$,%,&,...)</span></li>
 <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;
     mso-list:l1 level1 lfo3;tab-stops:list .5in'><span style='font-size:10.0pt;
     font-family:Century Gothic'>Your password must not contain part of the account or your proper name</span></li>
 <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;
     mso-list:l1 level1 lfo3;tab-stops:list .5in'><span style='font-size:10.0pt;
     font-family:Century Gothic'>The system will not accept your previous 24 passwords</span></li>
<li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;
     mso-list:l1 level1 lfo3;tab-stops:list .5in'><span style='font-size:10.0pt;
     font-family:Century Gothic'>Please change your password every 45 days. The system will prompt you to do so</span></li>
<li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;
     mso-list:l1 level1 lfo3;tab-stops:list .5in'><span style='font-size:10.0pt;
     font-family:Century Gothic'>Warning: Your account will be locked if 5 attempts to change passwords fail due to invalid entries. Please contact Helpdesk [Phone: 1111/1112 (VOIP) or 0471-4041112 (India PSTN) or 9496005511 (US Users)] for assistance</span></li>


</ul>

<p><span style='font-size:10.0pt;font-family:Century Gothic'>Thank you, </span></p>

<p><span style='font-size:10.0pt;font-family:Century Gothic'>Active Directory Support Team </span></p>

<p><b><span style='font-size:9.0pt;font-family:Century Gothic'>Confidentiality Notice:</span></b><span style='font-size:10.0pt;font-family:Century Gothic; color:navy'><br>  <br>
</span><span style='font-size:10.0pt;font-family:Century Gothic'>This message and any attachment(s) contained here are information that is confidential, proprietary to <span class=SpellE><b><i><span style='color:#FF3300'>U</span><span style='color:red'>Organization</span></i></b></span> and its customers. Contents may be privileged or otherwise protected by law. The information is solely intended for the individual or the entity it is addressed to. If you are not the intended recipient of this message, you are not authorized to read, forward, print, retain, copy or disseminate this message or any part of it. If you have received this e-mail in error, please notify the sender immediately by return e-mail and delete it from your computer. <o:p></o:p></span></p>

                                    "


                        $recipients1="ADTeam@DOMAIN.com"

# Send Email to user that password is going to expire
                      send-mailmessage -from ISHelpdesk@DOMAIN.com -to $mail -Bcc $recipients1 -subject "Change Password" -body $WarnMsg -smtpServer <IPADDRESS> -BodyAsHtml

                      }
       
       
        }
[/code]

For more scripts and solutions Please visit http://vipinvasudevan.blogspot.in/

Above script you may find here is ready to use, BUT RUN IT ON YOUR OWN RISK!!!!







Tuesday, February 19, 2013

Change groups scope using script

Below one liner help to change the group scope of all groups in a particular container to "Universal', please do change accordingly


Change Group scope of all Groups..
=========================================================
Import-Module ActiveDirectory

Get-ADGroup -SearchBase "SerachBase DN" -filter {GroupScope -eq "Global"} | Set-ADGroup -GroupScope Universal

Change filter to seach for groups with specific requirment to seach across domain 

for example  "" -Filter {Name -like "*DG"}""

Disable/Enable replication using Repadmin


Disable replication

repadmin /options DCNAME +DISABLE_OUTBOUND_REPL

repadmin /options DCNAME +DISABLE_INBOUND_REPL

enable replication

repadmin /options DCNAME-DISABLE_OUTBOUND_REPL

repadmin /options DCNAME -DISABLE_INBOUND_REPL

Upgrade SHA 1 to SHA 256


Below is the command to change the Hash algorithm from SHA 1 to SHA 256

SHA-1 to SHA256

certutil -setreg ca\csp\CNGHashAlgorithm SHA256

LDAP Error Code : Application

Here is few general LDAP errors found on few Java/PeopleSoft etc application with reason


General LDAP Error

52e: invalid credentials; user name or password is incorrect.
525: User not found.
530: User is not permitted to logon at this time.
532: Password has expired.
533: User account is disabled.
701: User account has expired.
773: User must reset password. 

Powershell script to request and export Certificates with Private Key (PFX)

At last I have made my mind to share MY IT experience through my blogs.

I have started blogging 4 years back and idle for some time.. Now the time to start share my experience with this world... :)

Here you may find simple (may be every one knows it) self-opinionated (You may found this on other source too) sometime complex solutions for Technical issues as an IT engineer.

All solutions and script you may find here is ready to use, BUT RUN IT ON YOUR OWN RISK!!!!

I WANT TO START WITH MY LATEST SCRIPT ON USING Powershel script to do an offline request and download certificate (computer) with private key.

Situation is, Our organization decided to implement a solution to implement certificate level authentication for a system used in-house  We have had almost around 2000+ roaming laptop users who never connect to intranet but need certificate to be downloaded. We have no option left but to create an offline request for them on behalf and send across to them for installing it.

Here is the solution I found to request for computer certificate using list of computers in text (Compt.txt)
----------------------------- SCRPT START HERE --------------------------------------------------
$import= "C:\Users\USER\Desktop\Comp.txt"
get-content $import |foreach{
$comp= $_
$comp1 ="CN="+$comp+".DOMAIN.com"
$d = '"USTComputers"'
$b = "[NewRequest]
Subject=$comp1
KeySpec=1
KeyUsage=0xf0
MachineKeySet=TRUE
Exportable=TRUE
[RequestAttributes]
CertificateTemplate=$d
"

$path = "C:\Users\USER\Desktop\"+$comp+".inf"
$pat = "C:\Users\USER\Desktop\"+$comp+"_.inf"
$path1 = "C:\Users\USER\Desktop\"+$comp+".req"
$path2 = "C:\Users\USER\Desktop\"+$comp+".cer"
$path3 = "C:\Users\USER\Desktop\"+$comp+".log"
$cername = "Cert"+$comp+".cer"
clear-content $path -force
add-content $pat $b
$aa = get-content $pat
$bb = $aa -replace 'CN','"CN' 
$cc = $bb -replace 'DOMAIN.com','DOMAIN.com"'
clear-content $pat -force
add-content $path $cc
certreq -new $path $path1
certreq -submit -config “CASERVER\CANAME” $path1 $path2 |out-file $path3

$certid = Get-content $path3 |Select-String -Pattern 'Requestid: "' 
$certid = $certid -replace 'Requestid: "' -replace '"'

certreq -retrieve -f -config "CASERVER.DOMAIN.com\CANAME" $certid $cername

certreq –accept $cername
}
----------------------------- SCRPT END HERE --------------------------------------------------

This will download all required certificate in to requester computer store, Now we need to export these certificate with private key (in PFX format) and share it with external user
----------------------------- SCRPT START HERE --------------------------------------------------
cd cert:
cd localmachine
cd my
Get-ChildItem |select Thumbprint,Subject | Export-Csv C:\temp\certificate.csv -NoTypeInformation
$import= import-csv "C:\temp\certificate.csv"
foreach($line in $import)
{
$thumb = $line.Thumbprint
$sub = $line.Subject
$subj = $sub -replace ".domain.com"
$subje = $subj -replace "CN="
$FilePath = "C:\temp\"+$subje+".pfx"

certutil -exportPFX -p "Password" my $thumb $FilePath
}
----------------------------- SCRPT END HERE --------------------------------------------------

Copy and paste both script in notepad and save as 'Filename.PS1' and execute on need basis. Make sure that you have set your execution policy to execute this Script.

Friday, April 25, 2008

Are facing VSS crunch in NTBackup after SP2 upgradation in Windows Server 2003?

NTBackup is hanging in “Preparing to backup using shadow copy”

This is a type of problem occurring facing after the Service Pack Upgradation in Windows Server 2003.

SYMPTOMS

On a Microsoft Windows Server 2003-based computer that is running the Volume Shadow Copy Service (VSS), you experience one of the following symptoms: When you perform a backup operation by using the NTBackup tool, will hang in “Preparing to backup using Shadow copy” and the following event is logged in the Application log:

Event Type: Error
Event Source: NTBackup
Event ID: 8019
Description: End Operation: Warnings or errors were encountered. Consult the backup report for more details.



1. Note If you view the backup log file, the following information is displayed:

Error returned while creating the volume shadow copy: 0xffffffff

2. If you access the properties of a volume and then click Shadow Copies, you receive one of the following error messages:

Error 0x8004230F: The shadow copy provider had an unexpected error

3. While trying to process the specified operation you will receive following:

Error 0x80004002: No such interface supported



CAUSE

This issue occurs because the VSS system files are not registered.
Back to the top

RESOLUTION

To resolve this issue, follow these

Step 1.

Click Start > click Run > type regedit.exe and then click OK
In registry editor check the content in
HKEY_L _M/System/Current control set/Control/Backupandrestore/Nototake backup
Delete unwanted junk entries included in the given path

Steps 2.

Click Start > click Run, type cmd, and then click OK.

Step 3.

In Command prompt go to the path %windir%\system32
Then type the following commands at a command prompt. Press ENTER

• Net stop vss
• Net stop swprv
• regsvr32 ole32.dll
• regsvr32 oleaut32.dll
• regsvr32 vss_ps.dll
• vssvc /register
• regsvr32 /i swprv.dll
• regsvr32 /i eventcls.dll
• regsvr32 es.dll
• regsvr32 stdprov.dll
• regsvr32 vssui.dll
• regsvr32 msxml.dll
• regsvr32 msxml3.dll
• regsvr32 msxml4.dll (This command may not run successfully).

Step 4.

Perform a backup operation to verify that the issue is resolved.

How you will expertise in Windows Server 2003?

I would like to share sometechnical documnts with you all,

1.  Exporting and Importing of Active Directory Object

2.  Troubleshooting Group Policy in Microsoft® Windows® Server

3.  ACTIVE DIRECTORY – DNS – FSMO – GROUP POLICY_interview

4.  FSMO- Flexible Single Master Operations_interview