Licenses assigned to Assets do not show up at users license overview

[L3nny5]

Please confirm you have done the following before posting your bug report:

• I have enabled debug mode
• I have read checked the Common Issues page

Describe the bug
Since a couple of versions we do not see the licenses assigned to a device under the users license overview. Also when you check the license itself, it only shows the Device to which the license is assigned to. If I check a license in and check it out to the same device, it suddenly shows the correct information. So it seems it is "just" some old data entries of the database which do not get set correctly. Is there any workaround to get this fixed?� I don't like to check in and check out all licenses to the same device again.

Expected behavior
New checked out licenses are displayed correctly. I want to correct the old data.

Server (please complete the following information):

• Snipe-IT Version 4.6.2
• OS: Ubuntu 14.04
• Web Server: Apache
• PHP Version PHP 7.0.30-1

Desktop (please complete the following information):

• OS: All OS
• Browser All Browser
• Version All versions

Error Messages

• no error messages

[snipe]

Duplicate of #5155

[L3nny5]

I'm not sure, but I think this is a different issue. The function works fine for new checkouts. But old checkouts which were done a year or longer ago do not show correctly. If I checkin a license from a device and check it out to the same device it gets shown correctly.
So basically what I need is a repair of the database or a "script" which checks in licenses and checks them out to the same devices again. Than the issue would be fixed.

[jwhulette]

@snipe Here is a query that fixes the user empty user field when a license is check out to an asset.
I'm not sure the best way to implement.

UPDATE
    license_seats AS t1, (
        SELECT
           LS.id AS 'license_id',
            AST.assigned_to AS 'asset_assigned_to'
        FROM
            `license_seats` AS LS
        LEFT JOIN assets AS AST ON LS.asset_id = AST.id
    WHERE
        LS. `deleted_at` IS NULL
        AND LS.assigned_to IS NULL
        AND LS.asset_id IS NOT NULL) AS t2
SET
    t1.assigned_to = t2.asset_assigned_to
WHERE
    t1.id = t2.license_id;

[stale]

Is this still relevant? We haven't heard from anyone in a bit. If so, please comment with any updates or additional detail.
This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Don't take it personally, we just need to keep a handle on things. Thank you for your contributions!

[p1n7o]

@jwhulette Thanks! We used your query and it works great when you have a license check out to an asset, but only when you check out. We needed also this when you check in. So we decide to do 2 triggers (before and after) in assets tabel to work also when you check in. Hope that will help someone ^^

UPDATE license_seats AS t1,(        
        SELECT
           LSs.id AS 'license_id',
            ASTt.assigned_to AS 'asset_assigned_to'
        FROM
            `license_seats` AS LSs
        LEFT JOIN assets AS ASTt ON LSs.asset_id = ASTt.id
    WHERE
        LSs. `deleted_at` IS NULL
        AND ASTt.assigned_to IS NULL
        AND LSs.assigned_to IS NOT NULL
        AND LSs.asset_id IS NOT NULL ) as t3
SET
    t1.assigned_to = t3.asset_assigned_to
WHERE
    t1.id = t3.license_id

[stale]

Okay, it looks like this issue or feature request might still be important. We'll re-open it for now. Thank you for letting us know!