mardi 5 mai 2020

How to write a powershell script to compare the input csv file with the output from sql query and trigger a mail?

In the below script i have to compare the output with an input csv file, and if the the values doesnt matches then trigger a mail and if the values are equal then no mail should be triggerd


param(
    $emailFrom = 'Reporting.Services@accenture.com',
    $emailTo = @("aditi.m.singh@accenture.com","s.ab.balakrishnan@accenture.com","rajeshbabu.ks@accenture.com"),
    $emailSubject = 'Sending the Attachment',
    $smtp = 'AMRINT.SMTP.ACCENTURE.COM',
    $Server = (Import-Csv -Path "D:\Scripts\input.csv").ComputerName ,
    $SQLDBName = 'ReportServer',
    $SQLQuery = @"
        SELECT Distinct
            RL.RoleName,
            USR.UserName
        FROM  
            Catalog C
            INNER JOIN Policies PL 
                    ON C.PolicyID = PL.PolicyID
            INNER JOIN PolicyUserRole PUR
                    ON PUR.PolicyID = PL.PolicyID
            INNER JOIN Users USR
                    ON PUR.UserID = USR.UserID
            INNER JOIN dbo.Roles RL
                    ON RL.RoleID = PUR.RoleID    
            WHERE RoleName = 'Content Manager' 
            ORDER BY USR.UserName    
"@
)

# This code connects to the SQL server and retrieves the data    
$SQLConnection = New-Object System.Data.SqlClient.SqlConnection    
$SqlConnection.ConnectionString = "Server = $Server; Database = $SQLDBName; Integrated Security=true;"    
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand    
$SqlCmd.CommandText = $SqlQuery    
$SqlCmd.Connection = $SqlConnection    
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter    
$SqlAdapter.SelectCommand = $SqlCmd    
$DataSet = New-Object System.Data.DataSet    
$SqlAdapter.Fill($DataSet)    
$SqlConnection.Close()               
$Style = "
<style>
    TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}
    TH{border-width: 1px;padding: 3px;border-style: solid;border-color: black;}
    TD{border-width: 1px;padding: 3px;border-style: solid;border-color: black;}
</style>
"
$data = $DataSet.Tables[0]
$html = $data `
    | Select-Object -Property RoleName, UserName `
    |  ConvertTo-HTML -head $Style  `
            | Out-String

Send-MailMessage `
    -From $emailFrom `
    -To $emailTo `
    -Subject $emailSubject `
    -BodyAsHtml $html `
    -SmtpServer $smtp

Aucun commentaire:

Enregistrer un commentaire