top of page

#######################################################
#    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()

bottom of page