/*==========================================================*/
/* Migración 9.6.1.3                                        */
/*==========================================================*/

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID('METADATA_DROP_PROCEDURE') AND TYPE = 'P')
BEGIN
	DROP PROCEDURE METADATA_DROP_PROCEDURE
END
GO
CREATE PROCEDURE METADATA_DROP_PROCEDURE
(
	@procedureName NVARCHAR(255)
)
AS
BEGIN		
	IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(@procedureName) AND TYPE ='P')
	BEGIN    
		EXEC('DROP PROCEDURE ' + @procedureName)
	END
END
GO


update afw_roles set role_is_builtin=1 where role_name='Remote Support Administrator';
GO
update afw_roles set role_is_builtin=1 where role_name='Remote Support Controller';
GO

EXEC METADATA_DROP_PROCEDURE @procedureName	= 'METADATA_DASHBOARD'
GO

CREATE PROCEDURE METADATA_DASHBOARD
(
     @name          NVARCHAR(255),
     @folder        INT,
     @order         INT,
     @definition    NVARCHAR(MAX),
     @frecuency     INT,
     @description   NVARCHAR(510),
	 @id            INT OUTPUT
)
AS
BEGIN
    SET @id = NULL
	
	SELECT @id           = boar_id
	FROM   [DASH_BOARD] 
	WHERE  [boar_name]   = @name 
    AND    [boar_folder] = @folder

	IF @id IS NULL
    BEGIN	
        INSERT INTO [DASH_BOARD]([boar_name], [boar_folder], [boar_state],  [boar_order], [boar_description], [boar_frecuency], [boar_definition]) 
		VALUES                  (@name,        @folder,       1,            @order,       @description,        @frecuency,      @definition)

		SELECT @id = SCOPE_IDENTITY()
    END
    ELSE
    BEGIN
        UPDATE [DASH_BOARD] SET		                                     
			   [boar_definition]    = @definition,
			   [boar_name]          = @name,
			   [boar_description]   = @description,
			   [boar_frecuency]     = @frecuency,
			   [boar_order]         = @order
        WHERE  [boar_id]            = @id
    END
END
GO

DECLARE @documentDeviceId   INT, 
		@folderDevicesId    INT,
		@dashboardDeviceFolder    INT,
		@aamFolder          INT,
		@rootDevice         INT,
		@xmlDefinitionDevice NVARCHAR(MAX)

SELECT @rootDevice = fold_id 
FROM   AFW_FOLDER
WHERE  fold_parent IS NULL 
AND    fold_name           = 'ADM'
AND    fold_application_id = 32

SELECT @dashboardDeviceFolder = fold_id 
FROM   AFW_FOLDER 
WHERE  fold_parent = @rootDevice  
AND    fold_name   = 'Dashboards'

SELECT @aamFolder = fold_id 
FROM   AFW_FOLDER 
WHERE  fold_parent = @dashboardDeviceFolder  
AND    fold_name   = 'Dashboards_AAM'

