#######################################################
# Script ReCertification
# *** Double Connexion ***
#
# CMD :
# C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
# -File P:\Test\Powershell\Script\Double Connexion\DoubleConnexion.ps1
###################################
# \\smb15-nas1.par.emea.cib\IRIAPRD
Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Start scanning Emails on Inbox 'ISEC-ISS'..." -ForegroundColor Cyan
# Warning Email
# $Outlook = New-Object -ComObject Outlook.Application
# $Mail = $Outlook.CreateItem(0)
# $Mail.To = "ISEC-ISS-Controls@ca-cib.com" #"ISEC-ISS-Controls@ca-cib.com"
# $Mail.Subject = "Recertification: Processing Emails started..."
# $Mail.Body = "~~~~~~~ \\\\ START //// ~~~~~~~"
# $Mail.Send()
# Date
$date = get-date -format d
$date = $date.ToString().Replace(“/”, “-”)
$time = get-date -format t
# $time = $time.ToString().Replace(":", "-")
$time = $time.ToString().Replace(" ", "")
Write-Host "Date:" $date $time -ForegroundColor Cyan
# Input by User 1 :
# Write-Host "How old are emails (received time)? #0=Today; 1=From yesterday; ..." -BackgroundColor DarkMagenta
# $NumberOfDays = Read-Host "Days"
# Input by User 2 :
# Write-Host "What is the subject of emails are we looking for" -BackgroundColor DarkMagenta
# Write-Host "RE: [POUR ACTION]" -ForegroundColor Cyan
# Write-Host "RE: [RAPPEL] [POUR ACTION]" -ForegroundColor Cyan
# Write-Host "RE: [ACTION REQUIRED]" -ForegroundColor Cyan
$Subject1 = "RE: CACIB Global Remote Connections Report" # Picture 10 AM .xlsm
$Subject2 = "Passage Cacib" # .csv
$Subject3 = "RE: Staffs présents à Levallois" #.xlsx #.xlsm
# # # # #
# Step 1
# Parameter Emails "received time:"
$Date = [DateTime]::Now.AddDays(-0) # $Date = [DateTime]::Now.AddDays(-$NumberOfDays)
$ReceivedDays = $Date.tostring("MM/dd/yyyy")
# Parameter Subject:
# $subjectComparisonExpression = "Message de sécurité pour les utilisateurs du Système d’Information de CACIB / Security message for all CACIB Information System users"
# Connecting to Outlook SharedMailBox
$ol = New-Object -ComObject Outlook.Application
$ns = $ol.GetNamespace('mapi')
$mb = $ns.Stores['baptiste.george-prestataire@ca-cib.com'].GetRootFolder()
$inbox = $mb.Folders['Inbox']
$recipient = $ns.CreateRecipient('ISEC-ISS-Controls@ca-cib.com')
if($recipient.Resolve()){
# Write-Host 'OK'
$shared = $ns.GetSharedDefaultFolder($recipient, [Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)
}else{
Write-Host 'Unable to resolve. Can not connect to sharedMailBox ISEC-ISS!'
}
Write-Host "Query is running" -ForegroundColor Cyan
################################### Filter Emails #######################################
# $messages = $inbox.items
# $messages = $shared.items | Where-Object -FilterScript {$_.senton -Ge "$ReceivedDays" -and $_.Subject -eq $Subject1}
$messages = $shared.items | Where-Object -FilterScript {
$_.senton -Ge "$ReceivedDays" -and ( ($_.Subject.SubString(0,[math]::min($Subject1.length,$_.Subject.length) ) -eq $Subject1) -or
($_.Subject.SubString(0,[math]::min($Subject2.length,$_.Subject.length) ) -eq $Subject2) -or
($_.Subject.SubString(0,[math]::min($Subject3.length,$_.Subject.length) ) -eq $Subject3)
)}
# Query by User result :
# Count Emails into Inbox :
write-host "Query Result:" $messages.count "Emails were found in Inbox 'ISEC-ISS'" -ForegroundColor Green
$messcount = $messages.count
# Init:
$countprocessed = 0
$countprocessed_PJ = 0
# for ($c = $mailboxFolders.Items.count; $c -ge 1;$c--) {
################################### Starting #######################################
foreach($message in $messages){
# $From = $message.SenderEmailAddress
$msubject = $message.subject
Write-Host "Email found:" -BackgroundColor Blue
Write-Host $msubject -BackgroundColor Blue
$mBody = $message.body
# Write-Host $mBody
################################### Save Attachments #######################################
$filepathSource = "\\smb15-nas1.par.emea.cib\ISS_DATA\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Double connexion\SourceFilePM\"
$filepathSourceIRIA = "\\smb15-nas1.par.emea.cib\IRIAPRD\Input\Passage_Cacib"
$Check_PJ = 0
$message.attachments | foreach {
$attr = $_.filename
# add-content $log1 "Attachment: $attr"
$a = $_.filename
If ($a.Contains(".xlsx") -or $a.Contains(".xlsm") -or $a.Contains(".csv")) {
Write-Host $_.filename " --> The file has been saved on 'SourceFilePM'!"-BackgroundColor Green
$_.saveasfile((Join-Path $filepathSource $a))
# IRIA :
If ( $a -eq "Passage_Cacib.csv" ) { $_.saveasfile((Join-Path $filepathSourceIRIA $a))
Write-Host $_.filename " --> The file has been saved on 'Passage_Cacib'!"-BackgroundColor Magenta} # Saving Files for IRIA
$Check_PJ ++
$countprocessed_PJ ++
}
}
# In case you want to resend the file, into a new mail
# $attachment = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Fichiers Retour\" + $a
################################### Moving ALL EMAILS to Folder #######################################
$MoveTarget1 = $shared.Folders.item("19 - Remote Connections Report")
[void]$message.Move($MoveTarget1)
$countprocessed++
} # End
#Synthèse
Write-Host "Result :" -ForegroundColor Cyan
If ( $countprocessed - $countprocessed_PJ -ge 1) {
$count_Empty_emails = $countprocessed - $countprocessed_PJ
Write-Host "-->" $count_Empty_emails "Email(s) do not contain any file!" -ForegroundColor Yellow
}
Write-Host "-->" $countprocessed_PJ "Files have been successfully saved into 'SourceFilePM'" -ForegroundColor Green
write-host "-->" $countprocessed_PJ "Emails have been moved to '19 - Remote Connections Report' Folder" -ForegroundColor Green
Write-Host "End processing Emails from Inbox" -ForegroundColor Cyan
Write-Host "\\\ --- ///" -ForegroundColor Cyan
# Si on a les deux fichiers... on peut lancer la suite :
$CountFilesDest = (Get-ChildItem -Path $filepathSource -File).Count
If ( $CountFilesDest -ge 2 ) {} else {
Write-Host "Looks like at least one file is missing !" -ForegroundColor Red
Break}
# ? # ?
# From Home, On doit copier le fichier RCP (updaté à la mano quotidiennement) sur le shared
$CopyFile = "P:\Updated_RCP.xlsx"
$DestinationFile ="\\smb15-nas1.par.emea.cib\ISS_DATA\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Double connexion\Updated_RCP.xlsx"
Copy-Item -Path $CopyFile -Destination $DestinationFile -Force
# Step 2 : Copy version from Template File for the day
$filepathFile = "\\smb15-nas1.par.emea.cib\ISS_DATA\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Double connexion\"
$FileTemplate = "YYYYMMDD - MOVIS PULSE EMEA connections - Double connexion with mail.xlsm"
$FileTemp = " - MOVIS PULSE EMEA connections - Double connexion with mail.xlsm"
$today = (Get-Date -Format yyyyMMdd)
$CopyFile = $filepathFile + $FileTemplate
$PasteFile = $filepathFile + "$today" + $FileTemp
Copy-Item -Path $CopyFile -Destination $PasteFile -Force
# Step 3 : Start Conrol of the day : on Excel
$excel= new-object -comobject excel.application
$excel.Visible = $false
$excel.DisplayAlerts = $false
$classeur=$excel.workbooks.open($PasteFile)
$excel.Run('Global_Process_Double_Connexion')
get-process *excel* | stop-process -force
start-sleep -s 2
get-process *excel* | stop-process -force
# Step 4 : Archivage des Files Sources:
# $filepathSource = "\\smb15-nas1.par.emea.cib\ISS_DATA\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Double connexion\SourceFilePM\"
$Archivage = "$filepathSource" + "Archivage\"
# Move-Item -Path $filepathSource"*.csv" -Destination $Archivage -Force
Get-ChildItem -Path $filepathSource -File | Move-Item -Destination $Archivage -Force
# Step 4.1 : Déplacer les fichiers de remédiations du jour du jour dans Dossier Archivage du jour
$Today = (Get-Date -Format yyyyMMdd)
$folderArchivage = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\Remote access\02 - Double connexion\"
$folderArchivage_Today = "$folderArchivage" + "$Today" + "\"
# Check L'existence d'un dossier... Sinon, Création du dossier à la date du Jour
if (!(test-path -path $folderArchivage_Today)) {new-item -path $folderArchivage_Today -itemtype directory}
# Step 4.2 : Déplacer les fichiers des remédiations dans le dossier du jour créer à cet effet
Get-ChildItem -Path $folderArchivage -File | Move-Item -Destination $folderArchivage_Today -Force
# On attend encore 30 secondes pour ensuite ranger les emails # rep auto
start-sleep -s 30
# Step 5 : Archivage des emails: (tous les emails relatifs aux contrôles Remote 3AM or Double co)
# On va move les réponses auto suite à la remédiation... mais pas que : Check subject !
# Connecting to Outlook Inbox
$ol = New-Object -ComObject Outlook.Application
$ns = $ol.GetNamespace('mapi')
$mb = $ns.Stores['baptiste.george-prestataire@ca-cib.com'].GetRootFolder()
$inbox = $mb.Folders['Inbox']
$recipient = $ns.CreateRecipient('ISEC-ISS-Controls@ca-cib.com')
if($recipient.Resolve()){
# Write-Host 'OK'
$shared = $ns.GetSharedDefaultFolder($recipient, [Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)
}else{
Write-Host 'Unable to resolve. Can not connect to sharedMailBox ISEC-ISS!'
}
# Check Day of week 0=Sunday, 6=Saturday
$addDays = 0 # Par défaut
If ( (get-date).DayOfWeek -eq 1 ) { $addDays = 2 } # Ainsi le lundi... on rammasse les emails du week end !
$Date = [DateTime]::Now.AddDays(-$addDays)
$ReceivedDays = $Date.tostring("MM/dd/yyyy")
$Subject1 = "Réponse automatique : [ISS]"
$Subject2 = "[ISS] CA-CIB France - PDG - Utilisateurs connectés à distance à 10 heures du matin et passés sur site durant la journée"
$Subject3 = "[ISS] CA-CIB France - PDG - Personnes connectées à distance à 3 heures du matin"
$Subject4 = "RE: Paris Remote Access Report - 3h am"
$Subject5 = "CA-CIB EMEA Remote report"
$Subject6 = "CA-CIB UK Remote report"
$Subject7 = "CA-CIB Asia Remote report"
$Subject8 = "CA-CIB America Remote report"
$Subject9 = "[ISS] CA-CIB Greece Rep Office - People remotely connected at 3am"
$Subject10 = "[ISS] CA-CIB France - DGM - Personnes connectées à distance à 3 heures du matin"
$Subject11 = "[ISS] CA-CIB Austria Rep Office - Personnes connectées à distance à 3 heures du matin"
$Subject12 = "[Remote Control PULSE AMERICA 3AM]"
$Subject13 = "[Remote Control PULSE ASIA EMEA UK 3AM]"
$Subject14 = "[Remote Control MOVIS PULSE EMEA CACIB 3AM]"
$Subject15 = "[Job Status : Remote Control Double Connexion]"
$Subject16 = "[Job Status : Remote Control AM]"
$Subject17 = "[ISS] CA-CIB Argentina Rep Office - Personnes connectées à distance à 3 heures du matin"
$Subject18 = "[ISS] CA-CIB Seoul Branch - Personnes connectées à distance à 3 heures du matin"
################################### Filter Emails #######################################
# $messages = $inbox.items
# $messages = $shared.items | Where-Object -FilterScript {$_.senton -Ge "$ReceivedDays" -and $_.Subject -eq $Subject1}
$messages = $shared.items | Where-Object -FilterScript {
$_.senton -Ge "$ReceivedDays" -and ( ($_.Subject.SubString(0,[math]::min($Subject1.length,$_.Subject.length) ) -eq $Subject1) -or
($_.Subject.SubString(0,[math]::min($Subject2.length,$_.Subject.length) ) -eq $Subject2) -or
($_.Subject.SubString(0,[math]::min($Subject3.length,$_.Subject.length) ) -eq $Subject3) -or
($_.Subject.SubString(0,[math]::min($Subject4.length,$_.Subject.length) ) -eq $Subject4) -or
($_.Subject.SubString(0,[math]::min($Subject5.length,$_.Subject.length) ) -eq $Subject5) -or
($_.Subject.SubString(0,[math]::min($Subject6.length,$_.Subject.length) ) -eq $Subject6) -or
($_.Subject.SubString(0,[math]::min($Subject7.length,$_.Subject.length) ) -eq $Subject7) -or
($_.Subject.SubString(0,[math]::min($Subject8.length,$_.Subject.length) ) -eq $Subject8) -or
($_.Subject.SubString(0,[math]::min($Subject9.length,$_.Subject.length) ) -eq $Subject9) -or
($_.Subject.SubString(0,[math]::min($Subject10.length,$_.Subject.length) ) -eq $Subject10) -or
($_.Subject.SubString(0,[math]::min($Subject11.length,$_.Subject.length) ) -eq $Subject11) -or
($_.Subject.SubString(0,[math]::min($Subject12.length,$_.Subject.length) ) -eq $Subject12) -or
($_.Subject.SubString(0,[math]::min($Subject13.length,$_.Subject.length) ) -eq $Subject13) -or
($_.Subject.SubString(0,[math]::min($Subject14.length,$_.Subject.length) ) -eq $Subject14) -or
($_.Subject.SubString(0,[math]::min($Subject15.length,$_.Subject.length) ) -eq $Subject15) -or
($_.Subject.SubString(0,[math]::min($Subject16.length,$_.Subject.length) ) -eq $Subject16) -or
($_.Subject.SubString(0,[math]::min($Subject17.length,$_.Subject.length) ) -eq $Subject17) -or
($_.Subject.SubString(0,[math]::min($Subject18.length,$_.Subject.length) ) -eq $Subject18)
)}
# Query by User result :
# Count Emails into Inbox :
write-host "Query Result:" $messages.count "Emails were found in Shared Inbox" -ForegroundColor Green
$messcount = $messages.count
foreach($message in $messages){
$moveTarget = $shared.Folders.item("19 - Remote Connections Report")
[void]$message.Move($MoveTarget)
write-host "Email moved !" -BackgroundColor Blue
}
Write-Host "-->" $messcount "Emails moved to '19 - Remote Connections Report' SubFolder" -ForegroundColor Green
Write-Host "End processing Double Connexion" -ForegroundColor Cyan
Write-Host "\\\ --- ///" -ForegroundColor Cyan
# RGPD... cleanning old files
# $folderArchivage = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\Remote access\02 - Double connexion\"
# Get-ChildItem $folderArchivage -Recurse -File | Where CreationTime -lt (Get-Date).AddDays(-90) | Remove-Item -Force
# Playing wav sound
$musicPath = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Sounds\Powershell Notification.wav"
$Song = New-Object System.Media.SoundPlayer
$Song.SoundLocation = $musicPath
$Song.Play()