Help Newbie Powershell Scripter here.

[tekpr00]

Hello All,
I have a csv/excell file like so:
Server Database Users
xxxxxx\yyyyyyy zzzzz $ISSAdxxxxxL
xxxxxx\yyyyyyy zzzzz $ITIllllllllllallllll
xxxxxx\yyyyyyy zzzzz svckaldfasdf
xxxxxx\yyyyyyy zzzzz svcSQLSupportSrvr
xxxxxx\yyyyyyy zzzzz us438478

I am writing a power script to help send an email to the manager of each line in the user column to confirm if the user still need an access. Below is my attempt.

##Required for AD Support
Import-Module activedirectory

# List email recipients who should receive email
$InraDba = "dba@organization.com"

#Application & file paths
$ApplicationPath = "C:\script\databaseUsers\"
$RemediationCSV = $ApplicationPath + "SQL2000_Remediation.csv"
$ManagerCSV = $ApplicationPath + "ManagersResult.csv"
$UserCSV = $ApplicationPath + "UsersResult.csv"


### Deletes previous file
Remove-Item $UserCSV

import-csv $RemediationCSV | foreach {
$ServerName = $_.Server
$DatabaseName = $_.Database
$UserCSV = $_.Users


if ($_.Users -like "us.*" or "ew.*" or "os.*" or "sp.*"){ # This is a single user account
### look in the LDAP for user's manager
This part I am not sure how to go about this. Please help.
### Create the mail message and add the $Results.csv text file as an attachment###

Send-MailMessage –From $InraDba –To $ManagerCSV –Subject "Confirm user access to Database" –Body "Below is the list of users who have access to the database $DatabaseName. As the manager, please confirm that users still need access to the datbase, else access will be revoked in 7 days." -Attachment $UserCSV –SmtpServer
}
else ### This is a service account, look in the database inventory for the owner of the database.
Not sure how to go about this neither, but that can come later.
Send-MailMessage –From $InraDba –To $OwnerCSV –Subject "Confirm user access to Database" –Body "Below is the list of service account that have access to the database $. As the owner, please confirm that thise service account still need access to the datbase, else access will be revoked in 7 days." -Attachment $ResultsCSV –SmtpServer some.servers.com
}


Please help anyway you can to make this script work.
Thanks in advance.

 

[blister911]

tekpr00,

I'm still pretty new to PowerShell scripting, myself. But since nobody has assisted you so far I'll see if I can help.

A couple of thoughts for you.

1. I don't think your "if" statement is correct.

if ($_.Users -like "us.*" or "ew.*" or "os.*" or "sp.*")

I could be wrong, but I think if you're doing multiple compares they have to be separate statements like this:

 if (($_.Users -like "us.*") -OR ($_.Users -like "ew.*") -OR ($_.Users -like "os.*") -OR ($_.Users -like "sp.*"))

I believe there is a way to use match and a regular expression to compare search criteria to the username. I would have to search for it, but it would be something like this:

if($_.Users -match ("us.|ew.|os.|sp."))

2. You're missing the {} in your else statement

} #this is the end of the if statement
else ### This is a service account, look in the database inventory for the owner of the database.
[COLOR=#EF2929][b]{[/b][/color]Send-MailMessage –From $InraDba –To $OwnerCSV –Subject "Confirm user access to Database" –Body "Below is the list of service account that have access to the database $. As the owner, please confirm that this service account still needs access to the database, else access will be revoked in 7 days." -Attachment $ResultsCSV –SmtpServer some.servers.com[COLOR=#EF2929][b]}[/b][/color]
}#this is the end of the foreach loop

3. From the message you're providing in the body of the email it looks like you want to send 1 message containing all the user names associated with a particular database on a particular server. However, the send mail is inside your foreach loop, which means it will send a message for every line in the $RemediationCSV file. If you want to send one message per database, would need to save the data (servername, databasename, usernames) in a multi-dimensional array. Then after you've run through the csv-file run through another loop that reads the array and sends out the message.

4. You're assigning $UserCSV twice, differently

$UserCSV = $ApplicationPath + "UsersResult.csv"
$UserCSV = $_.Users

5. How are you connecting all these csv files, together? Is there a relationship between them?

Without this information and examples of the rest of the csv files, it will be extremely difficult for someone to assist you. And to make it easier for everyone to understand, don't use generic symbols; make up data if you're concerned about posting the real stuff. So instead of xxxxx\yyyyyy zzzzzz you could use MyServer1\DBInstance1 TestDB or whatever it stands for.

blister911


Light travels faster than sound. That's why some people appear bright until you hear them speak.