Report on usage and non-use of licenses assigned to users

[MS_10110283]

Hi everyone,
I wanted to build together a strategy to optimize the assignment of licenses to users of our companies.
The main goal is to understand which users have a license assigned but are not using the system.
Let me explain better: From the Security Audit Reporting utility, for example by analyzing the Login actions of the last 3 months I can see those users who have logged in, some more and some less. But I can't see who has a license and has never logged in.
It would be great to be able to also see if users have nominal or ADU licenses in a single report.

Basically build a report with the period filter (for example last 3 months) with columns:
username | assigned licenses | login number (including NEVER) | last login

A nice to have for another report would be to understand who has an Advanced license and instead does not perform any action but only does Search and View actions.
Basically build a report with the period filter (for example last 3 months) with columns:
username | assigned licenses | Action taken (including NOTHING=NO LOGIN)

[avillanueva]

PTC does have some license tools they use for compliance reporting. I had created my own in Excel which includes much of what you are asking for. I can see if I can generalize, sanitize and document. I might have posted this already. It uses queries to show evidence of license usage (such as CAD or WTPart creation/modification) to justify license usage. I used to do exactly what you are describing.

[avillanueva]

I was able to find an old post of mine on this very topic. https://community.ptc.com/t5/Windchill/Hourly-license-usage-report-Windchill/m-p/889133

Let me know if what I show in the screen shot is useful. I can work on getting the spreadsheet cleaned up and posted. 

[MS_10110283]

Is it possible to automate with SQL queries?

[anursingh]

Hi @MS_10110283,

I wanted to see if you got the help you needed.

If so, please mark the appropriate reply as the Accepted Solution. It will help other members who may have the same question.
Of course, if you have more to share on your issue, please pursue the conversation. 

Thanks,
Anurag