/build/static/layout/Breadcrumb_cap_w.png

Help with adding a column to a custom SQL report

Hi friends


We have a custom SQL report that was made for use by Kace professional services.

It's a report that ADDS the total cost of inventoried assets.

Now all I need is ONE thing. To add a column to this report. 

Actually I need the "last inventory" for listed devices to show up on the report.

I can do this with the normal reports wizard in Kace....., but as I said this is a custom report.

Anyone know some SQL code I need to add to the SQL code section to get his done?

Else, I will need to get pro services to pay for this again. 

And I think this is just a snippet of code I need to add to make this happen.

A few suggestions I could test would be helpful.

Thank all.


.......I am posting the section of the SQL code that deals with the computer asset for the gurus who might suggest an edit.

.......Also, I have added the "Last inventory" field in the "Asset types" from the "device field" section.




# Asset Report - Cost Account: 15.0.005.00 - 20180709

select `Item`, `Type`, `ID`, `Last Logged on User`, if(`Unit Cost` != "", format(`Unit Cost`, 2), "") as 'Unit Cost (₦)'

from

(

# Header Query

select

  "Cost Account:" as 'Item'

  ,COST_ACCOUNT_ASSET.NAME as 'Type'

  ,concat("Department: ", COST_ACCOUNT_ASSET_DATA.FIELD_10025) as 'ID'

  ,concat("Site: ", COST_ACCOUNT_ASSET_DATA.FIELD_10026) as 'Last Logged on User'

  ,"" as 'Unit Cost'

  ,null as 'ASSET_NAME'

  ,null as 'SORT_ORDER'

  ,COST_ACCOUNT_ASSET.NAME as 'COST_ACCOUNT'

  ,-1 as 'SECTION_ORDER'

from

  ASSET COST_ACCOUNT_ASSET

  join ASSET_DATA_15 COST_ACCOUNT_ASSET_DATA on COST_ACCOUNT_ASSET.ASSET_DATA_ID = COST_ACCOUNT_ASSET_DATA.ID

    and COST_ACCOUNT_ASSET.ASSET_TYPE_ID = 15 # Asset type ID 15 = "JBN Cost Accounts"

group by

  `Type`

union all

 

# Computer Assets

select

  "Computer" as 'Item'

  ,TYPE_ASSET.NAME as 'Type'

  ,COMPUTER_ASSET.NAME as 'ID'

  ,M.USER_LOGGED as 'Last Logged on User'

  ,TYPE_ASSET_DATA.FIELD_10047 as 'Unit Cost'

  ,COMPUTER_ASSET.NAME as 'ASSET_NAME'

  ,0 as 'SORT_ORDER'

  ,COST_ACCOUNT_ASSET.NAME as 'COST_ACCOUNT'

  ,1 as 'SECTION_ORDER'

from

  ASSET COMPUTER_ASSET

  left join MACHINE M on COMPUTER_ASSET.MAPPED_ID = M.ID

  join ASSET_ASSOCIATION AA_COMPUTER_TYPE on COMPUTER_ASSET.ID = AA_COMPUTER_TYPE.ASSET_ID

    and AA_COMPUTER_TYPE.ASSET_FIELD_ID = 10031

  join ASSET TYPE_ASSET on AA_COMPUTER_TYPE.ASSOCIATED_ASSET_ID = TYPE_ASSET.ID

    and TYPE_ASSET.ASSET_TYPE_ID = 17 # ASSET TYPE ID 17 = "JBN Computer Type"

  join ASSET_DATA_17 TYPE_ASSET_DATA on TYPE_ASSET.ASSET_DATA_ID = TYPE_ASSET_DATA.ID

  join ASSET_ASSOCIATION AA_COMPUTER_COST_ACCOUNT on COMPUTER_ASSET.ID = AA_COMPUTER_COST_ACCOUNT.ASSET_ID

    and AA_COMPUTER_COST_ACCOUNT.ASSET_FIELD_ID = 10030

  join ASSET COST_ACCOUNT_ASSET on AA_COMPUTER_COST_ACCOUNT.ASSOCIATED_ASSET_ID = COST_ACCOUNT_ASSET.ID

where

  COMPUTER_ASSET.ASSET_TYPE_ID = 5

union all


0 Comments   [ + ] Show comments

Answers (1)

Posted by: KevinG 3 years ago
Red Belt
0

After the line

  ,M.USER_LOGGED as 'Last Logged on User'

add


  ,M.LAST_INVENTORY as 'Last Inventory'


Comments:
  • Hi kevin.
    gives an error

    mysqli error: [1222: The used SELECT statements have a different number of columns] in EXECUTE - akmagnum 3 years ago
    • We would need to see the complete SQL statement to determine the issue. - KevinG 3 years ago
      • 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 3 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