O365 Import_Duplicate Licenses

[ImIronMan]

Hi All

We have O365 integration into FNMS 2019R2.

For the past couple of weeks, we observed there are duplicate licenses created fro O 365 import

Screenshot attached.

Can you please help understand how did it happen and how to ensure it is not repeated after we delete

Regards

Rajesh Ponnala

[mfranz]

Hi Rajesh,

This usually happens when the compliance connection is removed and newly created in the Beacon. I guess this is on-prem, so you can check the following tables for possible duplicates:

SELECT *
FROM ImportedSoftwareLicense

SELECT *
FROM ImportedUser

SELECT *
FROM ImportedSoftwareLicenseAllocation

If there are no duplicates, ImportedSoftwareLicense should still give you an idea which of your duplicate licenses  are active linked (see SoftwareLicenseID) and which ones can be removed.

Best regards,

Markward

[ImIronMan]

HI Markward, @mfranz 

Thanks for your response. Now I could find the duplicates or 'non active' licenses.

Can you also help me with a query that I can run to delete these unwanted licenses from db ? Just want to ensure my delete query does not impact any other tables or data

Regards

Rajesh Ponnala

[mfranz · Accepted solution]

Hi Rajesh,

Regarding the "Imported" data, you should be ble to remove anythign that is no longer related to any compliance connection:

DELETE
FROM ImportedSoftwareLicense
WHERE ComplianceConnectionID NOT IN (
    SELECT ComplianceConnectionID
    FROM ComplianceConnection
)

DELETE
FROM ImportedUser
WHERE ComplianceConnectionID NOT IN (
    SELECT ComplianceConnectionID
    FROM ComplianceConnection
)

DELETE
FROM ImportedSoftwareLicenseAllocation
WHERE ComplianceConnectionID NOT IN (
    SELECT ComplianceConnectionID
    FROM ComplianceConnection
)

There's not really a query that will remove duplicates from SoftwareLicense, without risking to remove valid data. So there I would take a list of these and compare the SoftwareLicenseIDs to the ones from ImportedSoftwraeLicense. Then remove the ones no longer related to the imported licenses.

Or, perhaps, remove anything (all the imported stuff & related licenes) and have the compliance import re-create anything from scratch.

Best regards,

Markward

[ImIronMan]

Yes Markward. I have followed these steps. Please correct if I'm wrong

1. Ran the first query you provided, found 42 licenses with out any duplicates. These must be active imported licenses

2. Downloaded all licenses from software license_MT

3. Compared above both reports.

4. Found the duplicate licenses  - 37. (whose software license ID is not present in step 1). Now will delete these ones

I have deleted 15 licenses from GUI carefully. But rest other 22 licenses looks to be same except the creation/update dates. So thought of using any automated query help full to delete license just by using the software license ID

[mfranz]

Hi Rajesh,

The difficulty is less in comparing the SoftwareLicenseIDs (between ImportedSoftwareLicense and SoftwareLicense). It is more about picking only the O365 licenses. Usually, there's many more licenses that you want to leave untouched, although they're not related to ImportedLicense.

Best regards,

Markward