and more in a single search tool across platforms. Read the announcement here. |
12/06/2023 09:31 PM
Hi All,
We are trying to generate an anlytics report where customproperty4 of entitlements has the values as below example:
CP4 = server1;server2;server3;server4;server5;server6 (It can contain more than 100 values separated by semi colon)
Below is the query I currently used with total number of values as 4 - this has a drawback of limiting to certain value
select distinct SUBSTRING_INDEX(SUBSTRING_INDEX(ev.customproperty4, ';', numbers.n), ';', -1) as 'ServerName', EV.customproperty11 as 'Address', EV.customproperty5 as 'Application Service', EV.customproperty6 as 'Environment', EV.customproperty3 as 'Safe', EV.customproperty1 as 'Privileged Account Name', A.name as 'Account Name', A.customproperty18 as 'AD Domain' from (select 1 n union all select 2 union all select 3 union all select 4 union all select 5) numbers INNER JOIN entitlement_values EV on CHAR_LENGTH(EV.customproperty4) -CHAR_LENGTH(REPLACE(EV.customproperty4, ';', '')) >= numbers.n-1 INNER JOIN entitlement_types ET on EV.entitlementtypekey=ET.entitlementtypekey INNER JOIN endpoints E on E.endpointkey=ET.endpointkey INNER JOIN entitlements2 ET2 on ET2.entitlement_value2key = EV.entitlement_valuekey INNER JOIN entitlement_values EV2 on ET2.entitlement_value1key = EV2.entitlement_valuekey INNER JOIN account_entitlements1 AE1 on EV2.entitlement_valuekey=AE1.entitlement_valuekey INNER JOIN accounts A on AE1.accountkey=A.accountkey where E.endpointkey='13'
Does Analytics report support stored procedures or cross apply string_function()?
Please provide your valuable inputs
Thank you,
Vidya D Mudagal
12/06/2023 09:37 PM
use group_concat function https://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-g...
12/06/2023 09:44 PM
I am trying to do the oppasite solution of splitting comma values as rows.
CP4 = server1;server2;server3
CP5=ApplicationName
Expected result
cp4 | cp5 |
server1 | ApplicationName |
server2 | ApplicationName |
server3 | applicationName |
Thank you,
VIdya D Mudagal
12/06/2023 09:58 PM
If you have two columns, column1 with values 'A', 'B', 'C' and column2 with values 'P', 'Q', 'R', and you want to perform a cross join to get all possible combinations of values from both columns, you can use the following SQL query:
This query will produce a result set with all possible combinations of values from column1 and column2. Each row in the result set will contain a pair of values, one from column1 and one from column2.
Here's how the result set might look like:
Adjust the table names (YourTable1 and YourTable2) based on the actual names of your tables.