This article is relevant if you are looking for a NetSuite SQL Query to provide you information on your user community license usage.

Background

In last week’s article, Quickly Assess NetSuite License Usage, I discussed how to use conventional saved searches to help you review how your NetSuite user community is logging in to the environment. NetSuite usage implied license consumption. However, the weakness in the conventional saved search approach is that we do not have sufficient reach to determine the type of licenses being consumed.

In this week’s article, I used NetSuite’s SuiteQL technology to produce a SQL query that will more conclusively answer the licensing question. The query affords us an opportunity to observe more complex SQL elements which can be an inspiration for getting at NetSuite data in new ways.

Assessing NetSuite License Usage

I broke up the NetSuite license pattern into three general parts:

1. Full: consumes a full license
2. Employee: consumes a limited employee license typically offered at the price of 5:1 relative to Full licenses.
3. Free: licenses that are offered without charge. We usually see this in Customer and Vendor situations — but may be for Partner usage

The key to figuring out the license model is to understand how users are assigned to roles and respective work centers. Furthermore, we still want to determine when the last time a user logged in and the number of times to see usage patterns. This can help us see if there are users that are candidates to reassign valuable licenses.  The query will indicate if the same user is assigned to different license types as well.

NetSuite SQL to Review Users and Licenses

Below is the SQL query to drive the questions. At the time of this writing, NetSuite does not support saving the query into the new Analytics Workbook or Datasets. However, our firm provides a license-free NetSuite SQL Query Tool to all of its clients to drive the question. Alternatively, consider downloading the tool offered by Tim Dietrich.

Click the image to see the query tool and its output.

As another consideration, our license-free Content Renderer Engine 2.0 with NetSuite SuiteQL will allow you to take this query and produce all kinds of reports and outputs. Stay tuned for yet another new tool that will allow you to draw SQL-based lists and sublists and attach them to native NetSuite forms.

Finally, if you have not activated the Vendor or Customer Center in your account, you can modify the query to remove query elements that are not applicable. I commented within the query definition to assist your interpretation.

SELECT 
		r.entityid,
		r.email, 
		COUNT(l.date) AS loginsuccesses, 
		MAX(l.date) AS lastlogin, 
		r.license 
FROM 
(

		SELECT employee.entityid, employee.email, role.id, role.centertype, CASE role.centertype
			WHEN 'PARTNER' THEN 'Free'
			WHEN 'CUSTOMER' THEN 'Free'
			WHEN 'VENDOR' THEN 'Free'
			WHEN 'EMPLOYEE' THEN 'Employee'
			ELSE 'Full'
			END AS License
		FROM employee
		INNER JOIN employeeRolesForSearch ON employee.id = employeeRolesForSearch.entity
			AND employee.giveaccess = 'T' AND employee.isinactive = 'F'
		INNER JOIN role ON employeeRolesForSearch.role = role.id 

		UNION

		SELECT customer.entityid, customer.email, role.id, role.centertype, CASE role.centertype
			WHEN 'PARTNER' THEN 'Free'
			WHEN 'CUSTOMER' THEN 'Free'
			WHEN 'VENDOR' THEN 'Free'
			WHEN 'EMPLOYEE' THEN 'Employee'
			ELSE 'Full'
			END AS License
		FROM customer
		INNER JOIN customerContactRole ON customerContactRole.entity = Customer.id
			/* remove next line if the Customer Center is not enabled */
			AND customer.giveaccess = 'T' AND customer.isinactive = 'F'
		INNER JOIN role ON role.id = customerContactRole.role 

		UNION

		SELECT vendor.entityid, vendor.email, role.id, role.centertype, CASE role.centertype
			WHEN 'PARTNER' THEN 'Free'
			WHEN 'CUSTOMER' THEN 'Free'
			WHEN 'VENDOR' THEN 'Free'
			WHEN 'EMPLOYEE' THEN 'Employee'
			ELSE 'Full'
			END AS License
		FROM vendor
		INNER JOIN VendorRolesForSearch ON vendor.id = VendorRolesForSearch.entity
			/* remove next line if the Vendor Center is not enabled */
			AND vendor.giveaccess = 'T' AND vendor.isinactive = 'F'
		INNER JOIN role ON VendorRolesForSearch.role = role.id

) r LEFT JOIN loginaudit l
	ON l.emailaddress = r.email AND l.role = r.id AND l.status = 'Success' 
GROUP BY r.entityid, r.email, r.license 
ORDER BY lastlogin DESC

Work with NetSuite Innovation Leaders

The work above is an illustration of our NetSuite expertise. We understand and strive to exploit NetSuite’s platform enhancement architecture. NetSuite did a great job modeling general business patterns with its transaction model (please see my 2011 article, How NetSuite Outperforms Salesforce.com for Generating Revenue, which continues to be relevant today). By providing an ability to extend NetSuite’s information and logic model means that we can intelligently address requirements innovating on the margins. The key is to possess the kind of mind that can listen to concerns and translate those into meaningful software-based solutions.

If you found this article relevant, feel free to sign up for notifications to new articles as I post them. If you are the kind of individual that has worked to develop NetSuite competency, understands what it means to be innovative, but feel underappreciated, perhaps you may want to join our professional team? Let’s have a conversation.

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube