INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Help Newbie Powershell Scripter here.

Help Newbie Powershell Scripter here.

(OP)
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.

RE: Help Newbie Powershell Scripter here.

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.

CODE

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:

CODE

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:

CODE

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

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

CODE

} #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.
{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}
}#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

CODE

$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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close