Last 3 part of
migration package - reporting. This
script same use report from post "Windows XP software script".
Program checked txt files by old data in report folder and created xlsx
report*.
* - Format xlsx file:
1 column - ID
(%your_name%)
2 column - old
hostname (from Windows XP)
3 column - new
hostname (from Windows 7)
4 column - old MAC
(from Windows XP)
5 column - new MAC
(from Windows 7)
6 column - audit
Data (Windows XP)
7 column - refresh
Data (Windows 7)
#$audit = 1
############## Do we need AUDIT ???
$drive = [System.IO.Path]::GetDirectoryName($myInvocation.MyCommand.Definition).ToString()
$location = $drive[0]+":\Backup\DC30"
$DC_Report = Get-ChildItem $location | select BaseName, FullName, CreationTime
$report_dat = @()
for ($i
= 0; $i -ne $DC_Report.Count;
$i++)
{
$MainFile
= new-object
System.IO.StreamReader($location+"\"+$DC_Report.BaseName[$i]+".txt")
$t = 0
try {
while
(($line =
$MainFile.ReadLine())
-ne $null)
{
$report_data
= New-Object
System.Object
switch
($t)
{
0
{$tem =
$line;$t++}
4 {
$report_data
| Add-Member
-MemberType NoteProperty
-name "ID"
-Value $DC_Report[$i].BaseName.Substring(3).Split("_")[1]
$report_data
| Add-Member
-MemberType NoteProperty
-name "Status"
-Value $DC_Report[$i].BaseName.Substring(3).Split("_")[0]
$report_data
| Add-Member
-MemberType NoteProperty
-name "Hostname"
-Value "$tem"
$report_data
| Add-Member
-MemberType NoteProperty
-name "Mac"
-Value $line.Substring(0,17)
$report_data
| Add-Member
-MemberType NoteProperty
-name "Data"
-Value $DC_Report.CreationTime[$i].ToString("dd-MMM")
$t++
$report_dat
+= $report_data
}
default
{$t++}
}
}
}
finally
{ $MainFile.Close()
}
#for mac '^([0-9A-Fa-f]{2}[:-]){5}([0-9A-Fa-f]{2})$'
}
#Get-ChildItem E:\Backup\DC\sn_new_9.txt | % { $_.CreationTime
= '5/25/2016 15:10:23' }#change creation time
# Create the Excel Object
$xl = New-Object -comobject
Excel.Application
# Make it visible
$xl.Visible
= $true
# Add a workbook
$xl.Workbooks.Add()
# Move to the first row/column
$xl.ActiveSheet.Range("A1").Activate()
# Create the column headers
$xl.Rows.Item(1).Font.Bold = $True
$xl.ActiveCell.Value2 = "ID"
$xl.ActiveCell.Offset(0, 1).Value2 = "old hostname"
$xl.ActiveCell.Offset(0, 2).Value2 = "new hostname"
$xl.ActiveCell.Offset(0, 3).Value2 = "old MAC"
$xl.ActiveCell.Offset(0, 4).Value2 = "new MAC"
$xl.ActiveCell.Offset(0, 5).Value2 = "audit Data"
$xl.ActiveCell.Offset(0, 6).Value2 = "refresh Data"
# Move to the next row
$xl.ActiveCell.Offset(1, 0).Activate()
$rr = $report_dat |
Where-Object {($_.status -eq 'old')}
for ($i
= 0; $i -ne $rr.Count; $i++) {
$old_data
= $rr | Where-Object {($_.status -eq 'old') -and ($_.ID -eq $rr.id[$i])} | Sort-Object 'id' | select 'Hostname', 'Mac', 'Data'
$new_data
= $report_dat
| Where-Object
{($_.status
-eq 'new')
-and ($_.ID -eq $rr.id[$i])} | Sort-Object 'id' | select 'Hostname', 'Mac', 'Data'
if (($new_data -eq
$empty) -and
($audit -eq
$false)) {Write-Host $old_data.Hostname "
-> " $new_data.Hostname " and
" $old_data.Mac " ->
" $new_data.Mac $i} else {
$xl.ActiveCell.Value2
= $rr.id[$i]
$xl.ActiveCell.Offset(0, 1).Value2 = $old_data.Hostname
$xl.ActiveCell.Offset(0, 2).Value2 = $new_data.Hostname
$xl.ActiveCell.Offset(0, 3).Value2 = $old_data.Mac
$xl.ActiveCell.Offset(0, 4).Value2 = $new_data.Mac
$xl.ActiveCell.Offset(0, 5).Value2 = $old_data.Data
$xl.ActiveCell.Offset(0, 6).Value2 = $new_data.Data
Write-Host $_.ID ","$old_data.Hostname","$new_data.Hostname","$old_data.Mac","$new_data.Mac
$xl.ActiveCell.Offset(1, 0).Activate()
}
}
$xl.Cells.EntireColumn.AutoFit()
$xl.activeworkbook.saveas("\\127.0.0.1\c$\Compucom\installed_computers_from_DC30.xlsx")
#Write-Host("C:\Users\$env:UserName\Documents\results_$strUser.xls")
# Close Excel
$xl.Workbooks.Close()
$xl.Quit()
No comments:
Post a Comment