Email SQL query results from powershell

i have script run every half hour runs sql query, , takes results, , emails them group of users.  the script states how many faxes in faxmaker queue.  now, powers add additional information, , wanted know how include additional information in query. so, here script working.  i've changed information protect innocent.

$datetime = get-date -format "mm-dd-yyyy [hh:mm:ss]";
##### create log file , insert log heading ;
$log_heading = "show florida inbound fax queues ran @: " + $datetime ;
add-content "e:\psh\livescripts\logs\show-florida-inbound-fax-queue.txt" $log_heading ; 
# sql dbname  sample

#email variables
$emailfrom = "alert@myworkdomain.com" ;

$emailto = "my email list" 
############################################################################################################################################################
$sqlserver = "my-sql-server"
$sqlcatalog = "faxmakerarchive"
$sqlconnection = new-object system.data.sqlclient.sqlconnection
$sqlconnection.connectionstring = "server = $sqlserver; database = $sqlcatalog; integrated security = true"
$sqlconnection.open()
$sqlcmd = new-object system.data.sqlclient.sqlcommand
$sqlcmd.commandtext = "select count (distinct fax.id) datona_beach_fax_count from fm_faxin fax (nolock) left outer join t_fax_review rvw (nolock) on fax.id = rvw.idn_fax (fax.result '%success%' or (fax.result not '%success%' , attach_count > 0)) and isnull(rvw.bool_is_complete, 0) = 0 , fax.line in (select line t_location_fax_line lne (nolock) lne.cde_active_flag = 1 , lne.idn_location = 3) and fax.date >   '2013-09-25' "                                          
$sqlcmd.connection = $sqlconnection
$numberofflainboundfaxes = $sqlcmd.executescalar()
$sqlconnection.close()

#################################################################################################################################################################

if( $numberofflainboundfaxes -gt 29)
{ 
#email body
$emailbody = @"
there $numberofflainboundfaxes faxes in florida inbound fax queue (  )`n
please log in , clear queue.  thank you. `n
`n`n`n
people recieved email are: $emailto`n`n`n
apexadmin/systemscheduler
"@
$emailsubject = "there " + $numberofflainboundfaxes + " faxes in inbound florida fax queue. " + $datetime   ;
#end of email body
#send mail via gmail through powersehll
$smtpserver = "smtp.gmail.com" 
$smtpclient = new-object net.mail.smtpclient($smtpserver, 587) 
$smtpclient.enablessl = $true 
$smtpclient.credentials = new-object system.net.networkcredential("email user", "email password"); 
$smtpclient.send($emailfrom, $emailto, $emailsubject , $emailbody)
############################################################################################

}
else
{
write-host "no alert  on $datetime `n" -foreground 'darkgreen' -background 'white' ;
}
#write-output "there currently: " $numberofflainboundfaxes " faxes in florida fax queue"
write-host "there "  $numberofflainboundfaxes  " files in florida fax queue" -foreground 'darkblue' -background 'white'
write-host "smtp alert sent " ( "$emailto" ).toupper() " on $datetime `n" -foreground 'darkblue' -background 'white' ;
write-host "wrote entry logfile.`n" -foreground 'darkgreen' -background 'white'

#########

now, i'd include query... changed information protect innocent.

select u.idn_user,      
u.nam_lasdbuser,      
u.nam_firsdbuser,      
r.nam_role,      
count(o.idn_order) order_cnt,      
count(distinct o.idn_client) cliendbcnt,
(select idn_zone dbuser_pref p u.idn_user = p.idn_user) idn_user_zone 
dborder o (nolock)       
inner join dbuser u (nolock)        
on o.idn_user_crtd = u.idn_user       
inner join dbrole r (nolock)        
on u.idn_role = r.idn_role      
datediff(d, getdate(), o.dte_record_crtd)>=0 , datediff(d, getdate(), o.dte_record_crtd)<=0  
and r.is_internal_user = 1
group u.idn_user, u.nam_lasdbuser, u.nam_firsdbuser, r.nam_role, r.is_internal_user 
order u.nam_lasdbuser, u.nam_firsdbuser 

thank in advance suggestions, links, etc, can provide.  i don't mind doing heavy lifting, ie:  research , reading, needed pointed in right directions.

john

as example more readable improved make adding section easier.

$datetime = get-date -format 'mm-dd-yyyy [hh:mm:ss]' $log_heading = "show florida inbound fax queues ran @: " + $datetime; add-content "e:\psh\livescripts\logs\show-florida-inbound-fax-queue.txt" $log_heading;  $sql = @' select count(distinct fax.id) datona_beach_fax_count  	from fm_faxin fax (nolock)  	left outer join t_fax_review rvw (nolock) on fax.id = rvw.idn_fax  	where 		( 			fax.result '%success%' or ( 			fax.result not '%success%' , attach_count > 0 		) 	)  	and isnull(rvw.bool_is_complete, 0) = 0  	and fax.line in (select line t_location_fax_line lne (nolock)  	where lne.cde_active_flag = 1 , lne.idn_location = 3) , fax.date > '2013-09-25' '@  $template=@' 	there {0} faxes in florida inbound fax queue (  )      please log in , clear queue.  thank you.        people recieved email are: $emailto        apexadmin/systemscheduler '@  $subject= 'there {0} faxes in inbound florida fax queue. {1:mm-dd-yyyy [hh:mm:ss]}' $emailfrom='alert@myworkdomain.com' $emailto = "my email list" $sqlserver = "my-sql-server" $sqlcatalog = "faxmakerarchive"  #run query $sqlconnection = new-object system.data.sqlclient.sqlconnection $sqlconnection.connectionstring = "server = $sqlserver; database = $sqlcatalog; integrated security = true" $sqlconnection.open() $sqlcmd = new-object system.data.sqlclient.sqlcommand $sqlcmd.commandtext = $sql $sqlcmd.connection = $sqlconnection $numberofflainboundfaxes = $sqlcmd.executescalar() $sqlconnection.close()  # build email if needed if($numberofflainboundfaxes -gt 29){ 	$emailbody=$template -f $numberofflainboundfaxes 	$emailsubject = $subject -f $numberofflainboundfaxes,[datetime]::now 	$smtpserver='smtp.gmail.com"' 	$smtpclient=new-object net.mail.smtpclient($smtpserver, 587)  	$smtpclient.enablessl = $true  	$smtpclient.credentials = new-object system.net.networkcredential("email user", "email password");  	$smtpclient.send($emailfrom, $emailto, $emailsubject , $emailbody) }else{ 	write-host "no alert  on $datetime `n" -foreground 'darkgreen' -background 'white' ; }  write-host "there $numberofflainboundfaxes files in florida fax queue" -foreground 'darkblue' -background 'white' write-host "smtp alert sent $emailto on $datetime" -foreground 'darkblue' -background 'white' ; write-host 'wrote entry logfile.' -foreground 'darkgreen' -background 'white' 

---

\_(ツ)_/

Windows Server  >  Windows PowerShell