Solved

Need A Powershell Script

Posted on 2013-01-04
7
394 Views
Last Modified: 2013-01-04
Hello Experts,
I am looking for simple PowerShell script to scan a network directory and pull a list of excel files (using the following naming convention “result_MM-DD_YYYY.csv “ ) and compile the data in to new workbook called “CompileResults_MM-DD-YYYY.csv”.  Each excel file is the output of another script I use to scan Local Admin User Group (from list of servers) and look for changes.  Each file is laid out with the following headings “Computer Name, Administrators, and Status”.  What I am looking for the script to do is scan the particular file directory with the list of excel file (using the following naming convention “result_MM-DD_YYYY.csv “ ) for the past 7-10 days (based on the date indicated in the file name).  Then compile the data on to the new spreadsheet called, “CompileResults_MM-DD-YYYY.csv”, using  date from each source file “result_MM-DD_YYYY.csv “ in the new spreadsheet (example below) to indicate when the person was added or removed from the Local Administrators Group for that server.  Thank you for your help

Example of Source output Excel file called “result_MM-DD_YYYY.csv”
Computer Name      Administrators               Status
ComputerName1      Group1                        Removed
ComputerName1      Domain Admins       Removed
ComputerName1      Backup Admins        Added
ComputerName1      Enterprise Admins       Added
ComputerName2      Enterprise Admins       Removed
ComputerName2      Group2                       Removed
ComputerName2      Domain Admins      Added
ComputerName2      Backup Admins       Added
ComputerName2      Enterprise Admins      Removed
ComputerName2      User1                      Added
ComputerName3      Group2                      Added
ComputerName3      Domain Admins      Added
ComputerName3      Backup Admins       Added
ComputerName4      Enterprise Admins      Removed
ComputerName4      Group0                      Added
ComputerName4      Enterprise Admins      Added



Example of output file called “CompileResults_MM-DD-YYYY.csv” I am looking to do…

Date       Computer Name      Administrators              Status
1/1/2013      ComputerName1      Group1                       Removed
1/1/2013      ComputerName1      Domain Admins      Removed
1/1/2013      ComputerName1      Backup Admins       Added
1/1/2013      ComputerName1      Enterprise Admins      Added
1/1/2013      ComputerName2      Enterprise Admins      Removed
1/1/2013      ComputerName2      Group2                      Removed
1/1/2013      ComputerName2      Domain Admins      Added
1/1/2013      ComputerName2      Backup Admins       Added
1/1/2013      ComputerName2      Enterprise Admins      Removed
1/1/2013      ComputerName2      User6                      Added
1/1/2013      ComputerName3      Group2                      Added
1/1/2013      ComputerName3      Domain Admins      Added
1/1/2013      ComputerName3      Backup Admins       Added
1/1/2013      ComputerName4      Enterprise Admins      Removed
1/1/2013      ComputerName4      Group0                      Added
1/1/2013      ComputerName4      Enterprise Admins      Added
1/2/2013      ComputerName1      Group1                       Removed
1/2/2013      ComputerName1      Domain Admins      Removed
1/2/2013      ComputerName1      Backup Admins       Added
1/2/2013      ComputerName1      Enterprise Admins      Added
1/2/2013      ComputerName2      Enterprise Admins      Removed
1/2/2013      ComputerName2      Group2                      Removed
1/2/2013      ComputerName2      Domain Admins      Added
1/2/2013      ComputerName2      Backup Admins       Added
1/2/2013      ComputerName2      Enterprise Admins      Removed
1/2/2013      ComputerName2      User1                      Added
1/3/2013      ComputerName1      Group2                       Added
1/3/2013      ComputerName1      Domain Admins      Added
1/3/2013      ComputerName1      Backup Admins       Added
1/3/2013      ComputerName2      Enterprise Admins      Removed
1/3/2013      ComputerName2      Group0                      Added
1/3/2013      ComputerName2      Enterprise Admins      Added
result-MM-DD-YYYY.csv
CompileResults-MM-DD-YYYY.csv
0
Comment
Question by:amstoots
  • 5
  • 2