SET @xmlDefinitionDevice= '<?xml version="1.0" encoding="utf-8"?>
<Dashboard CurrencyCulture="es-CO">
  <Title Text="Monitor de dispositivos" Alignment="Left">iVBORw0KGgoAAAANSUhEUgAAAJEAAACKCAYAAACqyah7AAAACXBIWXMAABcSAAAXEgFnn9JSAAAAB3RJTUUH4ggDFC4TIQu3WAAAAAd0RVh0QXV0aG9yAKmuzEgAAAAMdEVYdERlc2NyaXB0aW9uABMJISMAAAAKdEVYdENvcHlyaWdodACsD8w6AAAADnRFWHRDcmVhdGlvbiB0aW1lADX3DwkAAAAJdEVYdFNvZnR3YXJlAF1w/zoAAAALdEVYdERpc2NsYWltZXIAt8C0jwAAAAh0RVh0V2FybmluZwDAG+aHAAAAB3RFWHRTb3VyY2UA9f+D6wAAAAh0RVh0Q29tbWVudAD2zJa/AAAABnRFWHRUaXRsZQCo7tInAAANVElEQVR4nO2dP4zcWhXGfwEk5IbkieIVFJk00CBlU0FhKZPuiWb3dRRIGUuUSNlIIETxFEdQIISUScerPNulQW9TUmVWcp9dhARdJgUFDcqih9wRinu9e8dz/WfWvn/Gcz8pUnY8uz6yP9/73XO+c33r48ePBAT0wTdcBxCw+wgkCuiNQKKA3ggkCuiNQKKA3ggkCuiNQKKA3ggkCuiNQKKA3viW6wB8RpTld4AD4LxI4g+u4/EVt0LZYxNRlh8DM+C+8vHLIomP3UTkNwKJFERZfgTMgbs1X3leJHFqL6LdQJjOgCjLDxDkedjy1RmQmo5n17DXI5HUPHPgsebwBdeE+Ur5/JOgj9axtyNRlOUpcAzcrhy6BI6LJF7I792pHD8Almaj2y3sHYmiLJ8CC/S65zkwV0eaIok/RFluJ7gdxd6QKMryCYI8Ot3zGjH6rCyGNBqMnkRyOkqBJ5rDFwjyLG3GNDaMmkQy35PSonsC+mGUJNpW9wT0w6hI1KJ7zoCZTd0jk5dTZOkEQV5r57eFUZCoRfe8R5BnaTEe3TT6EJhFWT4tkvjcViw2sPMkatE9aZHEc4uxTKmfRkHEmAJHdiKyg50lUcsNe4kgkBXd0zKNXgAr4FD+fKj5zk5j50gkb9gc/c2wqntayiZX06iszR0qv3cwpiltZ0gkb9gx8Exz2IXuSakvm6xNo0USn1ey3tVSyk5jJ0gUZfkM8cQ71z1AHGX5gpA+uILXJJK6Z866OayEVd2j4Leaz/a6bOIliTzTPZOGw6Fsgmck6qB7joskPrUci84SG8omCrwhUYvumdu0pcpYUjZ1TwH8Acu6R67ujhANA1Yeom3gnEQtuucE8cTbyvdMEeTR5Xv+DPzSQfrgCOXBirL8Apj6JN6dkUhqjRR9juUMQR4ruZQOGiz1JH0A4mGbI/zeXsA6iSpao3qRXOmeOg2W2tQ9LVn4S66v1+Moy62N0G2wSqIGreFK9/iiwSa0uC7l/98pn3vj9bZCohat4UL3+KLBtnJd+ur1NkqiltqSC92zoN5rZC0WGc9oXJemR6Ilm0+8Va3RwWtkTYMpeAV8qvncVRa+F4yRSGoOlUCXiFHJWo7FJ69RBVUCWXddDgmTI5FqvLpE5DZsTV1T/PEalStAHay7D0zAebJxSLT01LvwWM/QrwD/AzxzOBIOCpMkOuU6eXcbWEZZbmTp3NUcNvR5G+KZUr8C/BPwG9u6RxJ6Kv+BuD+DjMhGN3SIsvwcvbAe7KZ27am3AdPugyjL1Zv1qMs1bMjNAVwUSXxw03hKmJ7Opghtol7Uu8CbKMt7XdSWi2PVHOaT+0CJaUp9bq7E/SjLZ30fNKMkkjfxqGZ4fwi8i7J8qxvecnGsm8N8ysLLeCY0Nw0sEIQv453Kz24MK8JaDrsHNUvuZ4h+rMbcUYeCre0i6RRPsvAynjZdeHV95XfLUXPS99xWV2dFEs+lPzllPfl3F8jkU72WOe5QsLVdJJ3giftAxtN0fayMhtaX+PLpPJZkqi7HHwJvoyw/QVyUIxqmCtzoHufuAyWmGfoUAljMhznLE8mndVqz2eZj4GfANzW/eoK4OCvjQUo0bAhqncwK6kon1q+P82RjkcSnUZYv2VzdVAnkQvc0JS+t36wKdKUTq9enhHMSbYEPiHZk4+jgPrBN5gn1u9a6KiJfwTmJGub1/7H+2ohDxPRndPpoSF66EPFN+af/Ar/wwTLi7N0eUZZPoyxfARmbN+wE+C7wFKE7StxGXNBzqVOGjOdIxvOsEs8lInl5YJlAKWLk1REI4Kc+EAjceKwndN+Iaq6s4tSp5S7wlcx691pSt+gen5KXVXxtPJiOsEaim25EJaetWTmNoU8JbL2c9W1D0Jbk5Rmiu+Od5phz2PJYp3TcQaMOSkpgxqaGeoIgWqeCq0/W1A5liisy76vHesrA5rAiiRdRlp+yKThvI7Lex9SMIj5tCLpNmcJ3mLbHZppDvXWGvNGp1EsL1p/i+wiXwFUep0tLjuXOVqdliqFhhETKU6ZicJ0hb/y0ZoR5DHweZfnfgR9pft2FWW2GB2WKoWFqJJqyfqFOiiSeGTpX6RKYaLTXd9gkkG8bgrrOfPeGKRLdqfy8NHSeNRRJXE5xKXqt8U/gJ0US/9VGPB1WXE7KFEPDFImWlZ8zmY8xPlzLJ3omyfQl8H3l8PeAi22NcNuixS4C8EWRxL8zcW4XMJKxljfyZeXjJ8BK6gLjKJJ4WSTxD4CE9aw3iFXd4LFEWX5HTqnvqCcQgJ9r9RvCWNmjEC/dfV75uFyGn8uh3jjkMnnCJqnLWJZDxCJTCys2yxRl2WS0MFo7k0vVe4j5X0W5DD+NmvdEHCqOD5LUulgeylgW0eZbFlsRZflM1txeoK8BHuzakn1bGC/AFkm8KpJ4CjxCLKtVHCLM+ulNbmCPWD7XxPIYMcXVdauuQRaQl4hcWHXVdQbcK5J4Z1ujt4G1Kr7UKBPE0G5FozTEcorY36cay23gRZTlq7opLsryicyYv2Fz1XWB6Aeb7gN5SrjwWKdKMVUVn6VGmWFh6VvJelcbDsveuPcInbNE6KoD9F2tO1WmGBpOTGkdKvNrZQvDsayo7427K//VNQDuZJliaDgzpYGozLdolH9EWf57S3ppKVuKn2pi0eElMNl3AoFjEpUokvhU6qUzQO03/zbwa4STcWYplrmM5RFCM/1bOfwlgvCfFEnszcabruHcYw2NLTklapsbTUFqsmWU5Wre59UYyhRDwymJWqypf0HU4NQC6lpzYxgJ/ICT6UyWBxbAWzYJdIZYJn9WJPGPEWWLupxOajjUgA6wTiKli6FaW7oEEpljWZYfymVzXU7nmczpDNr5EbAdrJGooSUHBEEmdXkWWbZIEWR6XTlcdn4s5fQYYBnGNdGQ1tSWnM6NOz8C+sHoSCRXU++oLw8c3SSZqOR0dDaP0nLSqQYW0B/GSCRHoKpRv9Q9B0MslRWbh85y0lgDCxgOJkei6khwQoPuuSkUvXQPvV56I/XSZMjzBlzDJIlUkfte2iKM6RRp8zhCbzkp94ec2yih7BtMkkjNKt+VBjRbNbAJm5tBgGWL7r7AJInmrN/EQywKXtkSNKHeFmvNojt2mPRYrxC6aCvT18AxNNlirVp0xwzTHusF9QlCa4LXJ4vuGGHLY10K3ovKYauC1yeL7phg22Ndmr6cJghlSmCCSDuoGLSNaF9gvQDbInhf2BK8Ui/NgAc0tBGZjmMMcGIF8UnwdrDoBrTAtce6i+C1pZdOG/SSj3jli37zxWO9RGxH8zfNYds9/CliRVnVS77hUzzRb85JVNkE4Yc1X7OaIJQj5AwxQvqOXm3gQ8ApieToco5+E4Q/4l4vLU2fowf+VfnZmWXYlcf6oKGPvaz2/6pB8B4i2oj2OUH4czyxDFslUQeD/oNqtb9B8Kq7689Mxu0pvu5oGZ6YDsSmx7rcv6e6bH7PtUG/tp+sIUFY9qQ5F5gu4ENFwDiJout3eOj279nqnRldE4T7WFDd0jI8GfLcNjzWb9jUPa8R+/fcyFCvJAjretL2Vi+17Az3IsryD6zPBr27iU16rKdseqx7GfSraOtJw8DbiHYBLRWB6mzQeytmkyPRrPLz06EM+ioqPWk6veRjT9rM8s5wOr0EQouu+p7HJIkmyv8vC8Obj1cShDq99NZlQq6CMqczs3EyRS99gpAACUJOLIb4+7Y81rdtJghb9JIvPfzWbSdy1F7If6uh/q5tj7XNTT4XXOslFb718DsvW/SFaY/1FIcOwg49adYSchV8gd+j5FYw7bE+x4OOCx8SchXktO90MrUUS2/Y8Fj7ZEDzpoe/w04nO9O5a9Nj3bQRuQu9NMGDHv4tRslZ5Zg3u57c+vjxY/u3DCDy5P3z8kmv7mNd4oz1QvGjm+a5oixXL3Tt34nq30+r4rJIYm9EuDM/kS8F1Q49/FbR0Migwqttc1x7rL0pqLb08Jf4zHQcMhZVRz7neporHQ8LG3F0hbPpTIeofiviS/m5lbdFS12WIsR2FTd6l23X6WwX4RWJoPVtze8RN/DUUiwThPdbh622CgwkcoCo+RWYZ1jaFL1y83Xo9CrQMZPIebdHHTwtqJ4Qevg34C2JSnhWUF3Q3MO/l3seeU+iEr4UVFtWlGUGfq8sut5qoiZ0SBAO9trMNi3TZUWJIP8b5dgDG3rOFnaSRCUi/aboJQbZFH2LTHOKfkV5iShRXJGsSOJbfWLyDTsznengWUE1pV4vqaOU7z3+W2OnSVTCl4Kqopd0K0qQiUrTcdjGTk9nOkTN7xLZWi/1ye/I9MMRguAr38oVQ2F0JCohR54F+rc5dkoQyr8z2iThUBjFdKZDS0F17xOEQ2K0JCrRYK3Y6wThkBg9icAvi+4YsRckKuGTRXdMGK2w7oI2iy6wZN0KEoS1Bns1ElXRZtFl00u0Mh7UDmKvRyIVcsOHOfW+6tfSix1QwV6PRCqK9U3Rq31gr9nc5SRAIoxEAb0RRqKA3ggkCuiNQKKA3ggkCuiNQKKA3ggkCuiN/wN/Il32L9pDkwAAAABJRU5ErkJggg==</Title>
  <DataSources>
    <SqlDataSource ComponentName="sqlDataSource2">
      <Name>ADM</Name>
      <Connection Name="Connection" ProviderKey="MSSqlServer">
        <Parameters>
          <Parameter Name="server" Value="BG-D-APRENDIZ01" />
          <Parameter Name="database" Value="MTPreventaTest" />
          <Parameter Name="useIntegratedSecurity" Value="False" />
          <Parameter Name="read only" Value="1" />
          <Parameter Name="generateConnectionHelper" Value="false" />
          <Parameter Name="userid" Value="sa" />
          <Parameter Name="password" Value="Aranda2017" />
        </Parameters>
      </Connection>
      <Query Type="CustomSqlQuery" Name="CustomSqlQuery">
        <Sql>SELECT COUNT(ID) AS [DEVICES] FROM AAM_DEVICE WHERE is_active = 1</Sql>
      </Query>
      <Query Type="CustomSqlQuery" Name="CustomSqlQuery_1">
        <Sql>SELECT COUNT([CONFLICT]) AS [CONFLICTS] FROM (
	SELECT 
		CASE WHEN B.conflict_type_id is null OR B.status_id =2 THEN 0 ELSE B.conflict_type_id END AS [CONFLICT]
	FROM VIEW_AAM_DEVICE A 
	LEFT JOIN AAM_DEVICE_CONFLICT B ON B.device_id = A.id) AS X
WHERE [CONFLICT] != 0</Sql>
      </Query>
      <Query Type="CustomSqlQuery" Name="CustomSqlQuery_2">
        <Sql>SELECT COUNT(a.id) AS [NOT AGENT] FROM AAM_DEVICE a LEFT JOIN ADM_COMMUNICATION_NODE b ON a.id=b.device_id WHERE b.id is null</Sql>
      </Query>
      <Query Type="CustomSqlQuery" Name="CustomSqlQuery_3">
        <Sql>SELECT COUNT(DEVICE)AS [ACTUALIZED] FROM(
	SELECT
			CASE WHEN DEVLIC.device_id IS NULL  AND AAM_DEVICE.is_discovery = 0  THEN ''Without License''
				ELSE
					CASE  WHEN	DATEDIFF(DAY,last_update,GETUTCDATE()) &lt;= CONVERT(INT,AFW_SETTINGS.sett_value) THEN ''Inventory''
					END
			END [STATUS],
			ISNULL(ADM_DEVICE_INVENTORY.device_id, 0)AS [DEVICE]  
	FROM AAM_DEVICE 
	LEFT JOIN ADM_DEVICE_INVENTORY ON AAM_DEVICE.id = ADM_DEVICE_INVENTORY.device_id AND app_code = 1
	LEFT JOIN ADM_DEVICE_LICENSE DEVLIC ON AAM_DEVICE.[id] = DEVLIC.[device_id],
	 AFW_SETTINGS
	WHERE (app_code = 1 OR app_code IS NULL)
				   AND (ADM_DEVICE_INVENTORY.type = 0 OR ADM_DEVICE_INVENTORY.type IS NULL)
				   AND (AFW_SETTINGS.sett_key=''UpdatedInventoryDays'' OR AFW_SETTINGS.sett_key IS NULL)
				   AND (AFW_SETTINGS.sett_application_id = 32 OR AFW_SETTINGS.sett_application_id IS NULL)
				   AND AFW_SETTINGS.sett_project_id IS NULL
				   AND (DEVLIC.product_id = 1 OR DEVLIC.product_id IS NULL))AS X
