KACE Product Support

mysqli error: [1222: The used SELECT statements have a different number of columns] in EXECUTE(\n"# Asset Report - Cost Account: 15.0.005.00 - 20180709\nselect `Item`, `Type`, `ID`, `Last Logged on User`, if(`Unit Cost` != "", format(`Unit Cost`, 2), "") as 'Unit Cost (₦)'\nfrom\n(\n# Header Query\nselect\n "Cost Account:" as 'Item'\n ,COST_ACCOUNT_ASSET.NAME as 'Type'\n ,concat("Department: ", COST_ACCOUNT_ASSET_DATA.FIELD_10025) as 'ID'\n ,concat("Site: ", COST_ACCOUNT_ASSET_DATA.FIELD_10026) as 'Last Logged on User'\n ,"" as 'Unit Cost'\n ,null as 'ASSET_NAME'\n ,null as 'SORT_ORDER'\n ,COST_ACCOUNT_ASSET.NAME as 'COST_ACCOUNT'\n ,-1 as 'SECTION_ORDER'\nfrom\n ASSET COST_ACCOUNT_ASSET\n join ASSET_DATA_15 COST_ACCOUNT_ASSET_DATA on COST_ACCOUNT_ASSET.ASSET_DATA_ID = COST_ACCOUNT_ASSET_DATA.ID\n and COST_ACCOUNT_ASSET.ASSET_TYPE_ID = 15 # Asset type ID 15 = "JBN Cost Accounts"\ngroup by\n `Type`\nunion all\n\n# Computer Assets\nselect\n "Computer" as 'Item'\n ,TYPE_ASSET.NAME as 'Type'\n ,COMPUTER_ASSET.NAME as 'ID'\n ,M.USER_LOGGED as 'Last Logged on User'\n ,M.LAST_INVENTORY as 'Last Inventory'\n ,TYPE_ASSET_DATA.FIELD_10047 as 'Unit Cost'\n ,COMPUTER_ASSET.NAME as 'ASSET_NAME'\n ,0 as 'SORT_ORDER'\n ,COST_ACCOUNT_ASSET.NAME as 'COST_ACCOUNT'\n ,1 as 'SECTION_ORDER'\nfrom\n ASSET COMPUTER_ASSET\n left join MACHINE M on COMPUTER_ASSET.MAPPED_ID = M.ID\n join ASSET_ASSOCIATION AA_COMPUTER_TYPE on COMPUTER_ASSET.ID = AA_COMPUTER_TYPE.ASSET_ID\n and AA_COMPUTER_TYPE.ASSET_FIELD_ID = 10031\n join ASSET TYPE_ASSET on AA_COMPUTER_TYPE.ASSOCIATED_ASSET_ID = TYPE_ASSET.ID\n and TYPE_ASSET.ASSET_TYPE_ID = 17 # ASSET TYPE ID 17 = "JBN Computer Type"\n join ASSET_DATA_17 TYPE_ASSET_DATA on TYPE_ASSET.ASSET_DATA_ID = TYPE_ASSET_DATA.ID\n join ASSET_ASSOCIATION AA_COMPUTER_COST_ACCOUNT on COMPUTER_ASSET.ID = AA_COMPUTER_COST_ACCOUNT.ASSET_ID\n and AA_COMPUTER_COST_ACCOUNT.ASSET_FIELD_ID = 10030\n join ASSET COST_ACCOUNT_ASSET on AA_COMPUTER_COST_ACCOUNT.ASSOCIATED_ASSET_ID = COST_ACCOUNT_ASSET.ID\nwhere\n COMPUTER_ASSET.ASSET_TYPE_ID = 5\nunion all\n\n# Additional PC Components\nselect\n ADDITIONAL_PC_COMPONENTS_ASSET.NAME as 'Item'\n ,"" as 'Type'\n ,"" as 'ID'\n ,"" as 'Last Logged on User'\n ,ADDITIONAL_PC_COMPONENTS_ASSET_DATA.FIELD_10053 as 'Unit Cost'\n ,COMPUTER_ASSET.NAME as 'ASSET_NAME'\n ,1 as 'SORT_ORDER'\n ,COST_ACCOUNT_ASSET.NAME as 'COST_ACCOUNT'\n ,1 as 'SECTION_ORDER'\nfrom\n ASSET COMPUTER_ASSET\n join ASSET_ASSOCIATION AA_COMPUTER_ADDITIONAL_PC_COMPONENTS on COMPUTER_ASSET.ID = AA_COMPUTER_ADDITIONAL_PC_COMPONENTS.ASSET_ID\n and AA_COMPUTER_ADDITIONAL_PC_COMPONENTS.ASSET_FIELD_ID = 10072\n join ASSET ADDITIONAL_PC_COMPONENTS_ASSET on AA_COMPUTER_ADDITIONAL_PC_COMPONENTS.ASSOCIATED_ASSET_ID = ADDITIONAL_PC_COMPONENTS_ASSET.ID\n and ADDITIONAL_PC_COMPONENTS_ASSET.ASSET_TYPE_ID = 19 # ASSET TYPE ID 19 = "JBN Additional PC Components"\n join ASSET_DATA_19 ADDITIONAL_PC_COMPONENTS_ASSET_DATA on ADDITIONAL_PC_COMPONENTS_ASSET.ASSET_DATA_ID = ADDITIONAL_PC_COMPONENTS_ASSET_DATA.ID\n join ASSET_ASSOCIATION AA_COMPUTER_COST_ACCOUNT on COMPUTER_ASSET.ID = AA_COMPUTER_COST_ACCOUNT.ASSET_ID\n and AA_COMPUTER_COST_ACCOUNT.ASSET_FIELD_ID = 10030\n join ASSET COST_ACCOUNT_ASSET on AA_COMPUTER_COST_ACCOUNT.ASSOCIATED_ASSET_ID = COST_ACCOUNT_ASSET.ID\nwhere\n COMPUTER_ASSET.ASSET_TYPE_ID = 5\nunion all\n\n# Software (License)\nselect\n SC.NAME as 'Item'\n ,"" as 'Type'\n ,"" as 'ID'\n ,"" as 'Last Logged on User'\n ,LICENSE_ASSET_DATA.FIELD_6 as 'Unit Cost'\n ,COMPUTER_ASSET.NAME as 'ASSET_NAME'\n ,2 as 'SORT_ORDER'\n ,COST_ACCOUNT_ASSET.NAME as 'COST_ACCOUNT'\n ,1 as 'SECTION_ORDER'\nfrom\n ASSET COMPUTER_ASSET\n join MACHINE M on COMPUTER_ASSET.MAPPED_ID = M.ID\n join SAM_MACHINE_JT SMJT on SMJT.MACHINE_ID = M.ID\n join CATALOG.SAM_CATALOG SC on SMJT.SAM_CATALOG_ID = SC.ID\n join ASSET_CATALOG_ASSOCIATION ACA on SC.ID = ACA.ASSOCIATED_CATALOG_ID\n# and ACA.ASSET_FIELD_ID = 24 # License\n and ACA.ASSET_FIELD_ID = 10000 # License\n join ASSET LICENSE_ASSET on ACA.ASSET_ID = LICENSE_ASSET.ID\n and LICENSE_ASSET.ASSET_TYPE_ID = 7\n join ASSET_DATA_7 LICENSE_ASSET_DATA on LICENSE_ASSET.ASSET_DATA_ID = LICENSE_ASSET_DATA.ID\n join ASSET_ASSOCIATION AA_COMPUTER_COST_ACCOUNT on COMPUTER_ASSET.ID = AA_COMPUTER_COST_ACCOUNT.ASSET_ID\n and AA_COMPUTER_COST_ACCOUNT.ASSET_FIELD_ID = 10030\n join ASSET COST_ACCOUNT_ASSET on AA_COMPUTER_COST_ACCOUNT.ASSOCIATED_ASSET_ID = COST_ACCOUNT_ASSET.ID\nwhere\n COMPUTER_ASSET.ASSET_TYPE_ID = 5 # TYPE ID 5 = Computer Asset Type\n# and SC.SAM_TYPE IN ('TITLED_APPLICATION','TITLED_SUITE')\n and SC.SOFTWARE_CATEGORY_ID != 32\n and LICENSE_ASSET_DATA.FIELD_6 > 0\ngroup by\n M.ID, SC.ID\nunion all\n \n # Software (JBN software)\n select\n SPECIAL_SOFTWARE_ASSET.NAME as 'Item'\n ,"" as 'Type'\n ,"" as 'ID'\n ,"" as 'Last Logged on User'\n ,SPECIAL_SOFTWARE_ASSET_DATA.FIELD_10017 as 'Unit Cost'\n ,COMPUTER_ASSET.NAME as 'ASSET_NAME'\n ,2 as 'SORT_ORDER'\n ,COST_ACCOUNT_ASSET.NAME as 'COST_ACCOUNT'\n ,1 as 'SECTION_ORDER'\n from\n ASSET COMPUTER_ASSET\n join MACHINE M on COMPUTER_ASSET.MAPPED_ID = M.ID\n join ASSET_ASSOCIATION AA_SPECIAL_SOFTWARE on COMPUTER_ASSET.ID = AA_SPECIAL_SOFTWARE.ASSET_ID\n and AA_SPECIAL_SOFTWARE.ASSET_FIELD_ID = 10077\n join ASSET SPECIAL_SOFTWARE_ASSET on AA_SPECIAL_SOFTWARE.ASSOCIATED_ASSET_ID = SPECIAL_SOFTWARE_ASSET.ID\n and SPECIAL_SOFTWARE_ASSET.ASSET_TYPE_ID = 12\n join ASSET_DATA_12 SPECIAL_SOFTWARE_ASSET_DATA on SPECIAL_SOFTWARE_ASSET.ASSET_DATA_ID = SPECIAL_SOFTWARE_ASSET_DATA.ID\n join ASSET_ASSOCIATION AA_COMPUTER_COST_ACCOUNT on COMPUTER_ASSET.ID = AA_COMPUTER_COST_ACCOUNT.ASSET_ID\n and AA_COMPUTER_COST_ACCOUNT.ASSET_FIELD_ID = 10030\n join ASSET COST_ACCOUNT_ASSET on AA_COMPUTER_COST_ACCOUNT.ASSOCIATED_ASSET_ID = COST_ACCOUNT_ASSET.ID\n where\n COMPUTER_ASSET.ASSET_TYPE_ID = 5 # TYPE ID 5 = Computer Asset Type\n group by\n M.ID, SPECIAL_SOFTWARE_ASSET.ID\n union all\n \n # JBN Car radio unit:\n select\n "JBN Car radio unit" as 'Item'\n ,JBN_ASSET_DATA.FIELD_10106 as 'Type'\n ,JBN_ASSET.NAME as 'ID'\n ,JBN_ASSET_DATA.FIELD_10107 as 'Last Logged on User'\n ,JBN_ASSET_DATA.FIELD_10109 as 'Unit Cost'\n ,JBN_ASSET.NAME as 'ASSET_NAME'\n ,0 as 'SORT_ORDER'\n ,COST_ACCOUNT_ASSET.NAME as 'COST_ACCOUNT'\n ,2 as 'SECTION_ORDER'\n from\n ASSET JBN_ASSET\n join ASSET_DATA_29 JBN_ASSET_DATA on JBN_ASSET.ASSET_DATA_ID = JBN_ASSET_DATA.ID\n and JBN_ASSET.ASSET_TYPE_ID = 29\n join ASSET_ASSOCIATION AA_JBN_COST_ACCOUNT on JBN_ASSET.ID = AA_JBN_COST_ACCOUNT.ASSET_ID\n and AA_JBN_COST_ACCOUNT.ASSET_FIELD_ID = 10110\n join ASSET COST_ACCOUNT_ASSET on AA_JBN_COST_ACCOUNT.ASSOCIATED_ASSET_ID = COST_ACCOUNT_ASSET.ID\n union all\n \n # JBN Hand radio unit:\n select\n "JBN Hand radio unit" as 'Item'\n ,JBN_ASSET_DATA.FIELD_10099 as 'Type'\n ,JBN_ASSET.NAME as 'ID'\n ,JBN_ASSET_DATA.FIELD_10092 as 'Last Logged on User'\n ,JBN_ASSET_DATA.FIELD_10093 as 'Unit Cost'\n ,JBN_ASSET.NAME as 'ASSET_NAME'\n ,0 as 'SORT_ORDER'\n ,COST_ACCOUNT_ASSET.NAME as 'COST_ACCOUNT'\n ,3 as 'SECTION_ORDER'\n from\n ASSET JBN_ASSET\n join ASSET_DATA_25 JBN_ASSET_DATA on JBN_ASSET.ASSET_DATA_ID = JBN_ASSET_DATA.ID\n and JBN_ASSET.ASSET_TYPE_ID = 25\n join ASSET_ASSOCIATION AA_JBN_COST_ACCOUNT on JBN_ASSET.ID = AA_JBN_COST_ACCOUNT.ASSET_ID\n and AA_JBN_COST_ACCOUNT.ASSET_FIELD_ID = 10094\n join ASSET COST_ACCOUNT_ASSET on AA_JBN_COST_ACCOUNT.ASSOCIATED_ASSET_ID = COST_ACCOUNT_ASSET.ID\n union all\n \n # JBN Ipads:\n select\n "JBN Ipads" as 'Item'\n ,IPAD_TYPE_ASSET.NAME as 'Type'\n ,JBN_ASSET.NAME as 'ID'\n ,JBN_ASSET_DATA.FIELD_10091 as 'Last Logged on User'\n ,IPAD_TYPE_ASSET_DATA.FIELD_10115 as 'Unit Cost'\n ,JBN_ASSET.NAME as 'ASSET_NAME'\n ,0 as 'SORT_ORDER'\n ,COST_ACCOUNT_ASSET.NAME as 'COST_ACCOUNT'\n ,4 as 'SECTION_ORDER'\n from\n ASSET JBN_ASSET\n join ASSET_DATA_24 JBN_ASSET_DATA on JBN_ASSET.ASSET_DATA_ID = JBN_ASSET_DATA.ID\n and JBN_ASSET.ASSET_TYPE_ID = 24\n join ASSET_ASSOCIATION AA_JBN_COST_ACCOUNT on JBN_ASSET.ID = AA_JBN_COST_ACCOUNT.ASSET_ID\n and AA_JBN_COST_ACCOUNT.ASSET_FIELD_ID = 10088\n join ASSET COST_ACCOUNT_ASSET on AA_JBN_COST_ACCOUNT.ASSOCIATED_ASSET_ID = COST_ACCOUNT_ASSET.ID\n join ASSET_ASSOCIATION AA_JBN_IPAD_TYPE on JBN_ASSET.ID = AA_JBN_IPAD_TYPE.ASSET_ID\n and AA_JBN_IPAD_TYPE.ASSET_FIELD_ID = 10116\n join ASSET IPAD_TYPE_ASSET on AA_JBN_IPAD_TYPE.ASSOCIATED_ASSET_ID = IPAD_TYPE_ASSET.ID\n join ASSET_DATA_31 IPAD_TYPE_ASSET_DATA on IPAD_TYPE_ASSET.ASSET_DATA_ID = IPAD_TYPE_ASSET_DATA.ID\n and IPAD_TYPE_ASSET.ASSET_TYPE_ID = 31\n union all\n \n # JBN Sattelite Phone (Thuraya):\n select\n "JBN Sattelite Phone (Thuraya)" as 'Item'\n ,SATTELITE_PHONE_TYPE_ASSET.NAME as 'Type'\n ,JBN_ASSET.NAME as 'ID'\n ,JBN_ASSET_DATA.FIELD_10089 as 'Last Logged on User'\n ,SATTELITE_PHONE_TYPE_ASSET_DATA.FIELD_10118 as 'Unit Cost'\n ,JBN_ASSET.NAME as 'ASSET_NAME'\n ,0 as 'SORT_ORDER'\n ,COST_ACCOUNT_ASSET.NAME as 'COST_ACCOUNT'\n ,5 as 'SECTION_ORDER'\n from\n ASSET JBN_ASSET\n join ASSET_DATA_22 JBN_ASSET_DATA on JBN_ASSET.ASSET_DATA_ID = JBN_ASSET_DATA.ID\n and JBN_ASSET.ASSET_TYPE_ID = 22\n join ASSET_ASSOCIATION AA_JBN_COST_ACCOUNT on JBN_ASSET.ID = AA_JBN_COST_ACCOUNT.ASSET_ID\n and AA_JBN_COST_ACCOUNT.ASSET_FIELD_ID = 10084\n join ASSET COST_ACCOUNT_ASSET on AA_JBN_COST_ACCOUNT.ASSOCIATED_ASSET_ID = COST_ACCOUNT_ASSET.ID\n join ASSET_ASSOCIATION AA_JBN_SATTELITE_PHONE_TYPE on JBN_ASSET.ID = AA_JBN_SATTELITE_PHONE_TYPE.ASSET_ID\n and AA_JBN_SATTELITE_PHONE_TYPE.ASSET_FIELD_ID = 10119\n join ASSET SATTELITE_PHONE_TYPE_ASSET on AA_JBN_SATTELITE_PHONE_TYPE.ASSOCIATED_ASSET_ID = SATTELITE_PHONE_TYPE_ASSET.ID\n join ASSET_DATA_32 SATTELITE_PHONE_TYPE_ASSET_DATA on SATTELITE_PHONE_TYPE_ASSET.ASSET_DATA_ID = SATTELITE_PHONE_TYPE_ASSET_DATA.ID\n and SATTELITE_PHONE_TYPE_ASSET.ASSET_TYPE_ID = 32\n union all\n \n# JBN Sites & Fees:\nselect\n "JBN Sites & Fees" as 'Item'\n ,SITES_FEES_TYPE_ASSET.NAME as 'Type'\n ,JBN_ASSET.NAME as 'ID'\n ,"" as 'Last Logged on User'\n ,SITES_FEES_TYPE_ASSET_DATA.FIELD_10123 as 'Unit Cost'\n ,JBN_ASSET.NAME as 'ASSET_NAME'\n ,0 as 'SORT_ORDER'\n ,COST_ACCOUNT_ASSET.NAME as 'COST_ACCOUNT'\n ,6 as 'SECTION_ORDER'\nfrom\n ASSET JBN_ASSET\n join ASSET_DATA_20 JBN_ASSET_DATA on JBN_ASSET.ASSET_DATA_ID = JBN_ASSET_DATA.ID\n and JBN_ASSET.ASSET_TYPE_ID = 20\n join ASSET_ASSOCIATION AA_JBN_COST_ACCOUNT on JBN_ASSET.ID = AA_JBN_COST_ACCOUNT.ASSET_ID\n and AA_JBN_COST_ACCOUNT.ASSET_FIELD_ID = 10101\n join ASSET COST_ACCOUNT_ASSET on AA_JBN_COST_ACCOUNT.ASSOCIATED_ASSET_ID = COST_ACCOUNT_ASSET.ID\n join ASSET_ASSOCIATION AA_JBN_SITES_FEES_TYPE on JBN_ASSET.ID = AA_JBN_SITES_FEES_TYPE.ASSET_ID\n and AA_JBN_SITES_FEES_TYPE.ASSET_FIELD_ID = 10124\n join ASSET SITES_FEES_TYPE_ASSET on AA_JBN_SITES_FEES_TYPE.ASSOCIATED_ASSET_ID = SITES_FEES_TYPE_ASSET.ID\n join ASSET_DATA_34 SITES_FEES_TYPE_ASSET_DATA on SITES_FEES_TYPE_ASSET.ASSET_DATA_ID = SITES_FEES_TYPE_ASSET_DATA.ID\n and SITES_FEES_TYPE_ASSET.ASSET_TYPE_ID = 34\nunion all\n\n# JBN Smartphones:\nselect\n "JBN Smartphones" as 'Item'\n ,SMARTPHONES_TYPE_ASSET.NAME as 'Type'\n ,JBN_ASSET.NAME as 'ID'\n ,JBN_ASSET_DATA.FIELD_10090 as 'Last Logged on User'\n ,SMARTPHONES_TYPE_ASSET_DATA.FIELD_10121 as 'Unit Cost'\n ,JBN_ASSET.NAME as 'ASSET_NAME'\n ,0 as 'SORT_ORDER'\n ,COST_ACCOUNT_ASSET.NAME as 'COST_ACCOUNT'\n ,7 as 'SECTION_ORDER'\nfrom\n ASSET JBN_ASSET\n join ASSET_DATA_23 JBN_ASSET_DATA on JBN_ASSET.ASSET_DATA_ID = JBN_ASSET_DATA.ID\n and JBN_ASSET.ASSET_TYPE_ID = 23\n join ASSET_ASSOCIATION AA_JBN_COST_ACCOUNT on JBN_ASSET.ID = AA_JBN_COST_ACCOUNT.ASSET_ID\n and AA_JBN_COST_ACCOUNT.ASSET_FIELD_ID = 10086\n join ASSET COST_ACCOUNT_ASSET on AA_JBN_COST_ACCOUNT.ASSOCIATED_ASSET_ID = COST_ACCOUNT_ASSET.ID\n join ASSET_ASSOCIATION AA_JBN_SMARTPHONES_TYPE on JBN_ASSET.ID = AA_JBN_SMARTPHONES_TYPE.ASSET_ID\n and AA_JBN_SMARTPHONES_TYPE.ASSET_FIELD_ID = 10122\n join ASSET SMARTPHONES_TYPE_ASSET on AA_JBN_SMARTPHONES_TYPE.ASSOCIATED_ASSET_ID = SMARTPHONES_TYPE_ASSET.ID\n join ASSET_DATA_33 SMARTPHONES_TYPE_ASSET_DATA on SMARTPHONES_TYPE_ASSET.ASSET_DATA_ID = SMARTPHONES_TYPE_ASSET_DATA.ID\n and SMARTPHONES_TYPE_ASSET.ASSET_TYPE_ID = 33\nunion all\n \n# JBN Wired Phones:\nselect\n "JBN Wired Phones" as 'Item'\n ,PHONE_TYPE_ASSET.NAME as 'Type'\n ,JBN_ASSET.NAME as 'ID'\n ,JBN_ASSET_DATA.FIELD_10095 as 'Last Logged on User'\n ,PHONE_TYPE_ASSET_DATA.FIELD_10113 as 'Unit Cost'\n ,JBN_ASSET.NAME as 'ASSET_NAME'\n ,0 as 'SORT_ORDER'\n ,COST_ACCOUNT_ASSET.NAME as 'COST_ACCOUNT'\n ,8 as 'SECTION_ORDER'\nfrom\n ASSET JBN_ASSET\n join ASSET_DATA_26 JBN_ASSET_DATA on JBN_ASSET.ASSET_DATA_ID = JBN_ASSET_DATA.ID\n and JBN_ASSET.ASSET_TYPE_ID = 26\n join ASSET_ASSOCIATION AA_JBN_COST_ACCOUNT on JBN_ASSET.ID = AA_JBN_COST_ACCOUNT.ASSET_ID\n and AA_JBN_COST_ACCOUNT.ASSET_FIELD_ID = 10097\n join ASSET COST_ACCOUNT_ASSET on AA_JBN_COST_ACCOUNT.ASSOCIATED_ASSET_ID = COST_ACCOUNT_ASSET.ID\n join ASSET_ASSOCIATION AA_JBN_PHONE_TYPE on JBN_ASSET.ID = AA_JBN_PHONE_TYPE.ASSET_ID\n and AA_JBN_PHONE_TYPE.ASSET_FIELD_ID = 10111\n join ASSET PHONE_TYPE_ASSET on AA_JBN_PHONE_TYPE.ASSOCIATED_ASSET_ID = PHONE_TYPE_ASSET.ID\n join ASSET_DATA_30 PHONE_TYPE_ASSET_DATA on PHONE_TYPE_ASSET.ASSET_DATA_ID = PHONE_TYPE_ASSET_DATA.ID\n and PHONE_TYPE_ASSET.ASSET_TYPE_ID = 30\nunion all\n \n# User Accounts\nselect\n "User Account" as 'Item'\n ,USER.EMAIL as 'Type'\n ,ifnull(EMPLOYEE_ID.FIELD_VALUE, "") as 'ID'\n ,USER.FULL_NAME as 'Last Logged on User'\n ,5000 as 'Unit Cost'\n ,USER.USER_NAME as 'ASSET_NAME'\n ,0 as 'SORT_ORDER'\n #,COST_ACCOUNT.FIELD_VALUE as 'COST_ACCOUNT'\n ,concat(substring(COST_ACCOUNT.FIELD_VALUE, 1, 2), ".", substring(COST_ACCOUNT.FIELD_VALUE, 3, 1), ".", substring(COST_ACCOUNT.FIELD_VALUE, 4, 3), ".", substring(COST_ACCOUNT.FIELD_VALUE, 7, 2)) as 'COST_ACCOUNT'\n ,9 as 'SECTION_ORDER'\nfrom\n USER\n join USER_FIELD_VALUE COST_ACCOUNT on USER.ID = COST_ACCOUNT.USER_ID and COST_ACCOUNT.FIELD_ID = 1\n left join USER_FIELD_VALUE EMPLOYEE_ID on USER.ID = EMPLOYEE_ID.USER_ID and EMPLOYEE_ID.FIELD_ID = 2\n left join (select USER_ID from USER_LABEL_JT ULJT join LABEL L on ULJT.LABEL_ID = L.ID and L.NAME = "Disabled Users") D_USER on USER.ID = D_USER.USER_ID\nwhere\n D_USER.USER_ID is null\nunion all\n\n# Email Accounts\nselect\n if((select count(U.ID) from USER U join USER_FIELD_VALUE CA on U.ID = CA.USER_ID and CA.FIELD_ID = 1\n where U.EMAIL = USER.EMAIL and concat(substring(CA.FIELD_VALUE, 1, 2), ".", substring(CA.FIELD_VALUE, 3, 1), ".", substring(CA.FIELD_VALUE, 4, 3), ".", substring(CA.FIELD_VALUE, 7, 2))\n = concat(substring(COST_ACCOUNT.FIELD_VALUE, 1, 2), ".", substring(COST_ACCOUNT.FIELD_VALUE, 3, 1), ".", substring(COST_ACCOUNT.FIELD_VALUE, 4, 3), ".", substring(COST_ACCOUNT.FIELD_VALUE, 7, 2))\n group by EMAIL) > 1, "Shared Email Account", "Email Account") as 'Item'\n# "Email Account" as 'Item'\n ,USER.EMAIL as 'Type'\n ,ifnull(EMPLOYEE_ID.FIELD_VALUE, "") as 'ID'\n ,USER.FULL_NAME as 'Last Logged on User'\n ,5800 / (select count(U.ID) from USER U join USER_FIELD_VALUE CA on U.ID = CA.USER_ID and CA.FIELD_ID = 1\n where U.EMAIL = USER.EMAIL and concat(substring(CA.FIELD_VALUE, 1, 2), ".", substring(CA.FIELD_VALUE, 3, 1), ".", substring(CA.FIELD_VALUE, 4, 3), ".", substring(CA.FIELD_VALUE, 7, 2))\n = concat(substring(COST_ACCOUNT.FIELD_VALUE, 1, 2), ".", substring(COST_ACCOUNT.FIELD_VALUE, 3, 1), ".", substring(COST_ACCOUNT.FIELD_VALUE, 4, 3), ".", substring(COST_ACCOUNT.FIELD_VALUE, 7, 2))\n group by EMAIL) as 'Unit Cost' ,USER.USER_NAME as 'ASSET_NAME'\n ,1 as 'SORT_ORDER'\n #,COST_ACCOUNT.FIELD_VALUE as 'COST_ACCOUNT'\n ,concat(substring(COST_ACCOUNT.FIELD_VALUE, 1, 2), ".", substring(COST_ACCOUNT.FIELD_VALUE, 3, 1), ".", substring(COST_ACCOUNT.FIELD_VALUE, 4, 3), ".", substring(COST_ACCOUNT.FIELD_VALUE, 7, 2)) as 'COST_ACCOUNT'\n ,9 as 'SECTION_ORDER'\nfrom\n USER\n join USER_FIELD_VALUE COST_ACCOUNT on USER.ID = COST_ACCOUNT.USER_ID and COST_ACCOUNT.FIELD_ID = 1\n left join USER_FIELD_VALUE EMPLOYEE_ID on USER.ID = EMPLOYEE_ID.USER_ID and EMPLOYEE_ID.FIELD_ID = 2\n left join (select USER_ID from USER_LABEL_JT ULJT join LABEL L on ULJT.LABEL_ID = L.ID and L.NAME = "Disabled Users") D_USER on USER.ID = D_USER.USER_ID\nwhere\n ifnull(USER.EMAIL, "") not in ("", "noemail@jbn.local")\n and D_USER.USER_ID is null\nunion all\n\n# Internet Accounts\nselect\n "Internet Account" as 'Item'\n ,USER.EMAIL as 'Type'\n ,ifnull(EMPLOYEE_ID.FIELD_VALUE, "") as 'ID'\n ,USER.FULL_NAME as 'Last Logged on User'\n ,9000 as 'Unit Cost'\n ,USER.USER_NAME as 'ASSET_NAME'\n ,2 as 'SORT_ORDER'\n #,COST_ACCOUNT.FIELD_VALUE as 'COST_ACCOUNT'\n ,concat(substring(COST_ACCOUNT.FIELD_VALUE, 1, 2), ".", substring(COST_ACCOUNT.FIELD_VALUE, 3, 1), ".", substring(COST_ACCOUNT.FIELD_VALUE, 4, 3), ".", substring(COST_ACCOUNT.FIELD_VALUE, 7, 2)) as 'COST_ACCOUNT'\n ,9 as 'SECTION_ORDER'\nfrom\n USER\n join USER_FIELD_VALUE COST_ACCOUNT on USER.ID = COST_ACCOUNT.USER_ID and COST_ACCOUNT.FIELD_ID = 1\n left join USER_FIELD_VALUE EMPLOYEE_ID on USER.ID = EMPLOYEE_ID.USER_ID and EMPLOYEE_ID.FIELD_ID = 2\n join USER_LABEL_JT ULJT on USER.ID = ULJT.USER_ID\n join LABEL L on ULJT.LABEL_ID = L.ID and L.NAME in ("LDAP_JBN-Internet-GPO_Germany", "LDAP_JBN-Internet-GPO_local")\n left join (select USER_ID from USER_LABEL_JT ULJT join LABEL L on ULJT.LABEL_ID = L.ID and L.NAME = "Disabled Users") D_USER on USER.ID = D_USER.USER_ID\nwhere\n D_USER.USER_ID is null\n\n\n \n \n /****** TOTAL ROW ******/\nunion all \n select null, null, null, "Total", sum(`Unit Cost`), null, null, `COST_ACCOUNT`, 10 from\n (select\n "Computer" as 'Item'\n ,TYPE_ASSET.NAME as 'Type'\n ,COMPUTER_ASSET.NAME as 'ID'\n ,TYPE_ASSET_DATA.FIELD_10047 as 'Unit Cost'\n ,COMPUTER_ASSET.NAME as 'ASSET_NAME'\n ,0 as 'SORT_ORDER'\n ,COST_ACCOUNT_ASSET.NAME as 'COST_ACCOUNT'\n ,1 as 'SECTION_ORDER'\n from\n ASSET COMPUTER_ASSET\n join ASSET_ASSOCIATION AA_COMPUTER_TYPE on COMPUTER_ASSET.ID = AA_COMPUTER_TYPE.ASSET_ID\n and AA_COMPUTER_TYPE.ASSET_FIELD_ID = 10031\n join ASSET TYPE_ASSET on AA_COMPUTER_TYPE.ASSOCIATED_ASSET_ID = TYPE_ASSET.ID\n and TYPE_ASSET.ASSET_TYPE_ID = 17 # ASSET TYPE ID 17 = "JBN Computer Type"\n join ASSET_DATA_17 TYPE_ASSET_DATA on TYPE_ASSET.ASSET_DATA_ID = TYPE_ASSET_DATA.ID\n join ASSET_ASSOCIATION AA_COMPUTER_COST_ACCOUNT on COMPUTER_ASSET.ID = AA_COMPUTER_COST_ACCOUNT.ASSET_ID\n and AA_COMPUTER_COST_ACCOUNT.ASSET_FIELD_ID = 10030\n join ASSET COST_ACCOUNT_ASSET on AA_COMPUTER_COST_ACCOUNT.ASSOCIATED_ASSET_ID = COST_ACCOUNT_ASSET.ID\n where\n COMPUTER_ASSET.ASSET_TYPE_ID = 5\n union all\n \n # Additional PC Components\n select\n ADDITIONAL_PC_COMPONENTS_ASSET.NAME as 'Item'\n ,"" as 'Type'\n ,"" as 'ID'\n ,ADDITIONAL_PC_COMPONENTS_ASSET_DATA.FIELD_10053 as 'Unit Cost'\n ,COMPUTER_ASSET.NAME as 'ASSET_NAME'\n ,1 as 'SORT_ORDER'\n ,COST_ACCOUNT_ASSET.NAME as 'COST_ACCOUNT'\n ,1 as 'SECTION_ORDER'\n from\n ASSET COMPUTER_ASSET\n join ASSET_ASSOCIATION AA_COMPUTER_ADDITIONAL_PC_COMPONENTS on COMPUTER_ASSET.ID = AA_COMPUTER_ADDITIONAL_PC_COMPONENTS.ASSET_ID\n and AA_COMPUTER_ADDITIONAL_PC_COMPONENTS.ASSET_FIELD_ID = 10072\n join ASSET ADDITIONAL_PC_COMPONENTS_ASSET on AA_COMPUTER_ADDITIONAL_PC_COMPONENTS.ASSOCIATED_ASSET_ID = ADDITIONAL_PC_COMPONENTS_ASSET.ID\n and ADDITIONAL_PC_COMPONENTS_ASSET.ASSET_TYPE_ID = 19 # ASSET TYPE ID 19 = "JBN Additional PC Components"\n join ASSET_DATA_19 ADDITIONAL_PC_COMPONENTS_ASSET_DATA on ADDITIONAL_PC_COMPONENTS_ASSET.ASSET_DATA_ID = ADDITIONAL_PC_COMPONENTS_ASSET_DATA.ID\n join ASSET_ASSOCIATION AA_COMPUTER_COST_ACCOUNT on COMPUTER_ASSET.ID = AA_COMPUTER_COST_ACCOUNT.ASSET_ID\n and AA_COMPUTER_COST_ACCOUNT.ASSET_FIELD_ID = 10030\n join ASSET COST_ACCOUNT_ASSET on AA_COMPUTER_COST_ACCOUNT.ASSOCIATED_ASSET_ID = COST_ACCOUNT_ASSET.ID\n where\n COMPUTER_ASSET.ASSET_TYPE_ID = 5\n union all\n \n # Software (License)\n select\n SC.NAME as 'Item'\n ,"" as 'Type'\n ,"" as 'ID'\n ,LICENSE_ASSET_DATA.FIELD_6 as 'Unit Cost'\n ,COMPUTER_ASSET.NAME as 'ASSET_NAME'\n ,2 as 'SORT_ORDER'\n ,COST_ACCOUNT_ASSET.NAME as 'COST_ACCOUNT'\n ,1 as 'SECTION_ORDER'\n from\n ASSET COMPUTER_ASSET\n join MACHINE M on COMPUTER_ASSET.MAPPED_ID = M.ID\n join SAM_MACHINE_JT SMJT on SMJT.MACHINE_ID = M.ID\n join CATALOG.SAM_CATALOG SC on SMJT.SAM_CATALOG_ID = SC.ID\n join ASSET_CATALOG_ASSOCIATION ACA on SC.ID = ACA.ASSOCIATED_CATALOG_ID\n # and ACA.ASSET_FIELD_ID = 24 # License\n and ACA.ASSET_FIELD_ID = 10000 # License\n join ASSET LICENSE_ASSET on ACA.ASSET_ID = LICENSE_ASSET.ID\n and LICENSE_ASSET.ASSET_TYPE_ID = 7\n join ASSET_DATA_7 LICENSE_ASSET_DATA on LICENSE_ASSET.ASSET_DATA_ID = LICENSE_ASSET_DATA.ID\n join ASSET_ASSOCIATION AA_COMPUTER_COST_ACCOUNT on COMPUTER_ASSET.ID = AA_COMPUTER_COST_ACCOUNT.ASSET_ID\n and AA_COMPUTER_COST_ACCOUNT.ASSET_FIELD_ID = 10030\n join ASSET COST_ACCOUNT_ASSET on AA_COMPUTER_COST_ACCOUNT.ASSOCIATED_ASSET_ID = COST_ACCOUNT_ASSET.ID\n where\n COMPUTER_ASSET.ASSET_TYPE_ID = 5 # TYPE ID 5 = Computer Asset Type\n # and SC.SAM_TYPE IN ('TITLED_APPLICATION','TITLED_SUITE')\n and SC.SOFTWARE_CATEGORY_ID != 32\n and LICENSE_ASSET_DATA.FIELD_6 > 0\n group by\n M.ID, SC.ID\n union all\n \n # Software (JBN software)\n select\n SPECIAL_SOFTWARE_ASSET.NAME as 'Item'\n ,"" as 'Type'\n ,"" as 'ID'\n ,SPECIAL_SOFTWARE_ASSET_DATA.FIELD_10017 as 'Unit Cost'\n ,COMPUTER_ASSET.NAME as 'ASSET_NAME'\n ,2 as 'SORT_ORDER'\n ,COST_ACCOUNT_ASSET.NAME as 'COST_ACCOUNT'\n ,1 as 'SECTION_ORDER'\n from\n ASSET COMPUTER_ASSET\n join MACHINE M on COMPUTER_ASSET.MAPPED_ID = M.ID\n join ASSET_ASSOCIATION AA_SPECIAL_SOFTWARE on COMPUTER_ASSET.ID = AA_SPECIAL_SOFTWARE.ASSET_ID\n and AA_SPECIAL_SOFTWARE.ASSET_FIELD_ID = 10077\n join ASSET SPECIAL_SOFTWARE_ASSET on AA_SPECIAL_SOFTWARE.ASSOCIATED_ASSET_ID = SPECIAL_SOFTWARE_ASSET.ID\n and SPECIAL_SOFTWARE_ASSET.ASSET_TYPE_ID = 12\n join ASSET_DATA_12 SPECIAL_SOFTWARE_ASSET_DATA on SPECIAL_SOFTWARE_ASSET.ASSET_DATA_ID = SPECIAL_SOFTWARE_ASSET_DATA.ID\n join ASSET_ASSOCIATION AA_COMPUTER_COST_ACCOUNT on COMPUTER_ASSET.ID = AA_COMPUTER_COST_ACCOUNT.ASSET_ID\n and AA_COMPUTER_COST_ACCOUNT.ASSET_FIELD_ID = 10030\n join ASSET COST_ACCOUNT_ASSET on AA_COMPUTER_COST_ACCOUNT.ASSOCIATED_ASSET_ID = COST_ACCOUNT_ASSET.ID\n where\n COMPUTER_ASSET.ASSET_TYPE_ID = 5 # TYPE ID 5 = Computer Asset Type\n group by\n M.ID, SPECIAL_SOFTWARE_ASSET.ID\n union all\n \n # JBN Car radio unit:\n select\n "JBN Car radio unit" as 'Item'\n ,JBN_ASSET_DATA.FIELD_10106 as 'Type'\n ,JBN_ASSET.NAME as 'ID'\n ,JBN_ASSET_DATA.FIELD_10109 as 'Unit Cost'\n ,JBN_ASSET.NAME as 'ASSET_NAME'\n ,0 as 'SORT_ORDER'\n ,COST_ACCOUNT_ASSET.NAME as 'COST_ACCOUNT'\n ,2 as 'SECTION_ORDER'\n from\n ASSET JBN_ASSET\n join ASSET_DATA_29 JBN_ASSET_DATA on JBN_ASSET.ASSET_DATA_ID = JBN_ASSET_DATA.ID\n and JBN_ASSET.ASSET_TYPE_ID = 29\n join ASSET_ASSOCIATION AA_JBN_COST_ACCOUNT on JBN_ASSET.ID = AA_JBN_COST_ACCOUNT.ASSET_ID\n and AA_JBN_COST_ACCOUNT.ASSET_FIELD_ID = 10110\n join ASSET COST_ACCOUNT_ASSET on AA_JBN_COST_ACCOUNT.ASSOCIATED_ASSET_ID = COST_ACCOUNT_ASSET.ID\n union all\n \n # JBN Hand radio unit:\n select\n "JBN Hand radio unit" as 'Item'\n ,JBN_ASSET_DATA.FIELD_10099 as 'Type'\n ,JBN_ASSET.NAME as 'ID'\n ,JBN_ASSET_DATA.FIELD_10093 as 'Unit Cost'\n ,JBN_ASSET.NAME as 'ASSET_NAME'\n ,0 as 'SORT_ORDER'\n ,COST_ACCOUNT_ASSET.NAME as 'COST_ACCOUNT'\n ,3 as 'SECTION_ORDER'\n from\n ASSET JBN_ASSET\n join ASSET_DATA_25 JBN_ASSET_DATA on JBN_ASSET.ASSET_DATA_ID = JBN_ASSET_DATA.ID\n and JBN_ASSET.ASSET_TYPE_ID = 25\n join ASSET_ASSOCIATION AA_JBN_COST_ACCOUNT on JBN_ASSET.ID = AA_JBN_COST_ACCOUNT.ASSET_ID\n and AA_JBN_COST_ACCOUNT.ASSET_FIELD_ID = 10094\n join ASSET COST_ACCOUNT_ASSET on AA_JBN_COST_ACCOUNT.ASSOCIATED_ASSET_ID = COST_ACCOUNT_ASSET.ID\n union all\n \n # JBN Ipads:\n select\n "JBN Ipads" as 'Item'\n ,IPAD_TYPE_ASSET.NAME as 'Type'\n ,JBN_ASSET.NAME as 'ID'\n ,IPAD_TYPE_ASSET_DATA.FIELD_10115 as 'Unit Cost'\n ,JBN_ASSET.NAME as 'ASSET_NAME'\n ,0 as 'SORT_ORDER'\n ,COST_ACCOUNT_ASSET.NAME as 'COST_ACCOUNT'\n ,4 as 'SECTION_ORDER'\n from\n ASSET JBN_ASSET\n join ASSET_DATA_24 JBN_ASSET_DATA on JBN_ASSET.ASSET_DATA_ID = JBN_ASSET_DATA.ID\n and JBN_ASSET.ASSET_TYPE_ID = 24\n join ASSET_ASSOCIATION AA_JBN_COST_ACCOUNT on JBN_ASSET.ID = AA_JBN_COST_ACCOUNT.ASSET_ID\n and AA_JBN_COST_ACCOUNT.ASSET_FIELD_ID = 10088\n join ASSET COST_ACCOUNT_ASSET on AA_JBN_COST_ACCOUNT.ASSOCIATED_ASSET_ID = COST_ACCOUNT_ASSET.ID\n join ASSET_ASSOCIATION AA_JBN_IPAD_TYPE on JBN_ASSET.ID = AA_JBN_IPAD_TYPE.ASSET_ID\n and AA_JBN_IPAD_TYPE.ASSET_FIELD_ID = 10116\n join ASSET IPAD_TYPE_ASSET on AA_JBN_IPAD_TYPE.ASSOCIATED_ASSET_ID = IPAD_TYPE_ASSET.ID\n join ASSET_DATA_31 IPAD_TYPE_ASSET_DATA on IPAD_TYPE_ASSET.ASSET_DATA_ID = IPAD_TYPE_ASSET_DATA.ID\n and IPAD_TYPE_ASSET.ASSET_TYPE_ID = 31\n union all\n \n # JBN Sattelite Phone (Thuraya):\n select\n "JBN Sattelite Phone (Thuraya)" as 'Item'\n ,SATTELITE_PHONE_TYPE_ASSET.NAME as 'Type'\n ,JBN_ASSET.NAME as 'ID'\n ,SATTELITE_PHONE_TYPE_ASSET_DATA.FIELD_10118 as 'Unit Cost'\n ,JBN_ASSET.NAME as 'ASSET_NAME'\n ,0 as 'SORT_ORDER'\n ,COST_ACCOUNT_ASSET.NAME as 'COST_ACCOUNT'\n ,5 as 'SECTION_ORDER'\n from\n ASSET JBN_ASSET\n join ASSET_DATA_22 JBN_ASSET_DATA on JBN_ASSET.ASSET_DATA_ID = JBN_ASSET_DATA.ID\n and JBN_ASSET.ASSET_TYPE_ID = 22\n join ASSET_ASSOCIATION AA_JBN_COST_ACCOUNT on JBN_ASSET.ID = AA_JBN_COST_ACCOUNT.ASSET_ID\n and AA_JBN_COST_ACCOUNT.ASSET_FIELD_ID = 10084\n join ASSET COST_ACCOUNT_ASSET on AA_JBN_COST_ACCOUNT.ASSOCIATED_ASSET_ID = COST_ACCOUNT_ASSET.ID\n join ASSET_ASSOCIATION AA_JBN_SATTELITE_PHONE_TYPE on JBN_ASSET.ID = AA_JBN_SATTELITE_PHONE_TYPE.ASSET_ID\n and AA_JBN_SATTELITE_PHONE_TYPE.ASSET_FIELD_ID = 10119\n join ASSET SATTELITE_PHONE_TYPE_ASSET on AA_JBN_SATTELITE_PHONE_TYPE.ASSOCIATED_ASSET_ID = SATTELITE_PHONE_TYPE_ASSET.ID\n join ASSET_DATA_32 SATTELITE_PHONE_TYPE_ASSET_DATA on SATTELITE_PHONE_TYPE_ASSET.ASSET_DATA_ID = SATTELITE_PHONE_TYPE_ASSET_DATA.ID\n and SATTELITE_PHONE_TYPE_ASSET.ASSET_TYPE_ID = 32\n union all\n \n # JBN Sites & Fees:\n select\n "JBN Sites & Fees" as 'Item'\n ,SITES_FEES_TYPE_ASSET.NAME as 'Type'\n ,JBN_ASSET.NAME as 'ID'\n ,SITES_FEES_TYPE_ASSET_DATA.FIELD_10123 as 'Unit Cost'\n ,JBN_ASSET.NAME as 'ASSET_NAME'\n ,0 as 'SORT_ORDER'\n ,COST_ACCOUNT_ASSET.NAME as 'COST_ACCOUNT'\n ,6 as 'SECTION_ORDER'\n from\n ASSET JBN_ASSET\n join ASSET_DATA_20 JBN_ASSET_DATA on JBN_ASSET.ASSET_DATA_ID = JBN_ASSET_DATA.ID\n and JBN_ASSET.ASSET_TYPE_ID = 20\n join ASSET_ASSOCIATION AA_JBN_COST_ACCOUNT on JBN_ASSET.ID = AA_JBN_COST_ACCOUNT.ASSET_ID\n and AA_JBN_COST_ACCOUNT.ASSET_FIELD_ID = 10101\n join ASSET COST_ACCOUNT_ASSET on AA_JBN_COST_ACCOUNT.ASSOCIATED_ASSET_ID = COST_ACCOUNT_ASSET.ID\n join ASSET_ASSOCIATION AA_JBN_SITES_FEES_TYPE on JBN_ASSET.ID = AA_JBN_SITES_FEES_TYPE.ASSET_ID\n and AA_JBN_SITES_FEES_TYPE.ASSET_FIELD_ID = 10124\n join ASSET SITES_FEES_TYPE_ASSET on AA_JBN_SITES_FEES_TYPE.ASSOCIATED_ASSET_ID = SITES_FEES_TYPE_ASSET.ID\n join ASSET_DATA_34 SITES_FEES_TYPE_ASSET_DATA on SITES_FEES_TYPE_ASSET.ASSET_DATA_ID = SITES_FEES_TYPE_ASSET_DATA.ID\n and SITES_FEES_TYPE_ASSET.ASSET_TYPE_ID = 34\n union all\n \n # JBN Smartphones:\n select\n "JBN Smartphones" as 'Item'\n ,SMARTPHONES_TYPE_ASSET.NAME as 'Type'\n ,JBN_ASSET.NAME as 'ID'\n ,SMARTPHONES_TYPE_ASSET_DATA.FIELD_10121 as 'Unit Cost'\n ,JBN_ASSET.NAME as 'ASSET_NAME'\n ,0 as 'SORT_ORDER'\n ,COST_ACCOUNT_ASSET.NAME as 'COST_ACCOUNT'\n ,7 as 'SECTION_ORDER'\n from\n ASSET JBN_ASSET\n join ASSET_DATA_23 JBN_ASSET_DATA on JBN_ASSET.ASSET_DATA_ID = JBN_ASSET_DATA.ID\n and JBN_ASSET.ASSET_TYPE_ID = 23\n join ASSET_ASSOCIATION AA_JBN_COST_ACCOUNT on JBN_ASSET.ID = AA_JBN_COST_ACCOUNT.ASSET_ID\n and AA_JBN_COST_ACCOUNT.ASSET_FIELD_ID = 10086\n join ASSET COST_ACCOUNT_ASSET on AA_JBN_COST_ACCOUNT.ASSOCIATED_ASSET_ID = COST_ACCOUNT_ASSET.ID\n join ASSET_ASSOCIATION AA_JBN_SMARTPHONES_TYPE on JBN_ASSET.ID = AA_JBN_SMARTPHONES_TYPE.ASSET_ID\n and AA_JBN_SMARTPHONES_TYPE.ASSET_FIELD_ID = 10122\n join ASSET SMARTPHONES_TYPE_ASSET on AA_JBN_SMARTPHONES_TYPE.ASSOCIATED_ASSET_ID = SMARTPHONES_TYPE_ASSET.ID\n join ASSET_DATA_33 SMARTPHONES_TYPE_ASSET_DATA on SMARTPHONES_TYPE_ASSET.ASSET_DATA_ID = SMARTPHONES_TYPE_ASSET_DATA.ID\n and SMARTPHONES_TYPE_ASSET.ASSET_TYPE_ID = 33\n union all\n \n # JBN Wired Phones:\n select\n "JBN Wired Phones" as 'Item'\n ,PHONE_TYPE_ASSET.NAME as 'Type'\n ,JBN_ASSET.NAME as 'ID'\n ,PHONE_TYPE_ASSET_DATA.FIELD_10113 as 'Unit Cost'\n ,JBN_ASSET.NAME as 'ASSET_NAME'\n ,0 as 'SORT_ORDER'\n ,COST_ACCOUNT_ASSET.NAME as 'COST_ACCOUNT'\n ,8 as 'SECTION_ORDER'\n from\n ASSET JBN_ASSET\n join ASSET_DATA_26 JBN_ASSET_DATA on JBN_ASSET.ASSET_DATA_ID = JBN_ASSET_DATA.ID\n and JBN_ASSET.ASSET_TYPE_ID = 26\n join ASSET_ASSOCIATION AA_JBN_COST_ACCOUNT on JBN_ASSET.ID = AA_JBN_COST_ACCOUNT.ASSET_ID\n and AA_JBN_COST_ACCOUNT.ASSET_FIELD_ID = 10097\n join ASSET COST_ACCOUNT_ASSET on AA_JBN_COST_ACCOUNT.ASSOCIATED_ASSET_ID = COST_ACCOUNT_ASSET.ID\n join ASSET_ASSOCIATION AA_JBN_PHONE_TYPE on JBN_ASSET.ID = AA_JBN_PHONE_TYPE.ASSET_ID\n and AA_JBN_PHONE_TYPE.ASSET_FIELD_ID = 10111\n join ASSET PHONE_TYPE_ASSET on AA_JBN_PHONE_TYPE.ASSOCIATED_ASSET_ID = PHONE_TYPE_ASSET.ID\n join ASSET_DATA_30 PHONE_TYPE_ASSET_DATA on PHONE_TYPE_ASSET.ASSET_DATA_ID = PHONE_TYPE_ASSET_DATA.ID\n and PHONE_TYPE_ASSET.ASSET_TYPE_ID = 30\n\n\n union all\n \n # User Accounts\n select\n "User Account" as 'Item'\n ,USER.EMAIL as 'Type'\n ,ifnull(EMPLOYEE_ID.FIELD_VALUE, "") as 'ID'\n ,5000 as 'Unit Cost'\n ,USER.USER_NAME as 'ASSET_NAME'\n ,0 as 'SORT_ORDER'\n #,COST_ACCOUNT.FIELD_VALUE as 'COST_ACCOUNT'\n ,concat(substring(COST_ACCOUNT.FIELD_VALUE, 1, 2), ".", substring(COST_ACCOUNT.FIELD_VALUE, 3, 1), ".", substring(COST_ACCOUNT.FIELD_VALUE, 4, 3), ".", substring(COST_ACCOUNT.FIELD_VALUE, 7, 2)) as 'COST_ACCOUNT'\n ,9 as 'SECTION_ORDER'\nfrom\n USER\n join USER_FIELD_VALUE COST_ACCOUNT on USER.ID = COST_ACCOUNT.USER_ID and COST_ACCOUNT.FIELD_ID = 1\n left join USER_FIELD_VALUE EMPLOYEE_ID on USER.ID = EMPLOYEE_ID.USER_ID and EMPLOYEE_ID.FIELD_ID = 2\n left join (select USER_ID from USER_LABEL_JT ULJT join LABEL L on ULJT.LABEL_ID = L.ID and L.NAME = "Disabled Users") D_USER on USER.ID = D_USER.USER_ID\nwhere\n D_USER.USER_ID is null\nunion all\n\n# Email Accounts\nselect\n "Email Account" as 'Item'\n ,USER.EMAIL as 'Type'\n ,ifnull(EMPLOYEE_ID.FIELD_VALUE, "") as 'ID'\n ,5800 / (select count(U.ID) from USER U join USER_FIELD_VALUE CA on U.ID = CA.USER_ID and CA.FIELD_ID = 1\n where U.EMAIL = USER.EMAIL and concat(substring(CA.FIELD_VALUE, 1, 2), ".", substring(CA.FIELD_VALUE, 3, 1), ".", substring(CA.FIELD_VALUE, 4, 3), ".", substring(CA.FIELD_VALUE, 7, 2))\n = concat(substring(COST_ACCOUNT.FIELD_VALUE, 1, 2), ".", substring(COST_ACCOUNT.FIELD_VALUE, 3, 1), ".", substring(COST_ACCOUNT.FIELD_VALUE, 4, 3), ".", substring(COST_ACCOUNT.FIELD_VALUE, 7, 2))\n group by EMAIL) as 'Unit Cost'\n ,USER.USER_NAME as 'ASSET_NAME'\n ,1 as 'SORT_ORDER'\n #,COST_ACCOUNT.FIELD_VALUE as 'COST_ACCOUNT'\n ,concat(substring(COST_ACCOUNT.FIELD_VALUE, 1, 2), ".", substring(COST_ACCOUNT.FIELD_VALUE, 3, 1), ".", substring(COST_ACCOUNT.FIELD_VALUE, 4, 3), ".", substring(COST_ACCOUNT.FIELD_VALUE, 7, 2)) as 'COST_ACCOUNT'\n ,9 as 'SECTION_ORDER'\nfrom\n USER\n join USER_FIELD_VALUE COST_ACCOUNT on USER.ID = COST_ACCOUNT.USER_ID and COST_ACCOUNT.FIELD_ID = 1\n left join USER_FIELD_VALUE EMPLOYEE_ID on USER.ID = EMPLOYEE_ID.USER_ID and EMPLOYEE_ID.FIELD_ID = 2\n left join (select USER_ID from USER_LABEL_JT ULJT join LABEL L on ULJT.LABEL_ID = L.ID and L.NAME = "Disabled Users") D_USER on USER.ID = D_USER.USER_ID\nwhere\n ifnull(USER.EMAIL, "") not in ("", "noemail@jbn.local")\n and D_USER.USER_ID is null\nunion all\n\n# Internet Access\nselect\n "Internet Access" as 'Item'\n ,USER.EMAIL as 'Type'\n ,ifnull(EMPLOYEE_ID.FIELD_VALUE, "") as 'ID'\n ,9000 as 'Unit Cost'\n ,USER.USER_NAME as 'ASSET_NAME'\n ,2 as 'SORT_ORDER'\n #,COST_ACCOUNT.FIELD_VALUE as 'COST_ACCOUNT'\n ,concat(substring(COST_ACCOUNT.FIELD_VALUE, 1, 2), ".", substring(COST_ACCOUNT.FIELD_VALUE, 3, 1), ".", substring(COST_ACCOUNT.FIELD_VALUE, 4, 3), ".", substring(COST_ACCOUNT.FIELD_VALUE, 7, 2)) as 'COST_ACCOUNT'\n ,9 as 'SECTION_ORDER'\nfrom\n USER\n join USER_FIELD_VALUE COST_ACCOUNT on USER.ID = COST_ACCOUNT.USER_ID and COST_ACCOUNT.FIELD_ID = 1\n left join USER_FIELD_VALUE EMPLOYEE_ID on USER.ID = EMPLOYEE_ID.USER_ID and EMPLOYEE_ID.FIELD_ID = 2\n join USER_LABEL_JT ULJT on USER.ID = ULJT.USER_ID\n join LABEL L on ULJT.LABEL_ID = L.ID and L.NAME in ("LDAP_JBN-Internet-GPO_Germany", "LDAP_JBN-Internet-GPO_local")\n left join (select USER_ID from USER_LABEL_JT ULJT join LABEL L on ULJT.LABEL_ID = L.ID and L.NAME = "Disabled Users") D_USER on USER.ID = D_USER.USER_ID\nwhere\n D_USER.USER_ID is null\n\n ) TOTAL_TABLE\n group by\n `COST_ACCOUNT`\n \n ) ALL_ASSETS\n where\n \n /* SET Cost Account Here! */ ALL_ASSETS.COST_ACCOUNT = "15.0.005.00"\n \n order by\n ALL_ASSETS.SECTION_ORDER, ALL_ASSETS.ASSET_NAME, ALL_ASSETS.SORT_ORDER, ALL_ASSETS.ITEM LIMIT 0")\n - akmagnum 4 years ago