AboutPricingCommunityTeamsStart Free TrialLog in
Avatar of Mike
Mike
Flag for United States of America 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
PowershellVB ScriptScripting Languages
Avatar of undefined
Last Comment
Mike
8/22/2022 - Mon
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...
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Mike
ASKER
Subsan, thanks for your help with this script..... It worked w/o any problems..... Again thank you...
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent