Solved

Need A Powershell Script

Posted on 2013-01-04
7
393 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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

This is a PowerShell web interface I use to manage some task as a network administrator. Clicking an action button on the left frame will display a form in the middle frame to input some data in textboxes, process this data in PowerShell and display…
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now