asked on

Need A Powershell Script

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

SubSun

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

Mike

ASKER

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

Mike

ASKER

OK.. I will give it a shot... thanks...

Mike

ASKER

sorry for posting twice...

Mike

ASKER

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

ASKER CERTIFIED SOLUTION

SubSun

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike

ASKER

Subsan, thanks for your help with this script..... It worked w/o any problems..... Again thank you...