allocations/unallocations script

[ImIronMan]

Hi Team

We are manually allocating/unallocating lots of Atlassian licenses and other products in FNMS, by using  "Apply allocations and exemptions" (to exempt in bulk) and Business adapter (to allocate in bulk). As an example: Whenever we get a weekly consumption report from the Atlassian product team, we deallocate all the existing allocations and allocate them freshly. This includes lots of products and thousands of user allocations/deallocations which is kind of tedious job.

So, Is there any script (table references in db) to ease this process of allocations/unallocations. Hoping it should be possible as we know license id and compliance user id but not sure about how to use software allocation id for deallocate or allocating process

[mfranz]

A FULL JOIN of both, existing allocations & new data, should enable you to identify the three cases:

• New allocations to be added (INSERT)
• Already existing allocations (formally UPDATE, but usually no action needed)
• Old allocations (DELETE)

Therefore only 2 steps would be needed, one inserting new allocations, one deleteing no longer valid allocations. Existing ones can be ignored, which should decrease the overall effort. Some pseudo code:

SELECT *
FROM ECMImport_AtlassianLicenses new
FULL JOIN (
	SELECT
		sl.Name
		,cu.SAMAccountName
	FROM SoftwareLicense sl
	JOIN SoftwareLicenseAllocation sla
		ON sl.SoftwwareLicenseID = sla.SoftwareLicenseID
	JOIN ComplianceUser cu
		ON sla.ComplianceUserID = cu.ComplianceUserID
	) old
	ON new.LicenseName = old.Name
		AND new.UserName = old.SAMAccountName

If the "new" side is missing (IS NULL) it is considered an old allocation an can be removed. If old is missing it is a new allocation that needs to be created. The same applies if you have the SoftwareLicenseID and ComplianceUserID.

There are some more things to consider, like avoiding removal of all allocations if the import should be empty. The example is for user allocations, the same would be possible for devices, but I would keep them distinct imports.