Solved

Need to modify a Powershell Script

Posted on 2014-03-05
4
422 Views
Last Modified: 2014-03-24
Greeting Experts,
      I was wondering if would be possible to add on to existing PowerShell script used to modify excel spreadsheet. Currently the script works in two parts……First it deletes list of Summary Information until it reach the cell (in column A) with the following keyword “Event Category”. Once that is done the Script then inserts two columns and then adds the Name of the folder the file located at (i.e. Region 1, Region2, or Region3) in column A. Then plugs the date in to column B in the following format “yyyy_MM_dd” and saves the file.



What I would like to do is change the script from deleting the Summary information with “Number of Threat Events” and “Threat Names” is add the Threat Name to each of the Rows in order.. What do I mean?? Since the Number of “NO# Event Threats Events” Corresponds to number of rows under Each Cell marked “Event Category” (1 row for each no# threat Names that show up ) I want to add the “Threat Name” to each row it corresponds for each event. So there is one event “Threat Name1” under Threat name. That would go next to first row/record under the first “Event Category along with the Region no# and Date ( i.e. Threat Name would be the 3rd column called “Threat Name”)  
There is one event “Threat Name2” for the 2nd “Event Category”… There is 3 events for “Threat Name3” for the 3rd Event Category”…. And so one…..

So basically in a nut shell what I want to do is instead of deleting both “Number of Threat Events” and “Threat Name”. I want to add the “Threat Name” as a 3rd column and add to the number of instances under each row that falls under Each “Event Category” ….. ..



