I currently have a script that I run every half hour which runs an sql query, and takes the results, and emails them to a group of users.  The script states how many faxes are in a faxmaker queue.  Now, the powers that be would like to add additional information, and I wanted to know how to include the additional information in my query. So, here is the script that is working.  I've changed some information to protect the innocent.

$DATETIME = Get-Date -Format "MM-dd-yyyy [HH:mm:ss]";
##### CREATE A LOG FILE AND INSERT A 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 ; 
# get 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 WITH (NOLOCK) LEFT OUTER JOIN T_FAX_REVIEW rvw WITH (NOLOCK) ON fax.ID = rvw.IDN_FAX WHERE (fax.RESULT LIKE '%SUCCESS%' OR (fax.RESULT NOT LIKE '%SUCCESS%' AND ATTACH_COUNT > 0)) AND ISNULL(rvw.BOOL_IS_COMPLETE, 0) = 0 AND fax.LINE IN (SELECT LINE FROM T_LOCATION_FAX_LINE lne WITH (NOLOCK) WHERE lne.CDE_ACTIVE_FLAG = 1 AND 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 are currently $NumberOfFlaInboundFaxes Faxes in the Florida Inbound Fax Queue (  )`n
Please Log in and help clear the Queue.  Thank you. `n
`n`n`n
The people who recieved this email are: $EmailTo`n`n`n
From ApexAdmin/SystemScheduler
"@
$EmailSubject = "There are " + $NumberOfFlaInboundFaxes + " faxes in the 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 are currently: " $NumberOfFlaInboundFaxes " Faxes in the Florida Fax Queue"
Write-Host "There are currently "  $NumberOfFlaInboundFaxes  " Files in the Florida fax queue" -foreground 'DarkBLUE' -background 'white'
Write-Host "SMTP Alert sent to " ( "$EmailTo" ).ToUpper() " on $DATETIME `n" -foreground 'DARKBLUE' -background 'WHITE' ;
write-Host "WROTE AN ENTRY TO THE LOGFILE.`n" -foreground 'DARKGREEN' -background 'WHITE'

#########

Now, I'd like to also include this query... also changed information to protect the 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 FROM DBUSER_PREF p WHERE u.IDN_USER = p.IDN_USER) IDN_USER_ZONE 
FROM DBORDER o WITH (NOLOCK)       
INNER JOIN DBUSER u WITH (NOLOCK)        
ON o.IDN_USER_CRTD = u.IDN_USER       
INNER JOIN DBROLE r WITH (NOLOCK)        
ON u.IDN_ROLE = r.IDN_ROLE      
WHERE DATEDIFF(d, GETDATE(), o.DTE_RECORD_CRTD)>=0 AND DATEDIFF(d, GETDATE(), o.DTE_RECORD_CRTD)<=0  
AND r.IS_INTERNAL_USER = 1
GROUP BY u.IDN_USER, u.NAM_LASDBUSER, u.NAM_FIRSDBUSER, r.NAM_ROLE, r.IS_INTERNAL_USER 
ORDER BY u.NAM_LASDBUSER, u.NAM_FIRSDBUSER 

Thank you in advance for any suggestions, links, etc, you can provide.  I don't mind doing the heavy lifting, ie:  research and reading, just needed to be pointed in the right directions.

John

What is stopping you from adding the second query?  It is the same s the first one.  Just append the output.

Since we do not habve your database we cannot easily do more than answer specific questions.  Post a question and any errors you are getting.

You should also format the script so it is readable.  Most people here will ignore the script if it is unreadable.

As an example this is more readable but could be improved which would make your adding a section much 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 WITH (NOLOCK) 
	LEFT OUTER JOIN T_FAX_REVIEW rvw WITH (NOLOCK) ON fax.ID = rvw.IDN_FAX 
	WHERE
		(
			fax.RESULT LIKE '%SUCCESS%' OR (
			fax.RESULT NOT LIKE '%SUCCESS%' AND ATTACH_COUNT > 0
		)
	) 
	AND ISNULL(rvw.BOOL_IS_COMPLETE, 0) = 0 
	AND fax.LINE IN (SELECT LINE FROM T_LOCATION_FAX_LINE lne WITH (NOLOCK) 
	WHERE lne.CDE_ACTIVE_FLAG = 1 AND lne.IDN_LOCATION = 3) AND fax.DATE > '2013-09-25'
'@

$template=@'
	There are currently {0} Faxes in the Florida Inbound Fax Queue (  )
     Please Log in and help clear the Queue.  Thank you.


     The people who recieved this email are: $EmailTo
 
     From ApexAdmin/SystemScheduler
'@

$subject= 'There are {0} faxes in the 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 are currently $NumberOfFlaInboundFaxes Files in the Florida fax queue" -foreground 'DarkBLUE' -background 'white'
Write-Host "SMTP Alert sent to $EmailTo on $DATETIME" -foreground 'DARKBLUE' -background 'WHITE' ;
write-Host 'WROTE AN ENTRY TO THE LOGFILE.' -foreground 'DARKGREEN' -background 'WHITE'

Start by creating a simple script to test your query. Once you have it working then add it to the existing script.

Hi John,

In addition, if you are running SQL Server 2008 R2 and newer, you can also use the "Invoke-Sqlcmd" to run sql query in powershell, and refer to the script below:

$query =@'
use annatest
select empname, iif(number > 5,'good','bad') as judge
from emptable
'@

Invoke-Sqlcmd -Query $query

To run sql query in powershell, and send the result as email, please also refer to this article:

Powershell script to run query against many servers and send combined output as email

We also recommend you can post the current script and the specific issue you encountered for more efficient support.

If there is anything else regarding this issue, please feel free to post back.

Best Regards,

Anna Wang