Apr 17 2020 04:02 AM
I am new to Powershell and I want to run a powershell to query to SCCM Deployement status.
1)If appenforcement state is 1000 or 1001 for multple application names then the powershell output should be TRUE else FALSE.
2)IF the query inside for-each loop does not return any row ,then the counter must be incremented.
If the output from 1 is FALSE or counter>0 in step 2 then final output should be FALSE else true
PFB the query
$ApplicationName = "Add Support Group,Notepad-PlusPlus-7.6.6"
$AppNames = $ApplicationName -split ","
$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$dataTable = New-Object System.Data.DataTable
$dataSource = "dataSource"
$database = "database"
# Get Start Time
$startDTM = (Get-Date)
# Open a connection
#cls
#Write-host "Opening a connection to '$database' on '$dataSource'"
#Using windows authentication, or..
$connectionString = "Server=$dataSource;Database=$database;Integrated Security=SSPI;"
# Using SQL authentication
#$connectionString = "Server=$dataSource;Database=$database;uid=ConfigMgrDB_Read;pwd=Pa$$w0rd;Integrated Security=false"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$command = $connection.CreateCommand()
foreach ($AppName in $AppNames) {
Write-Output $AppName
$query = "
select distinct
ae.AppEnforcementState
from v_R_System_Valid s1
join vAppDTDeploymentResultsPerClient ae on ae.ResourceID=s1.ResourceID
join v_CICurrentComplianceStatus ci2 on ci2.CI_ID=ae.CI_ID AND
ci2.ResourceID=s1.ResourceID
join v_ApplicationAssignment aa on ae.AssignmentID = aa.AssignmentID
where ae.AppEnforcementState is not null and aa.ApplicationName = '$AppName'
and s1.netbios_name0 =$Computer "
$command.CommandText = $query
$sqlAdapter.SelectCommand = $command
$result = $command.ExecuteReader()
$result.Close()
$result = $sqlAdapter.Fill($dataTable)
}
Write-Output -Verbose $dataTable
$connection.Close()
write-host "Starting after connection close"
$success
for($i=0;$i -lt $dataTable.Rows.Count;$i++)
{
Write-Output $dataTable.Rows[$i][0]
if ( $dataTable.Rows[$i][0] -eq 1000 -Or $dataTable.Rows[$i][0] -eq 1001 ){
$success = "true"
} else {
$success = "false"
break
}
}
Write-Output $success
Apr 20 2020 06:17 PM