function Release-Ref ($ref)
{
([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}

$objExcel = new-object -comobject excel.application
$xlShiftToRight = -4161
$objExcel.Visible = $true

$Lookupdir="\\ServerName\Region1\"    
$days_backdate=$(Get-Date).AddDays(-1)    
 
$files = Get-ChildItem $Lookupdir -Recurse | where-object {!($_.psiscontainer)}  | where { $_.Creationtime -gt $days_backdate }
foreach ($file in $files)
{
$objWorkbook = $objExcel.Workbooks.Open($file.Fullname)
$objWorksheet = $objWorkbook.Worksheets.Item(1)

$i = 1
$cell=$objWorksheet.Cells.Item($i, 1)

Do {
    If ( $cell.value() -ne "Event Category"  )
      {
            $objRange = $cell.EntireRow
            $objRange.Delete()
            $i-=1
      }
      $i += 1
    $cell=$objWorksheet.Cells.Item($i, 1)
}
While ($cell.value() -ne "Event Category")

$region=$file.FullName.Split("\")[-2]
$date = Get-Date -Format "yyyy_MM_dd"

$objRange = $objExcel.Range("A1").EntireColumn
[void] $objRange.Insert($xlShiftToRight)

$objRange = $objExcel.Range("A1").EntireColumn
[void] $objRange.Insert($xlShiftToRight)


$objWorksheet.Cells.Item(1,1) = "Region"
$objWorksheet.Cells.Item(1,2) = "Date"


$cell=$objWorksheet.Cells.Item($i+1, 3)

While ($cell.value() -ne $null)
 {
            $objWorksheet.Cells.Item($i+1,1)=$Region;
            $objWorksheet.Cells.Item($i+1,2)=$Date;
     $i += 1
    $cell=$objWorksheet.Cells.Item($i+1, 3)
}

$objExcel.displayalerts=$false
$objExcel.activeworkbook.saveas($file.fullname)
$objExcel.Workbooks.Close()
$objExcel.Quit()
}

Release-Ref($objRange)
Release-Ref($objWorksheet)
Release-Ref($objWorkbook)
Release-Ref($objExcel) 

Open in new window

0rginalExclefile.csv
ModifiedExclefile.csv
FinalExclefile.csv
0
Comment
Question by:amstoots
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 11

Expert Comment

by:Joe Klimis
ID: 39912120
Hi

Please try the following.

I have have done it it a slightly different way to produce a csv file. this can then be loaded in to excel if requited.

I also removed the extra headers, i am not sure if you required them

# load files
$Lookupdir="\\ServerName\Region1\"    
$days_backdate=$(Get-Date).AddDays(-1)    
$header="Event Category,Threat Type,Threat Target User Name,Threat Target Host Name,Detecting Product IPv4 Address,Tags,Detecting Product Name,Detecting Product Version,DAT Version,Engine Version,Last Communication,Threat Source IPv4 Address,Threat Target IP Address,Threat Target IPv4 Address,Threat Target File Path,User Name,Assignment Path,Users"
$results =@() #  object for results
$results += "Region,Date,Threat Name,$header"
$today = (get-date).tostring("yyyy_MM_dd")

$files = Get-ChildItem $Lookupdir -Recurse | where-object {!($_.psiscontainer)}  | where { $_.Creationtime -gt $days_backdate }

foreach ($file in $files)
{
$Orig = get-content $file
# find events
$Events = @() # object for events

	foreach ( $line in $ORig)
	{ 
		$lineevent=@()
		$lineevent = "" | Select Event,count 
		# skip heading

		if ($line -eq "Number of Threat Events,Threat Name,,,,,,,,,,,,,,,,") { continue }
		# quit if the end of events
		if ($line -eq $Header) { break }
		$lineEvent.count = $line.split(",")[0]
		$lineEvent.event = $line.split(",")[1]
		$events += $lineEvent
	}
#
# read the rest of the fille
$lineCounter = -1
$eventCounter = -1
	foreach ($line in $Orig)
	{
		$linecounter++
		if ($linecounter -le $events.count )  { continue }
		if ($line -eq $Header) { $eventcounter ++; continue}
		$results += ",$today,$($events[$eventcounter].event),$line"

	}
}

$results | Out-File -Encoding ascii -append -FilePath results.csv

Open in new window

0
 

Author Comment

by:amstoots
ID: 39923621
Thank you for the script you created…. It works just like I need it to…. But just for couple of thing… Is there a way to adjust the script to add the name of the Folder the excel file is located (i.e. Region1, Region2, or Region3). I have 3 different network shares called “Region1, Region2, and Region3” and based on the file name Vender Server pushes those reports out to each of those Folder Locations… Also Instead of dropping the data all in to one excel folders. Is it possible to have it save in each one of the excel files it opens….. ???    


exmaple of spreadsheet
0
 
LVL 11

Accepted Solution

by:
Joe Klimis earned 500 total points
ID: 39923788
Hi
I think this is what you wanted


If this is not quite right just let me not

this will now
 > add the region in the first column on the spreadsheet
 > create a different spreadsheet for each region.

Thanks

Joe


# load files
$Lookupdir="\\ServerName\Region1\"
$region = $lookupdir.split("|")[3]
$days_backdate=$(Get-Date).AddDays(-1)    
$header="Event Category,Threat Type,Threat Target User Name,Threat Target Host Name,Detecting Product IPv4 Address,Tags,Detecting Product Name,Detecting Product Version,DAT Version,Engine Version,Last Communication,Threat Source IPv4 Address,Threat Target IP Address,Threat Target IPv4 Address,Threat Target File Path,User Name,Assignment Path,Users"
$results =@() #  object for results
$results += "Region,Date,Threat Name,$header"
$today = (get-date).tostring("yyyy_MM_dd")

$files = Get-ChildItem $Lookupdir -Recurse | where-object {!($_.psiscontainer)}  | where { $_.Creationtime -gt $days_backdate }

foreach ($file in $files)
{
$Orig = get-content $file
# find events
$Events = @() # object for events

	foreach ( $line in $ORig)
	{ 
		$lineevent=@()
		$lineevent = "" | Select Event,count 
		# skip heading

		if ($line -eq "Number of Threat Events,Threat Name,,,,,,,,,,,,,,,,") { continue }
		# quit if the end of events
		if ($line -eq $Header) { break }
		$lineEvent.count = $line.split(",")[0]
		$lineEvent.event = $line.split(",")[1]
		$events += $lineEvent
	}
#
# read the rest of the fille
$lineCounter = -1
$eventCounter = -1
	foreach ($line in $Orig)
	{
		$linecounter++
		if ($linecounter -le $events.count )  { continue }
		if ($line -eq $Header) { $eventcounter ++; continue}
		$results += "$region,$today,$($events[$eventcounter].event),$line"

	}
}

$results | Out-File -Encoding ascii -append -FilePath "$($region)_results.csv"

Open in new window

0
 

Author Closing Comment

by:amstoots
ID: 39951436
The Script works great, thanks for your help...
0

Featured Post

IoT Devices - Fast, Cheap or Secure…Pick Two

The IoT market is growing at a rapid pace and manufacturers are under pressure to quickly provide new products. Can you be sure that your devices do what they're supposed to do, while still being secure?

Question has a verified solution.

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

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Auditing domain password hashes is a commonly overlooked but critical requirement to ensuring secure passwords practices are followed. Methods exist to extract hashes directly for a live domain however this article describes a process to extract u…
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
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)

622 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