7 Comments
 
LVL 40

Expert Comment

by:Subsun
ID: 38744609
Try this..
[Array]$Result = $null
GCI C:\Users\Pinky\Desktop\Test | % {
$Date = $_.Name -Replace("^result-","") -Replace(".csv$","")
 Import-Csv $_.FullName | % {
		$Result += New-Object Psobject -Property @{`
			Date = $Date
			"Computer Name" = $_."Computer Name"
			Administrators = $_.Administrators
			Status = $_.Status}
 }
}
$Result | Export-Csv “CompileResults_$(Get-Date -f MM-dd-yyyy).csv” -NoTypeInformation

Open in new window


If you are looking for file creation date or modified date then replace
$Date = $_.Name -Replace("^result-","") -Replace(".csv$","")

With $Date = $_.LastWriteTime or $Date = $_.CreationTime based on your requirement..
0
 

Author Comment

by:amstoots
ID: 38745361
The result file (i.e. result_MM-DD_YYYY.csv) is a daily file created from a different script I run that uses the naming convention “result_MM-DD_YYYY.csv”. There is one excel file for each day of the week. (i.e.  result_01-01-2013.csv, result_01-02-2013.csv, result-01-03-2013.csv, result_01-04-2013.csv) in file directory “C:\PS\results\

The CompileResults_MM-DD-YYYY.csv file is an example of what I looking to do. Just simply collect all the data for the past 7 days or the past 7 reports. Referencing the Date in the file name (i.e. result_MM-DD-YYYY.csv) and then plug in the date on the first row for all those users whom has been removed or added that day. (please reference the attached file named “result_MM-DD-YYYY.csv”) Then added on all the other data with date for each file…..


P.S.  My original post Indicated file name formats using Date “result_MM-DD_YYYY.csv”  and should  have been “result_MM-DD-YYYY.csv

I hope not been fudging up with too much info!! If I am, let me know… sorry  :)
CompileResults-MM-DD-YYYY.csv
0
 

Author Comment

by:amstoots
ID: 38745363
OK.. I will give it a shot... thanks...
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:amstoots
ID: 38745381
sorry for posting twice...
0
 

Author Comment

by:amstoots
ID: 38745473
Subsun, the script works perfect... thanks you .... I do have one additional question: when attempting to give a date range ( on the following string "  $Date = $_.LastWriteTime or $Date = $_.CreationTime "  how do I complete the string to look back 7 days or 30 days.. I guess that where a little confused.....



[Array]$Result = $null
GCI "V:\MikesPowershellFolder\Scan" | % {
$Date = $_.LastWriteTime or $Date = $_.CreationTime 
 Import-Csv $_.FullName | % {
		$Result += New-Object Psobject -Property @{`
			Date = $Date
			"ComputerName" = $_."ComputerName"
			"Administrators" = $_."Administrators"
			Status = $_.Status}
 }
}
$Result | Export-Csv “C:\users\amstoots\CompileResults_$(Get-Date -f MM-dd-yyyy).csv” -NoTypeInformation

Open in new window

0
 
LVL 40

Accepted Solution

by:
Subsun earned 500 total points
ID: 38745512
If I am not wrong you need to get .csv files created since last 7 days.. If yes..
Following command will give you last 7 days files based on modified date or created date..
GCI "V:\MikesPowershellFolder\Scan" | Where {$_.LastWriteTime -gt $(Get-Date).AddDays(-7)}
or
GCI "V:\MikesPowershellFolder\Scan" | Where {$_.CreationTime -gt $(Get-Date).AddDays(-7)}

Open in new window

Also in script you have to keep
Either
$Date = $_.LastWriteTime
or
$Date = $_.CreationTime
not both.. :-)
0
 

Author Closing Comment

by:amstoots
ID: 38746173
Subsan, thanks for your help with this script..... It worked w/o any problems..... Again thank you...
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

This article will help you understand what HashTables are and how to use them in PowerShell.
This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

810 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