Appian Community

Parents

amitr0003 over 3 years ago

Hi Team,

is it possible to release the DB scripts for MS SQL Server as well.

thanks in advance

Amit
- Cancel
- Up 0 Down
- Reply
- More
- Cancel

Comment

amitr0003 over 3 years ago

Hi Team,

is it possible to release the DB scripts for MS SQL Server as well.

thanks in advance

Amit
- Cancel
- Up 0 Down
- Reply
- More
- Cancel

Children

jeanalainb0001 over 3 years ago in reply to amitr0003

I have one that seem working for me but not sure that all is fully correct
- Cancel
- Up 0 Down
- Reply
- More
- Cancel
jeanalainb0001 over 3 years ago in reply to amitr0003

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
- Cancel
- Up -1 Down
- Reply
- More
- Cancel

Appian Usage Insights