Solved

Need A Powershell Script

Posted on 2013-01-04
7
392 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In this previous article (https://oddytee.wordpress.com/2016/05/05/provision-new-office-365-user-and-mailbox-from-exchange-hybrid-via-powershell/), we made basic license assignments to users in O365. When I say basic, the method is the simplest way …
Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

746 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

13 Experts available now in Live!

Get 1:1 Help Now