Altiris 7.5 Filter SQL - Very strange behaviour

[Darren Collins]

I have a Filter where the membership changes and does not reflect the 'truth': i.e. when I manually click 'Update Membership' it shows correctly but is not automatically updating correctly, even when directly linked in to the target of a Policy.  This is quite a long one, apologies in advance.

For a Filter used for extended piloting, I wanted to it to dynamically contain 7% (plus one to ensure no zero returns) of the managed Windows computers that had checked in to the Altiris server within the last 7 days, ordered by GUID so that the sample was initially random but would be fairly static for regularly connecting computers.

Using a combination of copy / paste, Googling and a dash of imagination, I came up with the following SQL which I used in NS6 with great success (if it's not clear from the below, I am at best a beginner novice at SQL):

SELECT TOP (CONVERT(int, ROUND((SELECT COUNT(guid)  FROM  vComputer vc               
join resourceupdatesummary rus               
on vc.guid = rus.resourceguid               
AND rus.inventoryclassguid = 'C74002B6-C7B9-47BB-A5D6-3031AF73BB8D'                
WHERE vc.domain = 'CONNECT'   and Datediff(dd,rus.[modifieddate],Getdate()) <= 7) * 0.07, 0)) + 1) guid  FROM  vComputer vc               
join resourceupdatesummary rus               
on vc.guid = rus.resourceguid               
AND rus.inventoryclassguid = 'C74002B6-C7B9-47BB-A5D6-3031AF73BB8D'                
WHERE vc.domain = 'CONNECT'  and Datediff(dd,rus.[modifieddate],Getdate()) <= 7      
ORDER by guid

So when we installed Altiris 7.1 (and now upgraded to 7.5) to migrate our new Windows 7 computers to, I used the same code since the GUID in the database for the inventoryclassguid is the same as NS6.

Initially it looked like the code was working perfectly: in our test environment we have 45 domain computers in the 'CONNECT' domain and the SQL was returning 3 computers as I would expect, since some of that 45 have not connected in the last 7 days.

 

But... after a few hours the Filter showed 5 or 6 or 8 members.  I reset the Filter again yesterday and I did not refresh it manually (by clicking 'Update Membership') at all, and this morning it then contained 18 members!  I've just clicked the 'Update Membership' button and the membership has again reverted to 3.  

Over the past few weeks the behaviour is consistent in that the Filter never contains the correct number until I click 'Update Membership' and the number it does contain varies up and down, but always too high, within hours after I do click 'Update Membership'.

 

I am hoping that this behaviour makes sense to someone and / or a workaround or SQL fix can make it behave.

Kind regards,
Darren.

NB. the reason 7% was chosen is because we have an estate of about 2,500 managed computers and this means that normally the NS6 collection contains around 100 computers that have checked in the last 7 days, which is a nice extended pilot number.

[Darren Collins]

I've just found the PERCENT functionality of T-SQL (I said I was a novice!) so the SQL in the Filter is a liitle cleaner:

SELECT TOP 7 PERCENT guid  FROM  vComputer vc               
join resourceupdatesummary rus               
on vc.guid = rus.resourceguid               
AND rus.inventoryclassguid = 'C74002B6-C7B9-47BB-A5D6-3031AF73BB8D'
WHERE vc.domain = 'CONNECT'  and Datediff(dd,rus.[modifieddate],Getdate()) <= 7      
ORDER by guid

This cleaner Filter also had the identical symptoms above.  Yesterday at 16:28 I clicked  'Update Membership' on this filter with the leaner SQL.  The Filter Membership was 3 computers.  I then kept checking the membership afterwards (by clicking on another Filter then clicking back on this one and not by clicking 'Update Membership').  Below are the results:

 

Date	Time	# Filter Membership
9 Jan 2014	16:28	3  (after clicking 'Update Membership')
9 Jan 2014	16:53	4
9 Jan 2014	17:17	5
10 Jan 2014	16:42	19  !!!  (nineteen)
10 Jan 2014	16:48	3 (after clicking 'Update Membership')

So I'm still none the wiser.  Our Filters that are linked to the target of an enabled Policy, update automatically every 5 minutes.  This must be some sort of bug as the automatic background refreshing is obviously not returning the correct results, or is appending or something, even though manually recalculating the Filter by clicking 'Update Membership' returns the expected results.

In the mean time, I guess I'm going to have to hard code the TOP number of results I require instead of trying for a dymanic number based of total active managed computers.

Regards, Darren.

[KSchroeder]

I think it is an issue with top(); I don't recall the exact reason, but it will give you different results as you see. This is I believe in part to the way your"simple" sql query is wrapped behind the scenes in the SMP filter engine. I think @IanAtkin figured a way around this limitation, perhaps he'll pop in with a comment. One way,perhaps as dynamic, would be to use the first character of the guid as your filter, perhaps dependent on the hour or second AT the time the filter is being evaluated. I don't have a sql engine handy, but something using Select case(datepart(second, getdate()) When value between 0 and 4 then '0' When value between 5 and 9 then '1' And so on...

[KSchroeder]

Oh just remembered, I think it us the order by statement possibly with a guid...maybe explicitly cast the guid as a string?

[Darren Collins]

Thanks for the reply.  Ian currently is resolving serious problems upgrading the virtual hardware of our Altiris 7.5 environment but as soon as he's fixed that I'll test if TOP() itself is an issue by hardcoding SELECT TOP(4) (or another number) without percent and see if it retains the correct number of entries after a period of time.

As for using the first character of the GUID based on a unit of time, if I've got the right end of the stick I think that this could produce the opposite of what I need in that it would be too random!  I need a random sample of computers, but I need that sample to not change very much (hence the SELECT TOP() guid) except for where the computer is not seen for more than seven days when it would drop out of the results.

I appreciate the response and I'll try to remember to post back with any updates.

Cheers again, Darren.

[Darren Collins]

I'm afraid I don't know what you mean by 'explicitly cast the guid as a string?'

Darren.

[Migration User]

I'm a little late on this discussion but I've played with TOP issue in filters in all Altiris versions that I can remember.  I think the problems lies in delta update where the delta update is always true with your top statement.  I've been using ntile instead and I don't get those problems.

select vc.guid from vcomputer vc
inner join (
select ntile(5) over (order by guid) "grp",guid  from vcomputer
) "grp" on vc.Guid=grp.Guid
where

grp.grp=1

 

This sample gives you the first 20% of computers.

[ianatkin]

I didn't even know of NTILE's existence before now. Will take a look. Many thanks,

Ian./

 

[Darren Collins]

Well it's only taken 6+ months to try out your method!

I have used your NTILE method on the following SQL.  I'll post back with the stability results on the number of computers returned (currently 105).  The filter is applied via a target to an enabled policy:

select vc.guid from vcomputer vc
join (
select ntile(20) over (order by guid) "grp",guid  from   ----- ntile 20 is about 5%

(SELECT guid FROM  vComputer vc
join resourceupdatesummary rus               
on vc.guid = rus.resourceguid               
AND rus.inventoryclassguid = 'C74002B6-C7B9-47BB-A5D6-3031AF73BB8D'  
WHERE vc.domain = 'CONNECT'  and Datediff(dd,rus.[modifieddate],Getdate()) <= 7   ) xxx

) "grp" on vc.Guid=grp.Guid
where

grp.grp=1

 

[Darren Collins]

Well I think it's pretty conclusive that the NTILE method is working for us where TOP failed.

Many thanks ericg2 for the great advice, and sorry it took me so long to try it out!