Powershell for SCCM

%3CLINGO-SUB%20id%3D%22lingo-sub-1315464%22%20slang%3D%22en-US%22%3EPowershell%20for%20SCCM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1315464%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20am%20new%20to%20Powershell%20and%20I%20want%20to%20run%20a%20powershell%20to%20query%20to%20SCCM%20Deployement%20status.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E1)If%20appenforcement%20state%20is%201000%20or%201001%20for%20multple%20application%20names%20then%20the%20powershell%20output%20should%20be%20TRUE%20else%20FALSE.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E2)IF%20the%20query%20inside%20for-each%20loop%20does%20not%20return%20any%20row%20%2Cthen%20the%20counter%20must%20be%20incremented.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EIf%20the%20output%20from%201%20is%20FALSE%20or%20counter%26gt%3B0%20in%20step%202%20then%20final%20output%20should%20be%20FALSE%20else%20true%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EPFB%20the%20query%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%24ApplicationName%20%3D%20%22Add%20Support%20Group%2CNotepad-PlusPlus-7.6.6%22%3CBR%20%2F%3E%24AppNames%20%3D%20%24ApplicationName%20-split%20%22%2C%22%3C%2FP%3E%3CP%3E%24sqlAdapter%20%3D%20New-Object%20System.Data.SqlClient.SqlDataAdapter%3CBR%20%2F%3E%24dataTable%20%3D%20New-Object%20System.Data.DataTable%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%24dataSource%20%3D%20%22dataSource%22%3CBR%20%2F%3E%24database%20%3D%20%22database%22%3CBR%20%2F%3E%23%20Get%20Start%20Time%3CBR%20%2F%3E%24startDTM%20%3D%20(Get-Date)%3CBR%20%2F%3E%23%20Open%20a%20connection%3CBR%20%2F%3E%23cls%3CBR%20%2F%3E%23Write-host%20%22Opening%20a%20connection%20to%20'%24database'%20on%20'%24dataSource'%22%3CBR%20%2F%3E%23Using%20windows%20authentication%2C%20or..%3CBR%20%2F%3E%24connectionString%20%3D%20%22Server%3D%24dataSource%3BDatabase%3D%24database%3BIntegrated%20Security%3DSSPI%3B%22%3CBR%20%2F%3E%23%20Using%20SQL%20authentication%3CBR%20%2F%3E%23%24connectionString%20%3D%20%22Server%3D%24dataSource%3BDatabase%3D%24database%3Buid%3DConfigMgrDB_Read%3Bpwd%3DPa%24%24w0rd%3BIntegrated%20Security%3Dfalse%22%3CBR%20%2F%3E%24connection%20%3D%20New-Object%20System.Data.SqlClient.SqlConnection%3CBR%20%2F%3E%24connection.ConnectionString%20%3D%20%24connectionString%3CBR%20%2F%3E%24connection.Open()%3CBR%20%2F%3E%24command%20%3D%20%24connection.CreateCommand()%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eforeach%20(%24AppName%20in%20%24AppNames)%20%7B%3C%2FP%3E%3CP%3EWrite-Output%20%24AppName%3C%2FP%3E%3CP%3E%24query%20%3D%20%22%3CBR%20%2F%3Eselect%20distinct%3CBR%20%2F%3Eae.AppEnforcementState%3CBR%20%2F%3Efrom%20v_R_System_Valid%20s1%3CBR%20%2F%3Ejoin%20vAppDTDeploymentResultsPerClient%20ae%20on%20ae.ResourceID%3Ds1.ResourceID%3CBR%20%2F%3Ejoin%20v_CICurrentComplianceStatus%20ci2%20on%20ci2.CI_ID%3Dae.CI_ID%20AND%3CBR%20%2F%3Eci2.ResourceID%3Ds1.ResourceID%3CBR%20%2F%3Ejoin%20v_ApplicationAssignment%20aa%20on%20ae.AssignmentID%20%3D%20aa.AssignmentID%3CBR%20%2F%3Ewhere%20ae.AppEnforcementState%20is%20not%20null%20and%20aa.ApplicationName%20%3D%20'%24AppName'%3CBR%20%2F%3Eand%20s1.netbios_name0%20%3D%24Computer%20%22%3C%2FP%3E%3CP%3E%24command.CommandText%20%3D%20%24query%3CBR%20%2F%3E%24sqlAdapter.SelectCommand%20%3D%20%24command%3CBR%20%2F%3E%24result%20%3D%20%24command.ExecuteReader()%3CBR%20%2F%3E%24result.Close()%3CBR%20%2F%3E%24result%20%3D%20%24sqlAdapter.Fill(%24dataTable)%3CBR%20%2F%3E%7D%3CBR%20%2F%3EWrite-Output%20-Verbose%20%24dataTable%3C%2FP%3E%3CP%3E%24connection.Close()%3CBR%20%2F%3Ewrite-host%20%22Starting%20after%20connection%20close%22%3C%2FP%3E%3CP%3E%24success%3CBR%20%2F%3Efor(%24i%3D0%3B%24i%20-lt%20%24dataTable.Rows.Count%3B%24i%2B%2B)%3CBR%20%2F%3E%7B%3CBR%20%2F%3EWrite-Output%20%24dataTable.Rows%5B%24i%5D%5B0%5D%3CBR%20%2F%3Eif%20(%20%24dataTable.Rows%5B%24i%5D%5B0%5D%20-eq%201000%20-Or%20%24dataTable.Rows%5B%24i%5D%5B0%5D%20-eq%201001%20)%7B%3CBR%20%2F%3E%24success%20%3D%20%22true%22%3CBR%20%2F%3E%7D%20else%20%7B%3CBR%20%2F%3E%24success%20%3D%20%22false%22%3CBR%20%2F%3Ebreak%3CBR%20%2F%3E%7D%3CBR%20%2F%3E%7D%3CBR%20%2F%3EWrite-Output%20%24success%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1315464%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESystem%20Center%20Configuration%20Manager%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1323262%22%20slang%3D%22en-US%22%3ERe%3A%20Powershell%20for%20SCCM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1323262%22%20slang%3D%22en-US%22%3Eso%20what%20happens%20when%20you%20run%20this%20script%3F%3CBR%20%2F%3Ethe%20syntax%20looks%20OK%20with%20the%20exception%20of%20what's%20populating%20%24Computer%20that%20you%20pass%20to%20query%20string%20s1.netbios_name0%20%3D%24Computer%20%22%3CBR%20%2F%3EI%20can't%20see%20an%20IF%20query%20inside%20forEach(%24AppName%20in%20%24AppNames)%20loop%20unless%20you're%20referring%20to%20the%20IF%20inside%20for(%24i%3D0%3B%24i%20-lt%20%24dataTable.Rows.Count%3B%24i%2B%2B)%20which%20has%20no%20logic%20for%20a%20%24counter%20variable.%3C%2FLINGO-BODY%3E
Occasional Visitor

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

1 Reply
so what happens when you run this script?
the syntax looks OK with the exception of what's populating $Computer that you pass to query string s1.netbios_name0 =$Computer "
I can't see an IF query inside forEach($AppName in $AppNames) loop unless you're referring to the IF inside for($i=0;$i -lt $dataTable.Rows.Count;$i++) which has no logic for a $counter variable.