WHERE STATUS = ''Inventory''</Sql>
      </Query>
      <Query Type="CustomSqlQuery" Name="CustomSqlQuery_4">
        <Sql>SELECT COUNT(DEVICE)AS [QUANTITY], STATUS AS [STATE] FROM(
	SELECT
			CASE WHEN DEVLIC.device_id IS NULL  AND AAM_DEVICE.is_discovery = 0  THEN ''Without License''
				ELSE
					CASE  WHEN	DATEDIFF(DAY,last_update,GETUTCDATE()) &lt;= CONVERT(INT,AFW_SETTINGS.sett_value) THEN ''Inventory''
					WHEN ISNULL(DATEDIFF(DAY, last_update, GETUTCDATE()), 0) &gt; CONVERT(INT,AFW_SETTINGS.sett_value) THEN ''Without Updating''
					WHEN DATEDIFF(DAY, last_update, GETUTCDATE()) IS NULL THEN ''Without Inventory''
					END
			END [STATUS],
			ISNULL(ADM_DEVICE_INVENTORY.device_id, 0)AS [DEVICE]  
	FROM AAM_DEVICE 
	LEFT JOIN ADM_DEVICE_INVENTORY ON AAM_DEVICE.id = ADM_DEVICE_INVENTORY.device_id AND app_code = 1
	LEFT JOIN ADM_DEVICE_LICENSE DEVLIC ON AAM_DEVICE.[id] = DEVLIC.[device_id],
	 AFW_SETTINGS
	WHERE (app_code = 1 OR app_code IS NULL)
				   AND (ADM_DEVICE_INVENTORY.type = 0 OR ADM_DEVICE_INVENTORY.type IS NULL)
				   AND (AFW_SETTINGS.sett_key=''UpdatedInventoryDays'' OR AFW_SETTINGS.sett_key IS NULL)
				   AND (AFW_SETTINGS.sett_application_id = 32 OR AFW_SETTINGS.sett_application_id IS NULL)
				   AND AFW_SETTINGS.sett_project_id IS NULL
				   AND (DEVLIC.product_id = 1 OR DEVLIC.product_id IS NULL))AS X
GROUP BY STATUS</Sql>
      </Query>
      <Query Type="CustomSqlQuery" Name="CustomSqlQuery_5">
        <Sql>SELECT 
	COUNT(type) AS [Devices],
	CASE type 
		WHEN -1 THEN ''Unknown''
		WHEN 0 THEN ''Desktop''
		WHEN 1 THEN ''Laptop''
		WHEN 2 THEN ''Server''
		WHEN 3 THEN ''Printer''
		WHEN 4 THEN ''Switch''
		WHEN 5 THEN ''Router''
	END AS [PC TYPE]
FROM VIEW_AAM_DEVICE_DETAIL
GROUP BY type</Sql>
      </Query>
      <Query Type="CustomSqlQuery" Name="CustomSqlQuery_6">
        <Sql>SELECT
	COUNT(ISNULL(manufacturer_id, 0)) AS [Devices],
	ISNULL(manufacturer, ''Unknown'') AS [MANUFACTURER]
FROM VIEW_AAM_DEVICE_DETAIL A
GROUP BY manufacturer</Sql>
      </Query>
      <Query Type="CustomSqlQuery" Name="CustomSqlQuery_7">
        <Sql>SELECT
COUNT(operating_system) AS [Devices],
operating_system AS [Operating System]
FROM VIEW_AAM_DEVICE_DETAIL A
WHERE platform = 1
GROUP BY operating_system</Sql>
      </Query>
      <Query Type="CustomSqlQuery" Name="CustomSqlQuery_8">
        <Sql>SELECT
COUNT(operating_system) AS [Devices],
operating_system AS [Operating System]
FROM VIEW_AAM_DEVICE_DETAIL A
WHERE platform = 2
GROUP BY operating_system</Sql>
      </Query>
      <Query Type="CustomSqlQuery" Name="CustomSqlQuery_9">
        <Sql>SELECT
COUNT(operating_system) AS [Devices],
operating_system AS [Operating System]
FROM VIEW_AAM_DEVICE_DETAIL A
WHERE platform = 3
GROUP BY operating_system</Sql>
      </Query>
      <Query Type="CustomSqlQuery" Name="CustomSqlQuery_10">
        <Sql>SELECT COUNT(ESTADO)AS [ON LINE] FROM(
	SELECT 
		CASE WHEN DATEDIFF(MINUTE, LAST_UPDATE, GETUTCDATE()) &lt;=5 THEN ''1'' END AS [ESTADO]

	FROM(
		SELECT MAX(last_update)AS [LAST_UPDATE], device_id FROM AAM_DEVICE A
		INNER JOIN ADM_COMMUNICATION_NODE B ON A.id = B.device_id
		GROUP BY device_id)AS X)AS X 
WHERE [ESTADO] IS NOT NULL</Sql>
      </Query>
      <ConnectionOptions CloseConnection="true" />
    </SqlDataSource>
  </DataSources>
  <Items>
    <CustomItem ComponentName="customItemDashboardItem1" Name="Dispositivos " DataSource="sqlDataSource2" DataMember="CustomSqlQuery" CustomItemType="Donut">
      <DataItems>
        <Dimension DataMember="DEVICES" DefaultId="DataItem0" />
        <Measure DataMember="DEVICES" DefaultId="DataItem1" />
        <Dimension DataMember="DEVICES" DefaultId="DataItem2" />
        <Dimension DataMember="DEVICES" DefaultId="DataItem3" />
      </DataItems>
      <SliceTables>
        <SliceTable Name="SliceTable1">
          <Dimensions>
            <Dimension DefaultId="DataItem3" />
          </Dimensions>
        </SliceTable>
      </SliceTables>
      <InteractivityTargets>
        <Dimension DefaultId="DataItem3" />
      </InteractivityTargets>
      <ColoringMeasures>
        <Measure DefaultId="DataItem1" />
      </ColoringMeasures>
      <CustomMetadata barColorDonut="#00a1c6" textColorDonut="#00a1c6">
        <donutValue ItemType="Dimension" DefaultId="DataItem3">
          <Item1 ItemType="Measure" DefaultId="DataItem1" />
        </donutValue>
      </CustomMetadata>
    </CustomItem>
    <Pie ComponentName="pieDashboardItem1" Name="Dispositivos por estado" DataSource="sqlDataSource2" DataMember="CustomSqlQuery_4" PieType="Donut" LabelContentType="ArgumentAndValue" TooltipContentType="Percent" ShowPieCaptions="false">
      <ColoringOptions MeasuresColoringMode="Hue" />
      <DataItems>
        <Measure DataMember="QUANTITY" DefaultId="DataItem0" />
        <Dimension DataMember="STATE" DefaultId="DataItem1" />
        <Dimension DataMember="QUANTITY" DefaultId="DataItem2" />
      </DataItems>
      <Arguments>
        <Argument DefaultId="DataItem1" />
        <Argument DefaultId="DataItem2" />
      </Arguments>
      <Values>
        <Value DefaultId="DataItem0" />
      </Values>
    </Pie>
    <CustomItem ComponentName="customItemDashboardItem2" Name="KPI Icon 1" ShowCaption="false" DataSource="sqlDataSource2" DataMember="CustomSqlQuery_10" CustomItemType="KPI Icon">
      <DataItems>
        <Dimension DataMember="ON LINE" DefaultId="DataItem0" />
        <Measure DataMember="ON LINE" DefaultId="DataItem1" />
        <Dimension DataMember="ON LINE" DefaultId="DataItem2" />
        <Dimension DataMember="ON LINE" DefaultId="DataItem3" />
      </DataItems>
      <SliceTables>
        <SliceTable Name="SliceTable1">
          <Dimensions>
            <Dimension DefaultId="DataItem3" />
          </Dimensions>
        </SliceTable>
      </SliceTables>
      <InteractivityTargets>
        <Dimension DefaultId="DataItem3" />
      </InteractivityTargets>
      <ColoringMeasures>
        <Measure DefaultId="DataItem1" />
      </ColoringMeasures>
      <CustomMetadata KpiIcon="fa fa-link" value2="En línea" bgColorKpiIcon="#00a1c6" valueColorKpiIcon="#00a1c6">
        <kpiIconValue ItemType="Dimension" DefaultId="DataItem3">
          <Item1 ItemType="Measure" DefaultId="DataItem1" />
        </kpiIconValue>
      </CustomMetadata>
    </CustomItem>
    <CustomItem ComponentName="customItemDashboardItem3" Name="KPI Icon 2" ShowCaption="false" DataSource="sqlDataSource2" DataMember="CustomSqlQuery_3" CustomItemType="KPI Icon">
      <DataItems>
        <Dimension DataMember="ACTUALIZED" DefaultId="DataItem0" />
        <Measure DataMember="ACTUALIZED" DefaultId="DataItem1" />
        <Dimension DataMember="ACTUALIZED" DefaultId="DataItem2" />
        <Dimension DataMember="ACTUALIZED" DefaultId="DataItem3" />
      </DataItems>
      <SliceTables>
        <SliceTable Name="SliceTable1">
          <Dimensions>
            <Dimension DefaultId="DataItem3" />
          </Dimensions>
        </SliceTable>
      </SliceTables>
      <InteractivityTargets>
        <Dimension DefaultId="DataItem3" />
      </InteractivityTargets>
      <ColoringMeasures>
        <Measure DefaultId="DataItem1" />
      </ColoringMeasures>
      <CustomMetadata KpiIcon="fa fa-calendar-check-o" value2="Actualizados" bgColorKpiIcon="#00a1c6" valueColorKpiIcon="#00a1c6">
        <kpiIconValue ItemType="Dimension" DefaultId="DataItem3">
          <Item1 ItemType="Measure" DefaultId="DataItem1" />
        </kpiIconValue>
      </CustomMetadata>
    </CustomItem>
    <Chart ComponentName="chartDashboardItem1" Name="Dispositivos por fabricante" DataSource="sqlDataSource2" DataMember="CustomSqlQuery_6">
      <DataItems>
        <Dimension DataMember="MANUFACTURER" DefaultId="DataItem1" />
        <Measure DataMember="Devices" DefaultId="DataItem0" />
      </DataItems>
      <Arguments>
        <Argument DefaultId="DataItem1" />
      </Arguments>
      <Panes>
        <Pane Name="Pane 1">
          <AxisY Title="Devices" />
          <Series>
            <Simple>
              <Value DefaultId="DataItem0" />
            </Simple>
          </Series>
        </Pane>
      </Panes>
      <ChartLegend Visible="false" />
      <AxisX Reverse="true" />
    </Chart>
    <Chart ComponentName="chartDashboardItem2" Name="Dispositivos por tipo" DataSource="sqlDataSource2" DataMember="CustomSqlQuery_5">
      <ColoringOptions MeasuresColoringMode="None" />
      <DataItems>
        <Dimension DataMember="PC TYPE" DefaultId="DataItem1" />
        <Measure DataMember="Devices" DefaultId="DataItem0" />
      </DataItems>
      <Arguments>
        <Argument DefaultId="DataItem1" />
      </Arguments>
      <Panes>
        <Pane Name="Pane 1">
          <AxisY Title="Devices" />
          <Series>
            <Simple Name="Devices">
              <Value DefaultId="DataItem0" />
            </Simple>
          </Series>
        </Pane>
      </Panes>
    </Chart>
    <Grid ComponentName="gridDashboardItem1" Name="Versiones windows" DataSource="sqlDataSource2" DataMember="CustomSqlQuery_7">
      <DataItems>
        <Dimension DataMember="Operating System" DefaultId="DataItem0" />
        <Measure DataMember="Devices" DefaultId="DataItem1" />
      </DataItems>
      <GridColumns>
        <GridDimensionColumn>
          <Dimension DefaultId="DataItem0" />
        </GridDimensionColumn>
        <GridMeasureColumn Name="Devices">
          <Measure DefaultId="DataItem1" />
        </GridMeasureColumn>
      </GridColumns>
      <GridOptions EnableBandedRows="true" ShowHorizontalLines="false" ShowVerticalLines="false" ColumnWidthMode="Manual" />
    </Grid>
    <Grid ComponentName="gridDashboardItem2" Name="Versiones mac" DataSource="sqlDataSource2" DataMember="CustomSqlQuery_9">
      <DataItems>
        <Dimension DataMember="Operating System" DefaultId="DataItem0" />
        <Measure DataMember="Devices" DefaultId="DataItem1" />
      </DataItems>
      <GridColumns>
        <GridDimensionColumn>
          <Dimension DefaultId="DataItem0" />
        </GridDimensionColumn>
        <GridMeasureColumn Name="Devices">
          <Measure DefaultId="DataItem1" />
        </GridMeasureColumn>
      </GridColumns>
      <GridOptions EnableBandedRows="true" ShowHorizontalLines="false" ShowVerticalLines="false" WordWrap="true" />
    </Grid>
    <Grid ComponentName="gridDashboardItem3" Name="Versiones linux" DataSource="sqlDataSource2" DataMember="CustomSqlQuery_8">
      <DataItems>
        <Dimension DataMember="Operating System" DefaultId="DataItem0" />
        <Measure DataMember="Devices" DefaultId="DataItem1" />
      </DataItems>
      <GridColumns>
        <GridDimensionColumn>
          <Dimension DefaultId="DataItem0" />
        </GridDimensionColumn>
        <GridMeasureColumn Name="Devices">
          <Measure DefaultId="DataItem1" />
        </GridMeasureColumn>
      </GridColumns>
      <GridOptions EnableBandedRows="true" ShowHorizontalLines="false" ShowVerticalLines="false" WordWrap="true" />
    </Grid>
    <CustomItem ComponentName="customItemDashboardItem5" Name="KPI Icon 3" ShowCaption="false" DataSource="sqlDataSource2" DataMember="CustomSqlQuery_1" CustomItemType="KPI Icon">
      <DataItems>
        <Dimension DataMember="CONFLICTS" DefaultId="DataItem0" />
        <Measure DataMember="CONFLICTS" DefaultId="DataItem1" />
        <Dimension DataMember="CONFLICTS" DefaultId="DataItem2" />
        <Dimension DataMember="CONFLICTS" DefaultId="DataItem3" />
      </DataItems>
      <SliceTables>
        <SliceTable Name="SliceTable1">
          <Dimensions>
            <Dimension DefaultId="DataItem3" />
          </Dimensions>
        </SliceTable>
      </SliceTables>
      <InteractivityTargets>
        <Dimension DefaultId="DataItem3" />
      </InteractivityTargets>
      <ColoringMeasures>
        <Measure DefaultId="DataItem1" />
      </ColoringMeasures>
      <CustomMetadata KpiIcon="fa fa-times-circle" value2="Con conflictos" bgColorKpiIcon="#cf8740" valueColorKpiIcon="#cf8740">
        <kpiIconValue ItemType="Dimension" DefaultId="DataItem3">
          <Item1 ItemType="Measure" DefaultId="DataItem1" />
        </kpiIconValue>
      </CustomMetadata>
    </CustomItem>
    <CustomItem ComponentName="customItemDashboardItem4" Name="KPI Icon 3" ShowCaption="false" DataSource="sqlDataSource2" DataMember="CustomSqlQuery_2" CustomItemType="KPI Icon">
      <DataItems>
        <Dimension DataMember="NOT AGENT" DefaultId="DataItem0" />
        <Dimension DataMember="NOT AGENT" DefaultId="DataItem1" />
        <Dimension DataMember="NOT AGENT" DefaultId="DataItem2" />
      </DataItems>
      <SliceTables>
        <SliceTable Name="SliceTable1">
          <Dimensions>
            <Dimension DefaultId="DataItem2" />
          </Dimensions>
        </SliceTable>
      </SliceTables>
      <InteractivityTargets>
        <Dimension DefaultId="DataItem2" />
      </InteractivityTargets>
      <CustomMetadata KpiIcon="fa fa-hdd-o" value2="Sin agente" bgColorKpiIcon="#cf8740" valueColorKpiIcon="#cf8740">
        <kpiIconValue ItemType="Dimension" DefaultId="DataItem2" />
      </CustomMetadata>
    </CustomItem>
  </Items>
  <ColorScheme>
    <Entry DataSource="sqlDataSource1" DataMember="VIEW_ADM_DEVICE_DASHBOARD" Color="-15969158">
      <DimensionKeys>
        <DimensionKey>
          <Definition DataMember="status" />
          <Value Type="System.String" Value="not update" />
        </DimensionKey>
      </DimensionKeys>
    </Entry>
    <Entry DataSource="sqlDataSource1" DataMember="VIEW_ADM_DEVICE_DASHBOARD" Color="-10125922">
      <DimensionKeys>
        <DimensionKey>
          <Definition DataMember="status" />
          <Value Type="System.String" Value="not inventoried" />
        </DimensionKey>
      </DimensionKeys>
    </Entry>
    <Entry DataSource="sqlDataSource1" DataMember="VIEW_ADM_DEVICE_DASHBOARD" Color="-13910304">
      <DimensionKeys>
        <DimensionKey>
          <Definition DataMember="type" />
          <Value Type="System.String" Value="Desktop" />
        </DimensionKey>
      </DimensionKeys>
    </Entry>
    <Entry DataSource="sqlDataSource1" DataMember="VIEW_ADM_DEVICE_DASHBOARD" Color="-10125922">
      <DimensionKeys>
        <DimensionKey>
          <Definition DataMember="type" />
          <Value Type="System.String" Value="Laptop" />
        </DimensionKey>
      </DimensionKeys>
    </Entry>
    <Entry DataSource="sqlDataSource1" DataMember="VIEW_ADM_DEVICE_DASHBOARD" Color="-12396900">
      <DimensionKeys>
        <DimensionKey>
          <Definition DataMember="type" />
          <Value Type="System.String" Value="Unknown" />
        </DimensionKey>
      </DimensionKeys>
    </Entry>
    <Entry DataSource="sqlDataSource1" DataMember="VIEW_ADM_DEVICE_DASHBOARD" Color="-13910304">
      <DimensionKeys>
        <DimensionKey>
          <Definition DataMember="manufacturer" />
          <Value Type="System.String" Value="Apple Inc." />
        </DimensionKey>
      </DimensionKeys>
    </Entry>
    <Entry DataSource="sqlDataSource1" DataMember="VIEW_ADM_DEVICE_DASHBOARD" Color="-10125922">
      <DimensionKeys>
        <DimensionKey>
          <Definition DataMember="manufacturer" />
          <Value Type="System.String" Value="Dell Inc." />
        </DimensionKey>
      </DimensionKeys>
    </Entry>
    <Entry DataSource="sqlDataSource1" DataMember="VIEW_ADM_DEVICE_DASHBOARD" Color="-12396900">
      <DimensionKeys>
        <DimensionKey>
          <Definition DataMember="manufacturer" />
          <Value Type="System.String" Value="Hewlett-Packard" />
        </DimensionKey>
      </DimensionKeys>
    </Entry>
    <Entry DataSource="sqlDataSource1" DataMember="VIEW_ADM_DEVICE_DASHBOARD" Color="-5011812">
      <DimensionKeys>
        <DimensionKey>
          <Definition DataMember="manufacturer" />
          <Value Type="System.String" Value="HP" />
        </DimensionKey>
      </DimensionKeys>
    </Entry>
    <Entry DataSource="sqlDataSource1" DataMember="VIEW_ADM_DEVICE_DASHBOARD" Color="-686993">
      <DimensionKeys>
        <DimensionKey>
          <Definition DataMember="manufacturer" />
          <Value Type="System.String" Value="Intel Corporation" />
        </DimensionKey>
      </DimensionKeys>
    </Entry>
    <Entry DataSource="sqlDataSource1" DataMember="VIEW_ADM_DEVICE_DASHBOARD" Color="-2070454">
      <DimensionKeys>
        <DimensionKey>
          <Definition DataMember="manufacturer" />
          <Value Type="System.String" Value="LENOVO" />
        </DimensionKey>
      </DimensionKeys>
    </Entry>
    <Entry DataSource="sqlDataSource1" DataMember="VIEW_ADM_DEVICE_DASHBOARD" Color="-11637092">
      <DimensionKeys>
        <DimensionKey>
          <Definition DataMember="manufacturer" />
          <Value Type="System.String" Value="Unknown" />
        </DimensionKey>
      </DimensionKeys>
    </Entry>
    <Entry DataSource="sqlDataSource1" DataMember="VIEW_ADM_DEVICE_DASHBOARD" Color="-16734053">
      <DimensionKeys>
        <DimensionKey>
          <Definition DataMember="manufacturer" />
          <Value Type="System.String" Value="VMware, Inc." />
        </DimensionKey>
      </DimensionKeys>
    </Entry>
  </ColorScheme>
  <LayoutTree>
    <LayoutGroup Weight="100">
      <LayoutGroup Orientation="Vertical" Weight="100">
        <LayoutGroup Weight="108.25824302560869">
          <LayoutGroup Orientation="Vertical" Weight="192.84409286377124">
            <LayoutGroup Weight="100">
              <LayoutGroup Orientation="Vertical" Weight="122.05074044399238">
                <LayoutGroup Weight="141.39274227771887">
                  <LayoutItem DashboardItem="customItemDashboardItem1" Weight="117.89195585082612" />
                  <LayoutGroup Orientation="Vertical" Weight="118.80879828645135">
                    <LayoutItem DashboardItem="customItemDashboardItem3" Weight="99.833580977766971" />
                    <LayoutItem DashboardItem="customItemDashboardItem2" Weight="104.49534778071907" />
                  </LayoutGroup>
                </LayoutGroup>
                <LayoutGroup Weight="74.416896330824343">
                  <LayoutGroup Orientation="Vertical" Weight="105.77532144199515">
                    <LayoutGroup Weight="105.77532144199515">
                      <LayoutItem DashboardItem="customItemDashboardItem5" Weight="105.77532144199515" />
                      <LayoutItem DashboardItem="customItemDashboardItem4" Weight="105.77532144199515" />
                    </LayoutGroup>
                  </LayoutGroup>
                </LayoutGroup>
              </LayoutGroup>
              <LayoutItem DashboardItem="pieDashboardItem1" Weight="121.61629423239272" />
              <LayoutItem DashboardItem="chartDashboardItem2" Weight="117.34193289707181" />
            </LayoutGroup>
          </LayoutGroup>
        </LayoutGroup>
        <LayoutGroup Weight="101.84600717487245">
          <LayoutGroup Orientation="Vertical" Weight="139.91854570730055">
            <LayoutGroup Weight="114.12530991194515">
              <LayoutItem DashboardItem="chartDashboardItem1" Weight="221.47581189749118" />
              <LayoutItem DashboardItem="gridDashboardItem1" Weight="109.24566668182473" />
            </LayoutGroup>
          </LayoutGroup>
          <LayoutGroup Orientation="Vertical" Weight="79.88522762688828">
            <LayoutGroup Weight="124.21175461844432">
              <LayoutItem DashboardItem="gridDashboardItem3" Weight="129.22545156940313" />
              <LayoutItem DashboardItem="gridDashboardItem2" Weight="114.90815568836872" />
            </LayoutGroup>
          </LayoutGroup>
        </LayoutGroup>
      </LayoutGroup>
    </LayoutGroup>
  </LayoutTree>
</Dashboard>'

EXEC METADATA_DASHBOARD
     @name          = 'DASH_DEVICE_MONITOR',
     @folder        = @aamFolder,
     @order         = 1,
     @definition    = @xmlDefinitionDevice,
     @frecuency     = 60,
     @description   = 'DASH_DEVICE_MONITOR_DESCRIPTION',
	 @id            = @documentDeviceId OUTPUT

	PRINT 'AAM - Dashboard - Monitor de Dispositivos'   
GO

EXEC METADATA_DROP_PROCEDURE 'METADATA_DATABASEINFO'
GO

CREATE PROCEDURE METADATA_DATABASEINFO
(
    @v_key NVARCHAR(64), 
    @v_value NVARCHAR(64), 
    @v_description NVARCHAR(128)
)
AS
BEGIN

	IF (NOT EXISTS(SELECT 1 FROM [ADM_DATABASEINFO] WHERE [key] = @v_key))
	BEGIN
		INSERT INTO [ADM_DATABASEINFO] ([key], [value], [description]) VALUES (@v_key, @v_value, @v_description)
    END
	ELSE
	BEGIN
		UPDATE [dbo].[ADM_DATABASEINFO]
		   SET 
			  [value] = @v_value
			  ,[description] = @v_description
		 WHERE [key] = @v_key
	END

END
GO

DECLARE @current_product_version NVARCHAR(64)
DECLARE @product_patch_version NVARCHAR(64)

SELECT @current_product_version = '9.6'
SELECT @product_patch_version = '1.3'

EXEC METADATA_DATABASEINFO 'ADM_version',@current_product_version,'ADM Product Version'
EXEC METADATA_DATABASEINFO 'ADM_patch_version',@product_patch_version,'ADM Product Patch Version'

EXEC METADATA_DROP_PROCEDURE 'METADATA_DATABASEINFO'
GO