Appian Usage Insights

Overview

Gain insights into your Appian usage with an application suite specifically designed to cater for complex environment topologies and heterogenous license contracts.

Key Features & Functionality

With ‘Appian Usage Insights’:

  • Model your license contracts in the application through configuration only - no additional development required!
  • Cater for Cloud, self-managed or a mix of installation topologies.
  • Designed with the enterprise in mind, support large numbers of Appian environments, user accounts and keep track of license consumption over time.
  • Model your internal license usage using license pools mapped to user-definable business entities.
  • Visualize usage the way you want to:
    • Usage by business entity.
    • Usage by license type.
    • License pool membership.
    • License pool logins.
    • License pool membership (which user is in which license pool and, ultimately, which one consumes an actual license).
    • Logins by user (over time).
    • Capacity planning (allocated license count over time).

For more information, see https://community.appian.com/w/the-appian-playbook/2198/appian-usage-insights-faqs

Usage of this application does not permit your organization to exceed its licensed usage requirements. Your organization is required to maintain compliance with your licensing terms, and report any non-compliance to Appian per the terms of your organization’s contract with Appian.

Anonymous
Parents Comment Children
  • I have one that seem working for me but not sure that all is fully correct

  • I'm not sure that all is correct but tha application seem working for me

    -- --------------------------------------------------------
    -- ********************************************************
    -- ********************************************************
    -- *** TABLES ***
    -- ********************************************************
    -- ********************************************************
    -- --------------------------------------------------------

    --
    -- Table structure for table LMA_ANALYSIS_RUN
    --

    DROP TABLE IF EXISTS LMA_ANALYSIS_RUN
    GO
    CREATE TABLE LMA_ANALYSIS_RUN (
    analysis_id int NOT NULL identity(1,1),
    collection_run_id int NOT NULL,
    started_by varchar(255) NOT NULL,
    started_on datetime NOT NULL,
    end_on datetime DEFAULT NULL,
    user_count int NOT NULL,
    status varchar(255) NOT NULL
    )
    GO

    -- --------------------------------------------------------

    --
    -- Table structure for table LMA_STG_CSV_AUDITED_LOGINS
    --

    DROP TABLE IF EXISTS LMA_STG_CSV_AUDITED_LOGINS
    GO
    CREATE TABLE LMA_STG_CSV_AUDITED_LOGINS (
    audited_login_id int NOT NULL identity(1,1),
    username varchar(255) NOT NULL,
    login datetime NOT NULL
    )
    GO

    -- --------------------------------------------------------

    --
    -- Table structure for table LMA_COLLECTION_RUN
    --

    DROP TABLE IF EXISTS LMA_COLLECTION_RUN
    GO
    CREATE TABLE LMA_COLLECTION_RUN (
    collection_run_id int NOT NULL identity(1,1),
    status varchar(50) DEFAULT NULL,
    collection_run_date date DEFAULT NULL,
    started_on datetime DEFAULT NULL,
    ended_on datetime DEFAULT NULL
    )
    GO

    -- --------------------------------------------------------

    --
    -- Table structure for table LMA_ENTITY
    --

    DROP TABLE IF EXISTS LMA_ENTITY
    GO
    CREATE TABLE LMA_ENTITY (
    entity_id int NOT NULL identity(1,1),
    entity_name varchar(255) DEFAULT NULL,
    entity_type varchar(255) DEFAULT NULL
    )
    GO

    -- --------------------------------------------------------

    --
    -- Table structure for table LMA_ENTITY_LICENCE_POOL
    --

    DROP TABLE IF EXISTS LMA_ENTITY_LICENCE_POOL
    GO
    CREATE TABLE LMA_ENTITY_LICENCE_POOL (
    entity_licence_pool_id int NOT NULL identity(1,1),
    entity_id int DEFAULT NULL,
    licence_type_id int DEFAULT NULL,
    number_of_licences int DEFAULT NULL
    )
    GO

    -- --------------------------------------------------------

    --
    -- Table structure for table LMA_ENTITY_LICENCE_POOL_GROUP
    --

    DROP TABLE IF EXISTS LMA_ENTITY_LICENCE_POOL_GROUP
    GO
    CREATE TABLE LMA_ENTITY_LICENCE_POOL_GROUP (
    entity_licence_pool_group_id int NOT NULL identity(1,1),
    entity_licence_pool_id int DEFAULT NULL,
    group_id int DEFAULT NULL
    )
    GO

    -- --------------------------------------------------------

    --
    -- Table structure for table LMA_ENVIRONMENT
    --

    DROP TABLE IF EXISTS LMA_ENVIRONMENT
    GO
    CREATE TABLE LMA_ENVIRONMENT (
    environment_id int NOT NULL identity(1,1),
    environment_name varchar(255) DEFAULT NULL,
    environment_description varchar(5000) DEFAULT NULL,
    -- LMA-48
    is_active tinyint DEFAULT 1
    )
    GO

    -- --------------------------------------------------------


    -- --------------------------------------------------------

    --
    -- Table structure for table LMA_ENVIRONMENT_USER
    --

    DROP TABLE IF EXISTS LMA_ENVIRONMENT_USER
    GO
    CREATE TABLE LMA_ENVIRONMENT_USER (
    environment_user_id int NOT NULL identity(1,1),
    username varchar(255) DEFAULT NULL,
    user_uuid varchar(42) DEFAULT NULL,
    collection_run_id int DEFAULT NULL,
    environment_id int DEFAULT NULL
    )
    GO

    -- --------------------------------------------------------

    --
    -- Table structure for table LMA_ENVIRONMENT_USER_GROUP
    --

    DROP TABLE IF EXISTS LMA_ENVIRONMENT_USER_GROUP
    GO
    CREATE TABLE LMA_ENVIRONMENT_USER_GROUP (
    environment_user_group_id int NOT NULL identity(1,1),
    username varchar(255) DEFAULT NULL,
    user_uuid varchar(42) DEFAULT NULL,
    group_name varchar(255) DEFAULT NULL,
    group_uuid varchar(255) DEFAULT NULL,
    collection_run_id int DEFAULT NULL,
    environment_id int DEFAULT NULL
    )
    GO

    -- --------------------------------------------------------

    --
    -- Table structure for table LMA_ENVIRONMENT_USER_LOGIN
    --

    DROP TABLE IF EXISTS LMA_ENVIRONMENT_USER_LOGIN
    GO
    CREATE TABLE LMA_ENVIRONMENT_USER_LOGIN (
    Environment_user_login_id int NOT NULL identity(1,1),
    username varchar(255) DEFAULT NULL,
    login datetime DEFAULT NULL,
    collection_run_id int DEFAULT NULL,
    environment_id int DEFAULT NULL
    )
    GO

    -- --------------------------------------------------------

    --
    -- Table structure for table LMA_GROUP
    --

    DROP TABLE IF EXISTS LMA_GROUP
    GO
    CREATE TABLE LMA_GROUP (
    group_id int NOT NULL identity(1,1),
    group_name varchar(255) DEFAULT NULL,
    group_uuid varchar(255) DEFAULT NULL,
    environment_id int DEFAULT NULL
    )
    GO

    -- --------------------------------------------------------

    --
    -- Table structure for table LMA_STG_CSV_GROUP_MEMBERSHIP
    --

    DROP TABLE IF EXISTS LMA_STG_CSV_GROUP_MEMBERSHIP
    GO
    CREATE TABLE LMA_STG_CSV_GROUP_MEMBERSHIP (
    group_membership_id int NOT NULL identity(1,1),
    username varchar(255) DEFAULT NULL,
    user_uuid varchar(255) DEFAULT NULL,
    group_name varchar(255) DEFAULT NULL,
    group_uuid varchar(255) DEFAULT NULL
    )
    GO

    -- --------------------------------------------------------

    --
    -- Table structure for table LMA_LICENCE_POOL_USER_COUNT
    --

    DROP TABLE IF EXISTS LMA_LICENCE_POOL_USER_COUNT
    GO
    CREATE TABLE LMA_LICENCE_POOL_USER_COUNT (
    licence_pool_user_count_id int NOT NULL identity(1,1),
    entity_licence_pool_id int DEFAULT NULL,
    user_count int DEFAULT NULL,
    collection_run_id int DEFAULT NULL
    )
    GO

    -- --------------------------------------------------------

    --
    -- Table structure for table LMA_LICENCE_TYPE
    --

    DROP TABLE IF EXISTS LMA_LICENCE_TYPE
    GO
    CREATE TABLE LMA_LICENCE_TYPE (
    licence_type_id int NOT NULL identity(1,1),
    licence_type_name varchar(255) DEFAULT NULL,
    licence_type_description varchar(5000) DEFAULT NULL,
    licence_type_value float DEFAULT NULL
    )
    GO

    -- --------------------------------------------------------

    --
    -- Table structure for table LMA_USER_ALLOCATED_LICENCE_POOL
    --

    DROP TABLE IF EXISTS LMA_USER_ALLOCATED_LICENCE_POOL
    GO
    CREATE TABLE LMA_USER_ALLOCATED_LICENCE_POOL (
    user_allocated_licence_pool_id int NOT NULL identity(1,1),
    entity_licence_pool_id int DEFAULT NULL,
    username varchar(255) DEFAULT NULL
    )
    GO

    -- --------------------------------------------------------

    --
    -- Table structure for table LMA_USER_POTENTIAL_LICENCE_POOL
    --

    DROP TABLE IF EXISTS LMA_USER_POTENTIAL_LICENCE_POOL
    GO
    CREATE TABLE LMA_USER_POTENTIAL_LICENCE_POOL (
    user_potential_licence_pool_id int NOT NULL identity(1,1),
    entity_licence_pool_id int DEFAULT NULL,
    username varchar(255) DEFAULT NULL
    )
    GO


    --
    -- Indexes for dumped tables
    --

    --
    -- Indexes for table LMA_ANALYSIS_RUN
    --
    ALTER TABLE LMA_ANALYSIS_RUN
    ADD PRIMARY KEY (analysis_id)
    GO

    --
    -- Indexes for table LMA_STG_CSV_AUDITED_LOGINS
    --
    ALTER TABLE LMA_STG_CSV_AUDITED_LOGINS
    ADD PRIMARY KEY (audited_login_id)
    GO

    --
    -- Indexes for table LMA_COLLECTION_RUN
    --
    ALTER TABLE LMA_COLLECTION_RUN
    ADD PRIMARY KEY (collection_run_id)
    GO

    --
    -- Indexes for table LMA_ENTITY
    --
    ALTER TABLE LMA_ENTITY
    ADD PRIMARY KEY (entity_id)
    GO

    --
    -- Indexes for table LMA_ENTITY_LICENCE_POOL
    --
    ALTER TABLE LMA_ENTITY_LICENCE_POOL
    ADD PRIMARY KEY (entity_licence_pool_id)
    GO
    CREATE NONCLUSTERED INDEX [fk_entity_license_entity_id] ON LMA_ENTITY_LICENCE_POOL
    (
    [entity_id] ASC
    )
    GO
    CREATE NONCLUSTERED INDEX fk_entity_license_type_id ON [dbo].LMA_ENTITY_LICENCE_POOL
    (
    [licence_type_id] ASC
    )
    GO
    --
    -- Indexes for table LMA_ENTITY_LICENCE_POOL_GROUP
    --
    ALTER TABLE LMA_ENTITY_LICENCE_POOL_GROUP
    ADD PRIMARY KEY (entity_licence_pool_group_id)
    --ADD KEY ix_elpg_licence_pool_id (entity_licence_pool_id),
    --ADD KEY ix_elpg_group_id (group_id)
    GO

    --
    -- Indexes for table LMA_ENVIRONMENT
    --
    ALTER TABLE LMA_ENVIRONMENT
    ADD PRIMARY KEY (environment_id)
    GO


    -- Indexes for table LMA_ENVIRONMENT_USER
    --
    ALTER TABLE LMA_ENVIRONMENT_USER
    ADD PRIMARY KEY (environment_user_id)
    --ADD KEY fk_eu_environment_id (environment_id),
    --ADD KEY ix_eu_username (environment_user_id),
    --ADD KEY ix_eu_collection_run_id (collection_run_id)
    GO

    --
    -- Indexes for table LMA_ENVIRONMENT_USER_GROUP
    --
    ALTER TABLE LMA_ENVIRONMENT_USER_GROUP
    ADD PRIMARY KEY (environment_user_group_id)
    --ADD KEY fk_eug_environment_id (environment_id),
    --ADD KEY fk_eug_collection_run_id (collection_run_id),
    --ADD INDEX ix_group_name (group_name),
    --ADD INDEX ix_username (username)
    GO

    --
    -- Indexes for table LMA_ENVIRONMENT_USER_LOGIN
    --
    ALTER TABLE LMA_ENVIRONMENT_USER_LOGIN
    ADD PRIMARY KEY (Environment_user_login_id)
    --ADD KEY fk_eul_environment_id (environment_id),
    --ADD KEY fk_eul_collection_run_id (collection_run_id),
    --ADD INDEX ix_username (username)
    GO

    --ALTER TABLE LMA_ENVIRONMENT_USER_LOGIN ADD INDEX ix_login (login)
    --GO

    --
    -- Indexes for table LMA_GROUP
    --
    ALTER TABLE LMA_GROUP
    ADD PRIMARY KEY (group_id)
    --ADD KEY lma_fk_group_environment_id (environment_id),
    --ADD KEY ix_g_group_uuid (group_uuid) USING BTREE
    GO

    --
    -- Indexes for table LMA_STG_CSV_GROUP_MEMBERSHIP
    --
    ALTER TABLE LMA_STG_CSV_GROUP_MEMBERSHIP
    ADD PRIMARY KEY (group_membership_id)
    --ADD KEY ix_gm_group_uuid (group_uuid),
    --ADD KEY ix_gm_username (username) USING BTREE
    GO

    --
    -- Indexes for table LMA_LICENCE_POOL_USER_COUNT
    --
    ALTER TABLE LMA_LICENCE_POOL_USER_COUNT
    ADD PRIMARY KEY (licence_pool_user_count_id)
    --ADD KEY fk_lpuc_collection_run_id (collection_run_id),
    --ADD KEY fk_lpuc_entity_licence_pool_id (entity_licence_pool_id)
    GO

    --
    -- Indexes for table LMA_LICENCE_TYPE
    --
    ALTER TABLE LMA_LICENCE_TYPE
    ADD PRIMARY KEY (licence_type_id)
    GO

    --
    -- Indexes for table LMA_USER_ALLOCATED_LICENCE_POOL
    --
    ALTER TABLE LMA_USER_ALLOCATED_LICENCE_POOL
    ADD PRIMARY KEY (user_allocated_licence_pool_id)
    --ADD KEY fk_ualp_entity_licence_pool_id (entity_licence_pool_id),
    --ADD INDEX ix_username (username)
    GO

    --
    -- Indexes for table LMA_USER_POTENTIAL_LICENCE_POOL
    --
    ALTER TABLE LMA_USER_POTENTIAL_LICENCE_POOL
    ADD PRIMARY KEY (user_potential_licence_pool_id)
    --ADD KEY fk_uplp_entity_licence_pool_id (entity_licence_pool_id),
    --ADD INDEX ix_username (username)
    GO

    -- Constraints for dumped tables
    --

    --
    -- Constraints for table LMA_ENTITY_LICENCE_POOL
    --
    ALTER TABLE LMA_ENTITY_LICENCE_POOL
    ADD CONSTRAINT fk_entity_license_entity_id FOREIGN KEY (entity_id) REFERENCES LMA_ENTITY (entity_id) ON DELETE NO ACTION ON UPDATE NO ACTION
    ALTER TABLE LMA_ENTITY_LICENCE_POOL
    ADD CONSTRAINT fk_entity_license_type_id FOREIGN KEY (licence_type_id) REFERENCES LMA_LICENCE_TYPE (licence_type_id) ON DELETE NO ACTION ON UPDATE NO ACTION
    GO

    --
    -- Constraints for table LMA_ENTITY_LICENCE_POOL_GROUP
    --
    ALTER TABLE LMA_ENTITY_LICENCE_POOL_GROUP
    ADD CONSTRAINT fk_elpg_group_id FOREIGN KEY (group_id) REFERENCES LMA_GROUP (group_id) ON DELETE NO ACTION ON UPDATE NO ACTION
    ALTER TABLE LMA_ENTITY_LICENCE_POOL_GROUP
    ADD CONSTRAINT fk_elpg_license_pool_id FOREIGN KEY (entity_licence_pool_id) REFERENCES LMA_ENTITY_LICENCE_POOL (entity_licence_pool_id) ON DELETE NO ACTION ON UPDATE NO ACTION
    GO

    --
    -- Constraints for table LMA_ENVIRONMENT_USER
    --
    ALTER TABLE LMA_ENVIRONMENT_USER
    ADD CONSTRAINT fk_eu_collection_run_id FOREIGN KEY (collection_run_id) REFERENCES LMA_COLLECTION_RUN (collection_run_id) ON DELETE NO ACTION ON UPDATE NO ACTION
    ALTER TABLE LMA_ENVIRONMENT_USER
    ADD CONSTRAINT fk_eu_environment_id FOREIGN KEY (environment_id) REFERENCES LMA_ENVIRONMENT (environment_id) ON DELETE NO ACTION ON UPDATE NO ACTION
    GO

    --
    -- Constraints for table LMA_ENVIRONMENT_USER_GROUP
    --
    ALTER TABLE LMA_ENVIRONMENT_USER_GROUP
    ADD CONSTRAINT fk_eug_collection_run_id FOREIGN KEY (collection_run_id) REFERENCES LMA_COLLECTION_RUN (collection_run_id) ON DELETE NO ACTION ON UPDATE NO ACTION
    ALTER TABLE LMA_ENVIRONMENT_USER_GROUP
    ADD CONSTRAINT fk_eug_environment_id FOREIGN KEY (environment_id) REFERENCES LMA_ENVIRONMENT (environment_id) ON DELETE NO ACTION ON UPDATE NO ACTION
    GO

    --
    -- Constraints for table LMA_ENVIRONMENT_USER_LOGIN
    --
    ALTER TABLE LMA_ENVIRONMENT_USER_LOGIN
    ADD CONSTRAINT fk_eul_collection_run_id FOREIGN KEY (collection_run_id) REFERENCES LMA_COLLECTION_RUN (collection_run_id) ON DELETE NO ACTION ON UPDATE NO ACTION
    ALTER TABLE LMA_ENVIRONMENT_USER_LOGIN
    ADD CONSTRAINT fk_eul_environment_id FOREIGN KEY (environment_id) REFERENCES LMA_ENVIRONMENT (environment_id) ON DELETE NO ACTION ON UPDATE NO ACTION
    GO

    --
    -- Constraints for table LMA_GROUP
    --
    ALTER TABLE LMA_GROUP
    ADD CONSTRAINT lma_fk_group_environment_id FOREIGN KEY (environment_id) REFERENCES LMA_ENVIRONMENT (environment_id) ON DELETE NO ACTION ON UPDATE NO ACTION
    GO

    --
    -- Constraints for table LMA_LICENCE_POOL_USER_COUNT
    --
    ALTER TABLE LMA_LICENCE_POOL_USER_COUNT
    ADD CONSTRAINT fk_lpuc_collection_run_id FOREIGN KEY (collection_run_id) REFERENCES LMA_COLLECTION_RUN (collection_run_id) ON DELETE NO ACTION ON UPDATE NO ACTION
    ALTER TABLE LMA_LICENCE_POOL_USER_COUNT
    ADD CONSTRAINT fk_lpuc_entity_licence_pool_id FOREIGN KEY (entity_licence_pool_id) REFERENCES LMA_ENTITY_LICENCE_POOL (entity_licence_pool_id) ON DELETE NO ACTION ON UPDATE NO ACTION
    GO

    --
    -- Constraints for table LMA_USER_ALLOCATED_LICENCE_POOL
    --
    ALTER TABLE LMA_USER_ALLOCATED_LICENCE_POOL
    ADD CONSTRAINT fk_ualp_entity_licence_pool_id FOREIGN KEY (entity_licence_pool_id) REFERENCES LMA_ENTITY_LICENCE_POOL (entity_licence_pool_id) ON DELETE NO ACTION ON UPDATE NO ACTION
    GO

    --
    -- Constraints for table LMA_USER_POTENTIAL_LICENCE_POOL
    --
    ALTER TABLE LMA_USER_POTENTIAL_LICENCE_POOL
    ADD CONSTRAINT fk_uplp_entity_licence_pool_id FOREIGN KEY (entity_licence_pool_id) REFERENCES LMA_ENTITY_LICENCE_POOL (entity_licence_pool_id) ON DELETE NO ACTION ON UPDATE NO ACTION
    GO

    -- --------------------------------------------------------

    --
    -- Table structure for table LMA_DEFAULT_LICENCE_POOL_GROUP
    --

    DROP TABLE IF EXISTS LMA_DEFAULT_LICENCE_POOL_GROUP
    GO
    CREATE TABLE LMA_DEFAULT_LICENCE_POOL_GROUP (
    row_id int NOT NULL identity(1,1),
    group_name varchar(255) DEFAULT NULL
    )
    GO

    --
    -- Indexes for dumped tables
    --

    --
    -- Indexes for table LMA_DEFAULT_LICENCE_POOL_GROUP
    --
    ALTER TABLE LMA_DEFAULT_LICENCE_POOL_GROUP
    ADD PRIMARY KEY (row_id)
    --ADD KEY group_name (group_name)
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_entity_licence_pool' AND sc.name = N'dbo' AND si.name = N'fk_entity_license_entity_id' AND so.type in (N'U'))
    DROP INDEX [fk_entity_license_entity_id] ON [dbo].[lma_entity_licence_pool]
    GO
    CREATE NONCLUSTERED INDEX [fk_entity_license_entity_id] ON [dbo].[lma_entity_licence_pool]
    (
    [entity_id] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    GO

    USE LMA
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_entity_licence_pool' AND sc.name = N'dbo' AND si.name = N'fk_entity_license_type_id' AND so.type in (N'U'))
    DROP INDEX [fk_entity_license_type_id] ON [dbo].[lma_entity_licence_pool]
    GO
    CREATE NONCLUSTERED INDEX [fk_entity_license_type_id] ON [dbo].[lma_entity_licence_pool]
    (
    [licence_type_id] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    GO

    USE LMA
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_environment_user' AND sc.name = N'dbo' AND si.name = N'fk_eu_environment_id' AND so.type in (N'U'))
    DROP INDEX [fk_eu_environment_id] ON [dbo].[lma_environment_user]
    GO
    CREATE NONCLUSTERED INDEX [fk_eu_environment_id] ON [dbo].[lma_environment_user]
    (
    [environment_id] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    GO

    USE LMA
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_environment_user_group' AND sc.name = N'dbo' AND si.name = N'fk_eug_collection_run_id' AND so.type in (N'U'))
    DROP INDEX [fk_eug_collection_run_id] ON [dbo].[lma_environment_user_group]
    GO
    CREATE NONCLUSTERED INDEX [fk_eug_collection_run_id] ON [dbo].[lma_environment_user_group]
    (
    [collection_run_id] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    GO

    USE LMA
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_environment_user_group' AND sc.name = N'dbo' AND si.name = N'fk_eug_environment_id' AND so.type in (N'U'))
    DROP INDEX [fk_eug_environment_id] ON [dbo].[lma_environment_user_group]
    GO
    CREATE NONCLUSTERED INDEX [fk_eug_environment_id] ON [dbo].[lma_environment_user_group]
    (
    [environment_id] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    GO

    USE LMA
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_environment_user_login' AND sc.name = N'dbo' AND si.name = N'fk_eul_collection_run_id' AND so.type in (N'U'))
    DROP INDEX [fk_eul_collection_run_id] ON [dbo].[lma_environment_user_login]
    GO
    CREATE NONCLUSTERED INDEX [fk_eul_collection_run_id] ON [dbo].[lma_environment_user_login]
    (
    [collection_run_id] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    GO

    USE LMA
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_environment_user_login' AND sc.name = N'dbo' AND si.name = N'fk_eul_environment_id' AND so.type in (N'U'))
    DROP INDEX [fk_eul_environment_id] ON [dbo].[lma_environment_user_login]
    GO
    CREATE NONCLUSTERED INDEX [fk_eul_environment_id] ON [dbo].[lma_environment_user_login]
    (
    [environment_id] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    GO

    USE LMA
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_licence_pool_user_count' AND sc.name = N'dbo' AND si.name = N'fk_lpuc_collection_run_id' AND so.type in (N'U'))
    DROP INDEX [fk_lpuc_collection_run_id] ON [dbo].[lma_licence_pool_user_count]
    GO
    CREATE NONCLUSTERED INDEX [fk_lpuc_collection_run_id] ON [dbo].[lma_licence_pool_user_count]
    (
    [collection_run_id] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    GO

    USE LMA
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_licence_pool_user_count' AND sc.name = N'dbo' AND si.name = N'fk_lpuc_entity_licence_pool_id' AND so.type in (N'U'))
    DROP INDEX [fk_lpuc_entity_licence_pool_id] ON [dbo].[lma_licence_pool_user_count]
    GO
    CREATE NONCLUSTERED INDEX [fk_lpuc_entity_licence_pool_id] ON [dbo].[lma_licence_pool_user_count]
    (
    [entity_licence_pool_id] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    GO

    USE LMA
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_user_allocated_licence_pool' AND sc.name = N'dbo' AND si.name = N'fk_ualp_entity_licence_pool_id' AND so.type in (N'U'))
    DROP INDEX [fk_ualp_entity_licence_pool_id] ON [dbo].[lma_user_allocated_licence_pool]
    GO
    CREATE NONCLUSTERED INDEX [fk_ualp_entity_licence_pool_id] ON [dbo].[lma_user_allocated_licence_pool]
    (
    [entity_licence_pool_id] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    GO

    USE LMA
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_user_potential_licence_pool' AND sc.name = N'dbo' AND si.name = N'fk_uplp_entity_licence_pool_id' AND so.type in (N'U'))
    DROP INDEX [fk_uplp_entity_licence_pool_id] ON [dbo].[lma_user_potential_licence_pool]
    GO
    CREATE NONCLUSTERED INDEX [fk_uplp_entity_licence_pool_id] ON [dbo].[lma_user_potential_licence_pool]
    (
    [entity_licence_pool_id] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    GO

    USE LMA
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_default_licence_pool_group' AND sc.name = N'dbo' AND si.name = N'group_name' AND so.type in (N'U'))
    DROP INDEX [group_name] ON [dbo].[lma_default_licence_pool_group]
    GO
    CREATE NONCLUSTERED INDEX [group_name] ON [dbo].[lma_default_licence_pool_group]
    (
    [group_name] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    GO

    USE LMA
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_entity_licence_pool_group' AND sc.name = N'dbo' AND si.name = N'ix_elpg_group_id' AND so.type in (N'U'))
    DROP INDEX [ix_elpg_group_id] ON [dbo].[lma_entity_licence_pool_group]
    GO
    CREATE NONCLUSTERED INDEX [ix_elpg_group_id] ON [dbo].[lma_entity_licence_pool_group]
    (
    [group_id] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    GO

    USE LMA
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_entity_licence_pool_group' AND sc.name = N'dbo' AND si.name = N'ix_elpg_licence_pool_id' AND so.type in (N'U'))
    DROP INDEX [ix_elpg_licence_pool_id] ON [dbo].[lma_entity_licence_pool_group]
    GO
    CREATE NONCLUSTERED INDEX [ix_elpg_licence_pool_id] ON [dbo].[lma_entity_licence_pool_group]
    (
    [entity_licence_pool_id] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    GO

    USE LMA
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_environment_user' AND sc.name = N'dbo' AND si.name = N'ix_eu_collection_run_id' AND so.type in (N'U'))
    DROP INDEX [ix_eu_collection_run_id] ON [dbo].[lma_environment_user]
    GO
    CREATE NONCLUSTERED INDEX [ix_eu_collection_run_id] ON [dbo].[lma_environment_user]
    (
    [collection_run_id] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    GO

    USE LMA
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_environment_user' AND sc.name = N'dbo' AND si.name = N'ix_eu_username' AND so.type in (N'U'))
    DROP INDEX [ix_eu_username] ON [dbo].[lma_environment_user]
    GO
    CREATE NONCLUSTERED INDEX [ix_eu_username] ON [dbo].[lma_environment_user]
    (
    [environment_user_id] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    GO

    USE LMA
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_group' AND sc.name = N'dbo' AND si.name = N'ix_g_group_uuid' AND so.type in (N'U'))
    DROP INDEX [ix_g_group_uuid] ON [dbo].[lma_group]
    GO
    CREATE NONCLUSTERED INDEX [ix_g_group_uuid] ON [dbo].[lma_group]
    (
    [group_uuid] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    GO

    USE LMA
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_stg_csv_group_membership' AND sc.name = N'dbo' AND si.name = N'ix_gm_group_uuid' AND so.type in (N'U'))
    DROP INDEX [ix_gm_group_uuid] ON [dbo].[lma_stg_csv_group_membership]
    GO
    CREATE NONCLUSTERED INDEX [ix_gm_group_uuid] ON [dbo].[lma_stg_csv_group_membership]
    (
    [group_uuid] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    GO

    USE LMA
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_stg_csv_group_membership' AND sc.name = N'dbo' AND si.name = N'ix_gm_username' AND so.type in (N'U'))
    DROP INDEX [ix_gm_username] ON [dbo].[lma_stg_csv_group_membership]
    GO
    CREATE NONCLUSTERED INDEX [ix_gm_username] ON [dbo].[lma_stg_csv_group_membership]
    (
    [username] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    GO

    USE LMA
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_environment_user_group' AND sc.name = N'dbo' AND si.name = N'ix_group_name' AND so.type in (N'U'))
    DROP INDEX [ix_group_name] ON [dbo].[lma_environment_user_group]
    GO
    CREATE NONCLUSTERED INDEX [ix_group_name] ON [dbo].[lma_environment_user_group]
    (
    [group_name] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    GO

    USE LMA
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_environment_user_login' AND sc.name = N'dbo' AND si.name = N'ix_login' AND so.type in (N'U'))
    DROP INDEX [ix_login] ON [dbo].[lma_environment_user_login]
    GO
    CREATE NONCLUSTERED INDEX [ix_login] ON [dbo].[lma_environment_user_login]
    (
    [login] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    GO

    USE LMA
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_environment_user_login' AND sc.name = N'dbo' AND si.name = N'ix_username' AND so.type in (N'U'))
    DROP INDEX [ix_username] ON [dbo].[lma_environment_user_login]
    GO
    CREATE NONCLUSTERED INDEX [ix_username] ON [dbo].[lma_environment_user_login]
    (
    [username] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    GO

    USE LMA
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_environment_user_group' AND sc.name = N'dbo' AND si.name = N'ix_username' AND so.type in (N'U'))
    DROP INDEX [ix_username] ON [dbo].[lma_environment_user_group]
    GO
    CREATE NONCLUSTERED INDEX [ix_username] ON [dbo].[lma_environment_user_group]
    (
    [username] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    GO

    USE LMA
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_user_potential_licence_pool' AND sc.name = N'dbo' AND si.name = N'ix_username' AND so.type in (N'U'))
    DROP INDEX [ix_username] ON [dbo].[lma_user_potential_licence_pool]
    GO
    CREATE NONCLUSTERED INDEX [ix_username] ON [dbo].[lma_user_potential_licence_pool]
    (
    [username] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    GO

    USE LMA
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_user_allocated_licence_pool' AND sc.name = N'dbo' AND si.name = N'ix_username' AND so.type in (N'U'))
    DROP INDEX [ix_username] ON [dbo].[lma_user_allocated_licence_pool]
    GO
    CREATE NONCLUSTERED INDEX [ix_username] ON [dbo].[lma_user_allocated_licence_pool]
    (
    [username] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    GO

    USE LMA
    GO
    IF EXISTS (
    SELECT * FROM sys.objects so JOIN sys.indexes si
    ON so.object_id = si.object_id
    JOIN sys.schemas sc
    ON so.schema_id = sc.schema_id
    WHERE so.name = N'lma_group' AND sc.name = N'dbo' AND si.name = N'lma_fk_group_environment_id' AND so.type in (N'U'))
    DROP INDEX [lma_fk_group_environment_id] ON [dbo].[lma_group]
    GO
    CREATE NONCLUSTERED INDEX [lma_fk_group_environment_id] ON [dbo].[lma_group]
    (
    [environment_id] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    GO
    -- --------------------------------------------------------
    -- ********************************************************
    -- ********************************************************
    -- *** DATA ***
    -- ********************************************************
    -- ********************************************************
    -- --------------------------------------------------------

    INSERT INTO LMA_ENTITY (entity_id, entity_name, entity_type) VALUES
    (1, 'Default', 'Default Entity for Unassigned Licences')
    GO

    --
    -- Dumping data for table LMA_LICENCE_TYPE
    --

    INSERT INTO LMA_LICENCE_TYPE (licence_type_id, licence_type_name, licence_type_description, licence_type_value) VALUES
    (1, 'Default', 'Default', 0)
    GO

    --
    -- Dumping data for table LMA_ENTITY_LICENCE_POOL
    --

    INSERT INTO LMA_ENTITY_LICENCE_POOL (entity_licence_pool_id, entity_id, licence_type_id, number_of_licences) VALUES
    (1, 1, 1, 0)
    GO

    -- --------------------------------------------------------
    -- ********************************************************
    -- ********************************************************
    -- *** FUNCTIONS ***
    -- ********************************************************
    -- ********************************************************
    -- --------------------------------------------------------
    -- ----------------------------------------------------------------------------------------------------------------------------------------------------------
    -- FUNCTIONS
    -- ----------------------------------------------------------------------------------------------------------------------------------------------------------


    CREATE FUNCTION BigToInt(@n BIGINT) RETURNS int as
    BEGIN
    RETURN @n
    END
    GO
    -- --------------------------------------------------------
    -- ********************************************************
    -- ********************************************************
    -- *** VIEWS ***
    -- ********************************************************
    -- ********************************************************
    -- --------------------------------------------------------

    -- ----------------------------------------------------------------------------------------------------------------------------------------------------------
    -- VIEWS
    -- ----------------------------------------------------------------------------------------------------------------------------------------------------------
    -- --------------------------------------------------------

    -- --------------------------------------------------------
    --
    -- Structure for view LMA_V_COMPLIANCE_BY_ENTITY
    --

    create view LMA_V_COMPLIANCE_BY_ENTITY as

    SELECT concat(e.entity_id, '|', t.licence_type_id) as rowId,
    e.entity_name,
    t.licence_type_name,
    cast(ISNULL(lpuc.user_count, 0) as int) as allocated_licences,
    cast(ISNULL(p.number_of_licences, 0) as int) as licences_available,
    e.entity_id, t.licence_type_id, p.entity_licence_pool_id, lpuc.collection_run_id
    FROM LMA_ENTITY e
    join LMA_ENTITY_LICENCE_POOL p
    on e.entity_id = p.entity_id
    join LMA_LICENCE_TYPE t
    on t.licence_type_id = p.licence_type_id
    left join LMA_LICENCE_POOL_USER_COUNT lpuc
    on p.entity_licence_pool_id = lpuc.entity_licence_pool_id
    and lpuc.collection_run_id = (select max(collection_run_id) from LMA_COLLECTION_RUN)
    GO

    -- --------------------------------------------------------
    --
    -- Structure for view LMA_V_USERS_BY_ALLOCATED_ENTITY
    --

    create view LMA_V_USERS_BY_ALLOCATED_ENTITY as

    SELECT ul.username,
    (
    SELECT MAX(l.login) AS last_login
    FROM LMA_ENVIRONMENT_USER_LOGIN l
    WHERE l.username = ul.username
    ) as last_login,
    t.licence_type_name,
    e.entity_name,
    e.entity_id, t.licence_type_id, p.entity_licence_pool_id
    FROM LMA_USER_ALLOCATED_LICENCE_POOL ul
    left join LMA_ENTITY_LICENCE_POOL p
    on p.entity_licence_pool_id = ul.entity_licence_pool_id
    left join LMA_ENTITY e
    on e.entity_id = p.entity_id
    left join LMA_LICENCE_TYPE t
    on t.licence_type_id = p.licence_type_id
    GO

    -- --------------------------------------------------------
    --
    -- Structure for view LMA_V_LICENCE_POOL_ENVIRONMENT_USERS
    --

    CREATE VIEW LMA_V_LICENCE_POOL_ENVIRONMENT_USERS AS

    SELECT
    DISTINCT
    plp.username AS username,
    -- plp.user_potential_licence_pool_id AS id,
    lt.licence_type_id AS licence_type_id,
    lt.licence_type_name AS licence_type_name,
    env.environment_id AS environment_id,
    env.environment_name AS environment_name,
    (SELECT MAX(ll.login) FROM LMA_ENVIRONMENT_USER_LOGIN ll WHERE ll.username = plp.username) AS last_login
    FROM LMA_USER_POTENTIAL_LICENCE_POOL plp
    join LMA_ENTITY_LICENCE_POOL elp
    on elp.entity_licence_pool_id = plp.entity_licence_pool_id
    join LMA_LICENCE_TYPE lt
    on lt.licence_type_id = elp.licence_type_id
    join LMA_ENVIRONMENT_USER eu
    on eu.username = plp.username
    join LMA_ENVIRONMENT env
    on env.environment_id = eu.environment_id
    GO

    -- --------------------------------------------------------
    -- ********************************************************
    -- ********************************************************
    -- *** STORED PROCEDURES ***
    -- ********************************************************
    -- ********************************************************
    -- --------------------------------------------------------
    -- ----------------------------------------------------------------------------------------------------------------------------------------------------------
    -- STORED PROCEDURES
    -- ----------------------------------------------------------------------------------------------------------------------------------------------------------

    DROP PROCEDURE IF EXISTS LMA_SP_POPULATE_USER_ALLOCATED_LICENCE_POOL
    GO
    CREATE PROCEDURE LMA_SP_POPULATE_USER_ALLOCATED_LICENCE_POOL
    AS
    BEGIN

    -- Work out everyone's allocated licence pool
    INSERT INTO LMA_USER_ALLOCATED_LICENCE_POOL(username, entity_licence_pool_id)
    SELECT alp.username, max(elp.entity_licence_pool_id) as entity_licence_pool_id
    FROM LMA_USER_POTENTIAL_LICENCE_POOL lp
    JOIN LMA_ENTITY_LICENCE_POOL elp
    on elp.entity_licence_pool_id=lp.entity_licence_pool_id
    JOIN LMA_LICENCE_TYPE lt
    on lt.licence_type_id=elp.licence_type_id
    JOIN (
    SELECT distinct lp.username,
    max(lt.licence_type_value) as licence_type_value
    FROM LMA_USER_POTENTIAL_LICENCE_POOL lp
    JOIN LMA_ENTITY_LICENCE_POOL elp
    on elp.entity_licence_pool_id=lp.entity_licence_pool_id
    JOIN LMA_LICENCE_TYPE lt
    on lt.licence_type_id=elp.licence_type_id
    group by lp.username
    ) alp on alp.licence_type_value=lt.licence_type_value and alp.username=lp.username
    group by alp.username;


    -- now update the list of groups where users have fallen into the default licence pool
    insert into LMA_DEFAULT_LICENCE_POOL_GROUP (group_name)
    select distinct(g.group_name)
    from LMA_ENVIRONMENT_USER_GROUP g
    where exists (
    SELECT 1
    FROM LMA_USER_ALLOCATED_LICENCE_POOL dp
    WHERE dp.entity_licence_pool_id = 1
    and dp.username = g.username
    );

    END
    --
    -- LMA_SP_CLEAN_UP_ENVIRONMENT_PACKAGE_DATA
    -- clean up the data from the last files we uploaded
    --
    DROP PROCEDURE IF EXISTS LMA_R_SP_CLEAN_UP_STAGING_CSV_DATA
    GO
    CREATE PROCEDURE LMA_R_SP_CLEAN_UP_STAGING_CSV_DATA
    AS
    BEGIN

    TRUNCATE TABLE LMA_STG_CSV_AUDITED_LOGINS

    TRUNCATE TABLE LMA_STG_CSV_GROUP_MEMBERSHIP

    END

    GO

    --
    -- LMA_SP_CLEAN_UP_COLLECTION_RUN_DATA
    -- clean up data for tables where we only want the latest collection data for analysis
    --
    DROP PROCEDURE IF EXISTS LMA_SP_CLEAN_UP_COLLECTION_RUN_DATA
    GO

    CREATE PROCEDURE LMA_SP_CLEAN_UP_COLLECTION_RUN_DATA
    AS
    BEGIN


    TRUNCATE TABLE LMA_ENVIRONMENT_USER

    TRUNCATE TABLE LMA_ENVIRONMENT_USER_GROUP

    END

    GO

    --
    -- LMA_SP_CLEAN_UP_STAGING_ANALYSIS_DATA
    -- clean up the data for tables where we only want the latest analysis data for reporting
    --
    DROP PROCEDURE IF EXISTS LMA_SP_CLEAN_UP_STAGING_ANALYSIS_DATA
    GO

    CREATE PROCEDURE LMA_SP_CLEAN_UP_STAGING_ANALYSIS_DATA
    AS
    BEGIN

    TRUNCATE TABLE LMA_USER_ALLOCATED_LICENCE_POOL
    TRUNCATE TABLE LMA_USER_POTENTIAL_LICENCE_POOL

    TRUNCATE TABLE LMA_DEFAULT_LICENCE_POOL_GROUP

    END

    GO
    --
    -- LMA_SP_CONSOLIDATE_GROUPS
    --
    DROP PROCEDURE IF EXISTS LMA_SP_CONSOLIDATE_GROUPS
    GO


    CREATE PROCEDURE LMA_SP_CONSOLIDATE_GROUPS(@environment_id INT)
    AS
    BEGIN
    INSERT INTO LMA_GROUP (group_name, group_uuid, environment_id)
    SELECT
    DISTINCT
    eg.group_name,
    eg.group_uuid,
    eg.environment_id
    FROM LMA_ENVIRONMENT_USER_GROUP eg WHERE
    eg.environment_id = environment_id
    AND NOT EXISTS(
    select 1
    from LMA_GROUP g
    where g.group_uuid = eg.group_uuid
    and g.environment_id = environment_id
    )
    END

    GO

    --
    -- LMA_SP_POPULATE_ENVIRONMENT_DATA
    --
    DROP PROCEDURE IF EXISTS LMA_SP_POPULATE_ENVIRONMENT_DATA
    GO


    CREATE PROCEDURE LMA_SP_POPULATE_ENVIRONMENT_DATA(@collection_run_id INT, @environment_id INT)
    AS
    BEGIN

    INSERT INTO LMA_ENVIRONMENT_USER(username, user_uuid, collection_run_id, environment_id)
    SELECT distinct username,user_uuid, @collection_run_id, @environment_id
    FROM LMA_STG_CSV_GROUP_MEMBERSHIP

    -- Append new logins to existing set
    INSERT INTO LMA_ENVIRONMENT_USER_LOGIN(username, login, collection_run_id, environment_id)
    SELECT username,login, @collection_run_id, @environment_id
    FROM LMA_STG_CSV_AUDITED_LOGINS


    INSERT INTO LMA_ENVIRONMENT_USER_GROUP(username, user_uuid, group_name, group_uuid, collection_run_id, environment_id)
    SELECT username,user_uuid, group_name,group_uuid, @collection_run_id, @environment_id
    FROM LMA_STG_CSV_GROUP_MEMBERSHIP

    END

    GO


    --
    -- LMA_SP_POPULATE_LICENCE_POOL_USER_COUNT
    --
    DROP PROCEDURE IF EXISTS LMA_SP_POPULATE_LICENCE_POOL_USER_COUNT
    GO

    CREATE PROCEDURE LMA_SP_POPULATE_LICENCE_POOL_USER_COUNT
    @collection_id int = 0
    AS
    BEGIN
    DELETE FROM LMA_LICENCE_POOL_USER_COUNT
    WHERE collection_run_id=@collection_id

    INSERT INTO LMA_LICENCE_POOL_USER_COUNT(entity_licence_pool_id, user_count, collection_run_id)
    SELECT entity_licence_pool_id, count(user_allocated_licence_pool_id) as user_count, @collection_id
    FROM LMA_USER_ALLOCATED_LICENCE_POOL
    group by entity_licence_pool_id

    END

    GO


    --
    -- LMA_SP_POPULATE_USER_ALLOCATED_LICENCE_POOL
    --
    DROP PROCEDURE IF EXISTS LMA_SP_POPULATE_USER_ALLOCATED_LICENCE_POOL
    GO

    CREATE PROCEDURE LMA_SP_POPULATE_USER_ALLOCATED_LICENCE_POOL
    AS
    BEGIN

    -- Work out everyone's allocated licence pool
    INSERT INTO LMA_USER_ALLOCATED_LICENCE_POOL(username, entity_licence_pool_id)
    SELECT alp.username, max(elp.entity_licence_pool_id) as entity_licence_pool_id
    FROM LMA_USER_POTENTIAL_LICENCE_POOL lp
    JO@LMA_ENTITY_LICENCE_POOL elp
    on elp.entity_licence_pool_id=lp.entity_licence_pool_id
    JO@LMA_LICENCE_TYPE lt
    on lt.licence_type_id=elp.licence_type_id
    JO@(
    SELECT distinct lp.username,
    max(lt.licence_type_value) as licence_type_value
    FROM LMA_USER_POTENTIAL_LICENCE_POOL lp
    JO@LMA_ENTITY_LICENCE_POOL elp
    on elp.entity_licence_pool_id=lp.entity_licence_pool_id
    JO@LMA_LICENCE_TYPE lt
    on lt.licence_type_id=elp.licence_type_id
    group by lp.username
    ) alp on alp.licence_type_value=lt.licence_type_value and alp.username=lp.username
    group by alp.username

    -- now update the list of groups where users have fallen into the default licence pool
    insert into LMA_DEFAULT_LICENCE_POOL_GROUP (group_name)
    select distinct(g.group_name)
    from LMA_ENVIRONMENT_USER_GROUP g
    where exists (
    SELECT 1
    FROM LMA_USER_ALLOCATED_LICENCE_POOL dp
    WHERE dp.entity_licence_pool_id = 1
    and dp.username = g.username
    )

    END

    GO
    --
    -- LMA_SP_POPULATE_USER_POTENTIAL_LICENCE_POOL
    --
    DROP PROCEDURE IF EXISTS LMA_SP_POPULATE_USER_POTENTIAL_LICENCE_POOL
    GO
    CREATE PROCEDURE LMA_SP_POPULATE_USER_POTENTIAL_LICENCE_POOL
    @collection_run_id INT,
    -- LMA-69
    @LMA_R_BOOL_FLAT_APP_THRESHOLD_ENABLED tinyint,
    @LMA_R_INT_FLAT_APP_LICENCE_TYPE_IDS varchar(500),
    @LMA_R_INT_FLAT_APP_THRESHOLD_1_VALUE int,
    @LMA_R_INT_FLAT_APP_THRESHOLD_1_EXCEEDED_LIC_POOL_ID int,
    @LMA_R_INT_FLAT_APP_THRESHOLD_2_VALUE int,
    @LMA_R_INT_FLAT_APP_THRESHOLD_2_EXCEEDED_LIC_POOL_ID int

    AS
    BEGIN

    INSERT INTO LMA_USER_POTENTIAL_LICENCE_POOL(username, entity_licence_pool_id)
    SELECT distinct ug.username, ISNULL(lpg.entity_licence_pool_id, 1)
    FROM LMA_ENVIRONMENT_USER_GROUP ug
    left join LMA_GROUP g
    on g.group_uuid = ug.group_uuid
    left join LMA_ENTITY_LICENCE_POOL_GROUP lpg
    on lpg.group_id = g.group_id

    -- LMA-69 Support for Multiple Application License vs Enterprise License
    IF ISNULL(@LMA_R_BOOL_FLAT_APP_THRESHOLD_ENABLED, 0) = 1
    BEGIN
    -- Add a potential licence pool for users over thresholds flat app,
    -- where they don't already have this potential licence pool

    -- Flat Apps - Process First Threshold
    INSERT INTO LMA_USER_POTENTIAL_LICENCE_POOL (entity_licence_pool_id, username)
    SELECT @LMA_R_INT_FLAT_APP_THRESHOLD_1_EXCEEDED_LIC_POOL_ID, lp.username
    FROM LMA_USER_POTENTIAL_LICENCE_POOL lp
    -- Where this licence pool uses a Flat App licence type
    WHERE EXISTS(
    SELECT 1
    FROM LMA_ENTITY_LICENCE_POOL flatAppPools
    -- Where licence type is @scope as a Flat App
    WHERE flatAppPools.licence_type_id in (select value from string_split(@LMA_R_INT_FLAT_APP_LICENCE_TYPE_IDS,','))
    -- And it's used @this potential licence pool
    AND flatAppPools.entity_licence_pool_id = lp.entity_licence_pool_id
    )
    -- And they aren't already @the exceeds licence pool
    AND NOT EXISTS(
    SELECT 1
    FROM LMA_USER_POTENTIAL_LICENCE_POOL plp2
    WHERE plp2.username=lp.username AND plp2.entity_licence_pool_id = @LMA_R_INT_FLAT_APP_THRESHOLD_1_EXCEEDED_LIC_POOL_ID
    )
    GROUP BY lp.username
    HAVING count(lp.entity_licence_pool_id) > @LMA_R_INT_FLAT_APP_THRESHOLD_1_VALUE
    AND count(lp.entity_licence_pool_id) <= @LMA_R_INT_FLAT_APP_THRESHOLD_2_VALUE

    -- Flat Apps - Process Second Threshold
    INSERT INTO LMA_USER_POTENTIAL_LICENCE_POOL (entity_licence_pool_id, username)
    SELECT @LMA_R_INT_FLAT_APP_THRESHOLD_2_EXCEEDED_LIC_POOL_ID, lp.username
    FROM LMA_USER_POTENTIAL_LICENCE_POOL lp
    -- Where this licence pool uses a Flat App licence type
    WHERE EXISTS(
    SELECT 1
    FROM LMA_ENTITY_LICENCE_POOL flatAppPools
    -- Where licence type is @scope as a Flat App
    --WHERE FIND_IN_SET(flatAppPools.licence_type_id, @LMA_R_INT_FLAT_APP_LICENCE_TYPE_IDS)
    WHERE flatAppPools.licence_type_id in (select value from string_split(@LMA_R_INT_FLAT_APP_LICENCE_TYPE_IDS,','))
    -- And it's used @this potential licence pool
    AND flatAppPools.entity_licence_pool_id = lp.entity_licence_pool_id
    )
    -- And they aren't already @the exceeds licence pool
    AND NOT EXISTS(
    SELECT 1
    FROM LMA_USER_POTENTIAL_LICENCE_POOL plp2
    WHERE plp2.username=lp.username AND plp2.entity_licence_pool_id = @LMA_R_INT_FLAT_APP_THRESHOLD_2_EXCEEDED_LIC_POOL_ID
    )
    GROUP BY lp.username
    HAVING count(lp.entity_licence_pool_id) > @LMA_R_INT_FLAT_APP_THRESHOLD_2_VALUE
    END

    END


    DROP PROCEDURE IF EXISTS LMA_R_SP_USER_LICENCE_POOL_MEMBERSHIP
    GO

    CREATE PROCEDURE LMA_R_SP_USER_LICENCE_POOL_MEMBERSHIP(@username varchar(255))
    AS
    BEGIN

    SELECT DISTINCT
    ul.user_potential_licence_pool_id AS id,
    ul.username AS username,
    (SELECT MAX(ll.login) FROM LMA_ENVIRONMENT_USER_LOG@ll WHERE ll.username = ul.username) AS lastLogin,
    lt.licence_type_id AS licenceTypeId,
    lt.licence_type_name AS licenceTypeName,
    lt.licence_type_value AS licenceTypeValue,
    en.entity_id AS entityId,
    en.entity_name AS entityName,
    CASE WHEN alp.entity_licence_pool_id IS NULL then 0 ELSE 1 END AS isAllocated
    FROM LMA_USER_POTENTIAL_LICENCE_POOL ul
    LEFT join LMA_USER_ALLOCATED_LICENCE_POOL alp
    ON alp.entity_licence_pool_id = ul.entity_licence_pool_id
    AND alp.username = ul.username
    join LMA_ENTITY_LICENCE_POOL lp
    ON lp.entity_licence_pool_id = ul.entity_licence_pool_id
    LEFT join LMA_LICENCE_TYPE lt
    ON lt.licence_type_id = lp.licence_type_id
    join LMA_ENTITY en
    ON en.entity_id = lp.entity_id
    where ul.username = @username

    END

    GO

    END$$
    DELIMITER
    GO


    DROP PROCEDURE IF EXISTS LMA_R_SP_USER_LOGINS_OVER_TIME
    GO

    CREATE PROCEDURE LMA_R_SP_USER_LOGINS_OVER_TIME(@username varchar(255))
    AS
    BEGIN

    SELECT cast(ul.login as date) as loginDate, count(ul.Environment_user_login_id) as loginCount
    FROM LMA_ENVIRONMENT_USER_LOGin ul
    WHERE ul.username=username
    group by cast(ul.login as date)

    END

    GO

    --
    -- LMA_SP_LICENCE_POOL_THRESHOLD_COUNTS
    --
    DROP PROCEDURE IF EXISTS LMA_SP_LICENCE_POOL_THRESHOLD_COUNTS
    GO


    CREATE PROCEDURE LMA_SP_LICENCE_POOL_THRESHOLD_COUNTS(
    @entityLicencePoolId INT,
    @threshold INT,
    @periodDays INT
    )
    AS
    BEGIN

    SELECT sum(case when tbl.loginCount > @threshold then 1 else 0 end) as aboveUsers,
    sum(case when tbl.loginCount <= @threshold then 1 else 0 end) as withinUsers
    FROM (
    SELECT username, count(login) as loginCount
    FROM LMA_ENVIRONMENT_USER_LOGin eul
    WHERE EXISTS (
    SELECT 1
    FROM LMA_USER_ALLOCATED_LICENCE_POOL alp
    WHERE alp.entity_licence_pool_id = @entityLicencePoolId
    AND alp.username = eul.username
    )
    AND login > DATEADD(DAY,@periodDays*-1,CAST(GETDATE() AS DATE))
    GROUP BY username
    ) tbl


    END

    GO


    END$$
    DELIMITER
    GO


    --
    -- LMA_SP_LICENCE_POOL_THRESHOLD_USERS
    --
    DROP PROCEDURE IF EXISTS LMA_SP_LICENCE_POOL_THRESHOLD_USERS
    GO

    CREATE PROCEDURE LMA_SP_LICENCE_POOL_THRESHOLD_USERS(
    /* Paging and sorting filters */
    @p_limit int,
    @p_offset int,
    @p_orderBy varchar(255),
    @p_orderByDirection varchar(4),
    @p_isTotalCount tinyint,
    /* Standard filters */
    @p_entityLicencePoolId INT,
    @p_threshold INT,
    @p_periodDays INT,
    @p_segment VARCHAR(250)
    )
    AS
    BEGIN

    IF @p_isTotalCount = 1

    SELECT count(*) as totalCount
    FROM (
    SELECT username, count(login) as loginCount
    FROM LMA_ENVIRONMENT_USER_LOGin eul
    WHERE EXISTS (
    SELECT 1
    FROM LMA_USER_ALLOCATED_LICENCE_POOL alp
    WHERE alp.entity_licence_pool_id = @p_entityLicencePoolId
    AND alp.username = eul.username
    )
    AND login> DATEADD(DAY,@p_periodDays*-1,CAST(GETDATE() AS DATE))
    GROUP BY username
    ) tbl
    WHERE (
    @p_segment = 'ABOVE'
    AND
    loginCount > @p_threshold
    )
    OR
    (
    @p_segment = 'WITHIN'
    AND
    loginCount <= @p_threshold
    )

    ELSE
    SELECT username,
    loginCount
    FROM (
    SELECT username, count(login) as loginCount
    FROM LMA_ENVIRONMENT_USER_LOGin eul
    WHERE EXISTS (
    SELECT 1
    FROM LMA_USER_ALLOCATED_LICENCE_POOL alp
    WHERE alp.entity_licence_pool_id = @p_entityLicencePoolId
    AND alp.username = eul.username
    )
    AND login> DATEADD(DAY,@p_periodDays*-1,CAST(GETDATE() AS DATE))
    GROUP BY username
    ) tbl
    WHERE (
    @p_segment = 'ABOVE'
    AND
    loginCount > @p_threshold
    )
    OR
    (
    @p_segment = 'WITHIN'
    AND
    loginCount <= @p_threshold
    )
    /* Custom sorting - ensure that the column names @text below match the field names that are used by the grid for sorting */
    ORDER BY

    (case when @p_orderBy = 'username' and @p_orderByDirection = 'asc' then username end) asc,
    (case when @p_orderBy = 'username' and @p_orderByDirection = 'desc' then username end) desc,

    (case when @p_orderBy = 'loginCount' and @p_orderByDirection = 'asc' then loginCount end) asc,
    (case when @p_orderBy = 'loginCount' and @p_orderByDirection = 'desc' then loginCount end) desc

    /* Custom paging - the wrapper rule ensures the correct values are passed in, including allowing for the fact that OFFSET @MySQL is zero based and startIndex @Appian is 1 based */
    OFFSET @p_offset ROWS FETCH NEXT @p_limit ROWS ONLY


    END
    GO

    --
    -- LMA_SP_USERS_BY_LICENCE_TYPE
    --
    DROP PROCEDURE IF EXISTS LMA_SP_USERS_BY_LICENCE_TYPE
    GO

    CREATE PROCEDURE LMA_SP_USERS_BY_LICENCE_TYPE(
    /* Paging and sorting filters */
    @p_limit int,
    @p_offset int,
    @p_orderBy varchar(255),
    @p_orderByDirection varchar(4),
    @p_isTotalCount tinyint,
    /* Standard filters */
    @p_licenceTypeId INT,
    @p_username VARCHAR(255)
    )
    AS
    BEGIN

    IF @p_isTotalCount = 1

    SELECT count(distinct username) as totalCount
    FROM LMA_USER_POTENTIAL_LICENCE_POOL ul
    WHERE (
    ISNULL(@p_licenceTypeId,'') = ''
    OR
    EXISTS (
    SELECT 1
    FROM LMA_ENTITY_LICENCE_POOL lp
    WHERE lp.licence_type_id = @p_licenceTypeId
    AND lp.entity_licence_pool_id = ul.entity_licence_pool_id
    )
    )
    AND
    (
    ISNULL(@p_username,'') = ''
    OR
    ul.username = @p_username
    )

    ELSE
    SELECT ul.username AS username,
    (SELECT MAX(ll.login) FROM LMA_ENVIRONMENT_USER_LOG@ll WHERE ll.username = ul.username) AS lastLogin
    FROM LMA_USER_POTENTIAL_LICENCE_POOL ul
    WHERE (
    ISNULL(@p_licenceTypeId,'') = ''
    OR
    EXISTS (
    SELECT 1
    FROM LMA_ENTITY_LICENCE_POOL lp
    WHERE lp.licence_type_id = @p_licenceTypeId
    AND lp.entity_licence_pool_id = ul.entity_licence_pool_id
    )
    )
    AND
    (
    ISNULL(@p_username,'') = ''
    OR
    ul.username = @p_username
    )
    GROUP BY username

    /* Custom sorting - ensure that the column names @text below match the field names that are used by the grid for sorting */
    ORDER BY

    (case when @p_orderBy = 'username' and @p_orderByDirection = 'asc' then username end) asc,
    (case when @p_orderBy = 'username' and @p_orderByDirection = 'desc' then username end) desc

    /* Custom paging - the wrapper rule ensures the correct values are passed in, including allowing for the fact that OFFSET @MySQL is zero based and startIndex @Appian is 1 based */
    OFFSET @p_offset ROWS FETCH NEXT @p_limit ROWS ONLY


    END
    GO


    --
    -- LMA_SP_USER_LICENCE_POOLS
    --
    DROP PROCEDURE IF EXISTS LMA_SP_USER_LICENCE_POOLS
    GO

    CREATE PROCEDURE LMA_SP_USER_LICENCE_POOLS(
    @p_usernameList VARCHAR(1000)
    )
    AS
    BEGIN

    SELECT ul.username AS username,
    lt.licence_type_name AS licenceTypeName,
    en.entity_name AS entityName,
    CASE WHEN alp.entity_licence_pool_id is null then 0 ELSE 1 END AS isAllocated

    FROM LMA_USER_POTENTIAL_LICENCE_POOL ul
    LEFT join LMA_USER_ALLOCATED_LICENCE_POOL alp
    ON alp.entity_licence_pool_id = ul.entity_licence_pool_id
    AND alp.username = ul.username
    join LMA_ENTITY_LICENCE_POOL lp
    ON lp.entity_licence_pool_id = ul.entity_licence_pool_id
    join LMA_LICENCE_TYPE lt
    ON lt.licence_type_id = lp.licence_type_id
    join LMA_ENTITY en
    ON en.entity_id = lp.entity_id

    --WHERE FIND_IN_SET(ul.username, p_usernameList)
    WHERE ul.username in (select value from string_split(@p_usernameList,','))

    END
    GO

    -- LMA-36 / LMA-72 - delete unused licence pools / business entities

    --
    -- LMA_SP_IS_RECORD_IN_USE
    -- Utility to identify if a certain record is in use
    --
    DROP PROCEDURE IF EXISTS LMA_SP_IS_RECORD_IN_USE
    GO
    CREATE PROCEDURE LMA_SP_IS_RECORD_IN_USE(
    @recordType VARCHAR(255),
    @recordId int
    )
    AS
    BEGIN

    -- LMA-72
    IF @recordType = 'LMA_ENTITY_LICENCE_POOL'
    SELECT
    CASE
    WHEN sum(tbl.rowsCount) > 0 then 1
    ELSE 0
    END as licPoolInUse
    FROM (
    SELECT count(1) as rowsCount FROM LMA_LICENCE_POOL_USER_COUNT WHERE entity_licence_pool_id=@recordId
    UNION
    SELECT count(1) as rowsCount FROM LMA_USER_ALLOCATED_LICENCE_POOL WHERE entity_licence_pool_id=@recordId
    UNION
    SELECT count(1) as rowsCount FROM LMA_USER_POTENTIAL_LICENCE_POOL WHERE entity_licence_pool_id=@recordId
    ) tbl

    -- LMA-36
    ELSE IF @recordType = 'LMA_ENTITY'
    SELECT
    CASE
    WHEN count(1) > 0 then 1
    ELSE 0
    END as licPoolInUse
    FROM LMA_ENTITY_LICENCE_POOL
    WHERE entity_id = @recordId

    END
    GO