Client Management Suite

Hi,
I need your help on my existing SQL Query. We recently moved to New altiris Version. On NS6.0 I have an automated shedule job to check an NS Task completion status and send the results using email
On new NS following code works for getting existing policy status from previous day 10PM till today morning 10AM.

What I am looking for is

# Get the result of Policy completion status and then compare the computernames from this result against "Applies to Collection"(ie Applied to XXX Resources").From this result ignore the Success and and email the resulting list.

For Eg
TestTaskPolicy computer1 11/27/2011 22.01 Success
TestTaskPolicy computer2 11/27/2011 22.01 Failure
TestTaskPolicy computer3 11/27/2011 22.01 Not Reported
TestTaskPolicy computer4 11/25/2011 22.08 Success
TestTaskPolicy computer5 11/24/2011 22.09 Success

By running Below sql query I will only computer1, 2 and 3.
Computer 4 and computer 5 is skipped because it didn't run and it doesn't fall on yesterday's date.
If we compare against group/target the policy applies to then I will get computer 4 and 5 too.

SQL Code for reference:

select taskname,machinename,eventtime,status   from vSMS_SoftwareExecutionStatus2  where 
EventTime between replace(convert(varchar,getdate()-1,111),'/','-')  +' 22:00:00.000'
and replace(convert(varchar,getdate(),111),'/','-')  +' 10:00:00.000'
and taskname like '%TestTask%'
and status!=2
order by eventtime

Thanks

Joraj

Hi Joraj,

you should check the sql behind the canned reports under Tasks => Status especially 'Executed Jobs/Tasks by Filter' - this looks like what your searching for regarding jobs/tasks. Under Software => Compliance and Software => Delivery you can get hints out of the SQL behind the report to find out how to query what you need in the new database.

Do you want to have the results for Jobs/Tasks or Software Delivery Policys, cause you are mixing them up in your explanation.

Thanks for the reply.

Not jobs/task..I need the results for Software Delivery Policys