Sometimes it's desirable to create 4 collections for inventory tasks so software audits only occur on a particular day for 1/4 of the computers in the environment without dealing with the random execution of AexRunControl. Or your CIO wants to evenly distribute a software delivery over 10 days to reduce risk and you would like to avoid creating 10 dynamic collections.
One of the challenges we often face in Notification Server is creating collections that evenly divide computers into several groups to distribute inventory processing or software delivery.
The SQL script below provides a simple way to create a dynamic collection which will be roughly 1/4 of the computers in your Notification Server. It contains 2 variables: @Groups and @GroupNumber. To create another collection for the different 1/4's of your computers, change the value of @GroupNumber to 2, 3 or 4. No computer will be in 2 groups and the groups will be close in size more or less evenly distributed among your enterprise.
The example code delivers 1 of 4 groups. You can specify one of the other 3 groups by changing the @GroupVariable to 2, 3 or 4. You can adjust the @Groups value to be as many groups as you wish.
DECLARE @Groups int, @GroupNumber int
/* Number of groups to divide population into */
SET @Groups = 4
/* The group for this collection( between 1 and the @Groups value */
SET @GroupNumber = 1
SELECT Guid
FROM vComputer vc
WHERE (
datediff(second,
convert(datetime,convert(char(10),vc.CreatedDate,112)),
vc.CreatedDate)
% @Groups
) +1 = @GroupNumber
How does that work? Some simple integer math from grade school. The modulo operation in SQL returns the remainder when one integer is divided by another. Divide 17 by 4 and you will have a modulo or remainder of 3. The possible modulos when dividing by 4 are 0,1,2 and 3.
Now, we need an integer value associated with a computer which will not change and is more or less random. Created date does not change unless the computer is deleted. The number of seconds between the midnight and the time in the created date will not change and is not based on any outside values like OS, location, etc. Even if you import a batch of computers, they will have different times in their created date column.
The number of seconds between midnight of the date it was created and the time it was created when divided by 4 yields a value of 0, 1, 2 or 3 for each computer. No computer can have 2 different values for this calculation. The near even distribution occurs because we're not using ranges of values, but relying on the spread of integers between 0 and 1,440 seconds in the day. It's not a perfectly even distribution, but it gets close and it runs very quickly.
How about that example for the 10% per day for software delivery? Create a collection for 1/10 of the population with the SQL below. Note the @GroupNumber is 1 and the comparision in the last line is "<=". Enable your software delivery task on day 1 for the first 10% of your population.
DECLARE @Groups int, @GroupNumber int
/* Number of groups to divide population into */
SET @Groups = 10
/* The group for this collection( between 1 and the @Groups value */
SET @GroupNumber = 1
SELECT Guid
FROM vComputer vc
WHERE (
datediff(second,
convert(datetime,convert(char(10),vc.CreatedDate,112)),
vc.CreatedDate)
% @Groups
) +1 <= @GroupNumber
On day 2 of your 10 day software delivery event, edit your dynamic SQL collection and change the @GroupNumber to 2. This will be a collection of the first and second 1/10th's of your population. Each day, advance the @GroupNumber by 1 to increase the population of the collection by 10%.
Having a hard time visualizing/trusting this works? Try the query below to see how it would distribute your computers into nearly congruent populations. Change the @Groups variable to the number of groups you need.
DECLARE @Groups int
SET @Groups = 10
SELECT (
datediff(second,
convert(datetime,convert(char(10),vc.CreatedDate,112)),
vc.CreatedDate)
% @Groups
) +1 AS [Group Number] ,
Count(*) as [Count of Computers]
FROM vComputer vc
GROUP BY (
datediff(second,
convert(datetime,convert(char(10),vc.CreatedDate,112)),
vc.CreatedDate)
% @Groups
) +1