Solved

need from your expertise to build a powershell script

Posted on 2014-02-25
13
332 Views
Last Modified: 2014-03-13
Hello Experts


I have been given a task from the finance department to help them to chargeback for SASAPP users and write a PowerShell script to automate and alleviate the need for someone to do manual reporting each month.  

So here’s the challenge:

Write a PowerShell script that will produce a csv file that contains unique SASAPP Users, the columns in the csv should be username, firstname, lastname, departmentnumber.

The csv should be timestamped somehow with when it was created so that Finance can do an audit at a later date.

People are granted access to SASAPP using two Security Groups, RDS_SAS and RDS_SAS2 so if they are in one of those Security Groups, Finance will charge them.

The departmentNumber AD User attribute actually contains a cost centre code that Finance will use for the chargeback.

Can someone help me to write this powershell script and test?

Your feedback is really appreciated
0
Comment
Question by:Jerry Seinfield
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
13 Comments
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 39888533
as i understand u need a script to list users in a AD security-group with desired fields..

Is that true?
0
 

Author Comment

by:Jerry Seinfield
ID: 39888728
The script is supposed to produce a csv file with only users from a specific application. FYI SASAPP is a third party application that we used for telephony and chargeback based on some criteria. The SASAPP users are contained on the two distribution groups below explained

Please see details below

Write a PowerShell script that will produce a csv file that contains unique SASAPP Users, the columns in the csv should be username, firstname, lastname, departmentnumber.

The csv should be timestamped somehow with when it was created so that Finance can do an audit at a later date.

People are granted access to SASAPP using two Security Groups, RDS_SAS and RDS_SAS2 so if they are in one of those Security Groups, Finance will charge them.

The departmentNumber AD User attribute actually contains a cost centre code that Finance will use for the chargeback.
0
 

Author Comment

by:Jerry Seinfield
ID: 39890004
Any updates?

Subsun, can you please provide your input here?
0
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

Author Comment

by:Jerry Seinfield
ID: 39890697
Can I please get an update?
0
 

Author Comment

by:Jerry Seinfield
ID: 39892509
Good Afternoon all PowerShell Experts and Team,

Can anyone please pick up this case and provide some sort of support here?

Your feedback is really appreciated

Looking forward hearing from you
0
 
LVL 14

Expert Comment

by:Justin Yeung
ID: 39896657
would you please be a little more clear about what you need?

you mentioned about a timestamp csv file but what timestamp do you need?

the user access the application? or the user logon to their computer?
0
 

Author Comment

by:Jerry Seinfield
ID: 39896685
Hi Justin,

There is a third party application called SAS that is integrated with AD.  People are granted access to this application using two Security Groups, RDS_SAS and RDS_SAS2 so if they are in one of those Security Groups in AD, they will access the app.

Each user connect to their computer using a domain account and access the application via web. The only users that have access to this application are the ones who are member of the security groups explained above

Every month the finance department has to create a manual report  with some parameters such as username, firstname, lastname, departmentnumber information that is pulled out from AD. Each domain user in our AD has an attribute called The departmentNumber  which actually contains a cost centre and this code cost centre will used by finance to chargeback users.

Request

The challenge is  to write a PowerShell script that will produce a csv file with only users from the application[users members of the 2 security groups above]

The columns in the csv should be username, firstname, lastname, departmentnumber

The csv should be timestamped somehow with when it was created so that Finance can do an audit at a later date. Perhaps or correct me if i wrong create the file name  with the actual format datetime when it was created, for example SASReport2282014 and include a column inside the csv file with the time it was created, or maybe something else

Please see the notes below

People are granted access to SAS using two Security Groups, RDS_SAS and RDS_SAS2 so if they are in one of those Security Groups, Finance will charge them.

The departmentNumber AD User attribute actually contains a cost centre code that Finance will use for the chargeback

I hope you have a better understanding of the request

SO, basically we need a script to list users in a AD security-groups with desired fields, create a csv based on that information, and validate the csv is timestamped somehow and export the information on this csv with those fields
0
 
LVL 14

Expert Comment

by:Justin Yeung
ID: 39896712
import-module activedirectory
$groups = get-adgroup | ? {$_.Name -eq "RDS_SAS" -or $_.name "RDS_SAS2"}
foreach {$group in $groups}
{
$date = (get-date).tostring("MMddyyyy")
$path = "\\path\path\SASReport" + $date + ".txt"
get-adgroupmember $group.name -properties * | ft Name,Givename,Surname, departmentnumber | out-file $path -append
}


this should work for what you need.
0
 

Author Comment

by:Jerry Seinfield
ID: 39897213
Thanks Justin,

what line of your code creates the csv based on that information?

Where the csv file is timestamped and the information on the csv is exported to a file?
0
 
LVL 14

Expert Comment

by:Justin Yeung
ID: 39900504
$path = "\\path\path\SASReport" + $date + ".txt"


it is a txt file since it needs to be appended.

on $date = (get-date).tostring("MMddyyyy"), which means $date = MMddyyyy

$path will equal "\\path\path\SASReportMMddyyyy.txt"
0
 

Author Comment

by:Jerry Seinfield
ID: 39900680
should i replace path\path with a network location so should I leave it as this?
0
 
LVL 14

Accepted Solution

by:
Justin Yeung earned 500 total points
ID: 39900872
I made a bit changes that makes the out come better and tested it....

import-module activedirectory
$groups = get-adgroup -filter * | ?  {$_.Name -eq "RDS_SAS" -or $_.name "RDS_SAS2"}
foreach ($group in $groups)
{
$Users = get-adgroupmember $group.name
foreach ($user in $users)
{
$Userinfo = Get-ADUser -id $user.SamAccountName -Properties *

$array = @()
$Properties = @{Name=$Userinfo.Name;LastName=$Userinfo.surname;FirstName=$Userinfo.givenname;"Department Number"=$Userinfo.departmentnumber;"Group Name"=$group.name}

$Newobject = New-Object  PSObject -Property  $Properties

$Array +=$Newobject

 

$date = (get-date).tostring("MMddyyyy")
$path = "your path" + $date + ".csv"

$array | Select-Object Name,LastName,FirstName,@{Name="Department Number";Expression={$userinfo.departmentNumber}},"Group Name" | export-csv $path -Append

}
}


note: it requires powershell 3.0 to support -append under export-csv
0
 

Author Comment

by:Jerry Seinfield
ID: 39910352
Thanks Justin

Allow me some time to do full testing and will get back to you
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
Windows 10 came with  a lot of built in applications, Some organisations leave them there, some will control them using GPO's. This Article is useful for those who do not want to have any applications in their image (example:me).
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question