Home > Uncategorized > Query CRM/TMT Database for Disabled Users / Generate Report / Email Utilizing PowerShell

Query CRM/TMT Database for Disabled Users / Generate Report / Email Utilizing PowerShell

Working for the Department of Defense (DoD) I have the pleasure to work with many technologies that many people don’t.  This would be the case here for CRM/TMT.  I had a weekly request to query the CRM/TMT database to generate a report for all users within TMT whose accounts were disabled so that our CRM / TMT engineer could parse these accounts out.  So instead of manually executing a T-SQL query every week I turned to PowerShell to do all the heavy lifting, and automate the process.  Below is how I accomplish that.

On the SQL server where the TMT/CRM database lives I created a SQL Server Agent Job using PowerShell.

———————————————————————————————————
$Server = “SQL SERVER\INSTANCE
$Database = “CRMTMT_DATABASE
$query = “select b.ava_spidentity, max(a.createdon) lasacessdate from Audit
a inner join SystemUserExtensionBase b on a.ObjectId = b.SystemUserId
inner join systemuserbase c on b.SystemUserId = c.SystemUserId where a.Operation = 4 and c.isDisabled = 0
group by b.ava_spidentity”

$today = Get-Date
$filedate =$today.toString(‘MM-dd-yyyy’)

$extractfile = “PATH OF CRM REPORTS\CRM-DisabledAccounts_” + $filedate + “.csv”

$connectionTemplate = “Data Source={0};Integrated Security=SSPI;Initial Catalog={1};”
$connectionString = [string]::Format($connectionTemplate, $Server, $Database)
$connection = New-Object System.Data.SqlClient.sqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $connection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

$DataSet.Table[0] | Export-Csv $extractfile

—————————————————————————————————

On the server that will run the Task Scheduler Job and Send the Email via SMTP Services.

Create a new Task Schedule job and create a .ps1 file with the script below

—————————————————————————————————

$CRMDirectory = “\\PATH TO CRM REPORT ON SQL
$getCRMReports = Get-ChildItem $CRMDirectory
$getCRMReports | Copy-Item “\\LOCAL PATH TO COPY CRM REPORT
Get-ChildItem  “\\PATH TO CRM REPORTS ON SQL” | Remove-Item -Force

$directory = “\\LOCAL PATH WHERE CRM REPORTS WHERE COPIED
$GetLatestCRMDisabledList = Get-ChildItem $directory

$attachment = “LOCAL PATH OF CRM REPORTS\” + $GetLastestCRMDisabledList

att1 = New-Object Net.Mail.Attachment($attachment)
$email = New-Object System.Net.Mail.MailMessage
$email.From = “EMAIL OF SENDER
$email.To.Add = “EMAIL TO RECEIVER
$email.Subject = “EMAIL SUBJECT
$email.Body = “EMAIL BODY
$email.Attachments.Add($att1)
$SmtpServer = “IP / NAME OF SMTP SERVER’
$Smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$Smtp.Send($email)

Get-ChildItem “\\LOCAL PATH OF CRM REPORTS” | Remove-Item -force

—————————————————————————————————-

I set the task schedule to run once a week on Thursdays, and call it a day.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: