/build/static/layout/Breadcrumb_cap_w.png

Warranty and Budget Code

I am using the canned Warranty report and I am trying to add the User's Budget code. I am getting output, but the budget Code is not displaying all the Correct information. There are a lot of 1's in the column's and also it is not assigning the correct Budget Code with the user. We need this to be able to send a report to specific departments when it is time to upgrade PC's at Budget Time.

 

SELECT M.NAME AS MACHINE_NAME, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DA.SHIP_DATE,M.USER_LOGGED AS LAST_LOGGED_IN_USER, DW.SERVICE_LEVEL_CODE,
BUDGET_CODE,
DW.SERVICE_LEVEL_DESCRIPTION, DW.END_DATE AS EXPIRATION_DATE
FROM (DELL_WARRANTY DW, ORG1.USER) JOIN DELL_ASSET DA ON (DW.SERVICE_TAG = DA.SERVICE_TAG)
JOIN MACHINE M ON (M.BIOS_SERIAL_NUMBER = DA.PARENT_SERVICE_TAG OR M.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG)
LEFT JOIN DELL_WARRANTY DW2 ON DW2.SERVICE_TAG=DW.SERVICE_TAG and DW2.END_DATE > NOW()
WHERE M.CS_MANUFACTURER LIKE '%dell%'
AND M.BIOS_SERIAL_NUMBER!=''
AND DA.DISABLED != 1
AND DW.END_DATE < NOW()
AND DW2.SERVICE_TAG IS NULL;

Asked 11 years ago 1476 views
0 Comments   [ + ] Show comments

Answers (1)

Posted by: dugullett 11 years ago
Red Belt
0

I think the problem is there's no good way to join the user table. The only field I can see would be the user field, but that depends on who was logged in at the time of check in. So that way would not be 100% accurate. Try this and let me know. I don't use budget codes so you'll have to tell me.

SELECT M.NAME AS MACHINE_NAME, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DA.SHIP_DATE,M.USER_LOGGED AS LAST_LOGGED_IN_USER, DW.SERVICE_LEVEL_CODE,

U.BUDGET_CODE,

DW.SERVICE_LEVEL_DESCRIPTION, DW.END_DATE AS EXPIRATION_DATE

FROM DELL_WARRANTY DW

JOIN DELL_ASSET DA ON (DW.SERVICE_TAG = DA.SERVICE_TAG)

JOIN MACHINE M ON (M.BIOS_SERIAL_NUMBER = DA.PARENT_SERVICE_TAG OR M.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG)

LEFT JOIN USER U ON U.USER_NAME = M.USER

LEFT JOIN DELL_WARRANTY DW2 ON DW2.SERVICE_TAG=DW.SERVICE_TAG and DW2.END_DATE > NOW()

WHERE M.CS_MANUFACTURER LIKE '%dell%'

AND M.BIOS_SERIAL_NUMBER!=''

AND DA.DISABLED != 1

AND DW.END_DATE < NOW()

AND DW2.SERVICE_TAG IS NULL
Comments:
  • By any chance do your different departments have separate labels? If so this might be a little better. Change the last line to match your label name.

    SELECT DISTINCT M.NAME AS MACHINE_NAME, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DA.SHIP_DATE,M.USER_LOGGED AS LAST_LOGGED_IN_USER, DW.SERVICE_LEVEL_CODE,
    DW.SERVICE_LEVEL_DESCRIPTION, DW.END_DATE AS EXPIRATION_DATE
    FROM DELL_WARRANTY DW
    JOIN DELL_ASSET DA ON (DW.SERVICE_TAG = DA.SERVICE_TAG)
    JOIN MACHINE M ON (M.BIOS_SERIAL_NUMBER = DA.PARENT_SERVICE_TAG OR M.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG)
    LEFT JOIN MACHINE_LABEL_JT MLJT ON MLJT.MACHINE_ID = M.ID
    LEFT JOIN LABEL L ON L.ID = MLJT.LABEL_ID
    LEFT JOIN DELL_WARRANTY DW2 ON DW2.SERVICE_TAG=DW.SERVICE_TAG and DW2.END_DATE > NOW()
    WHERE M.CS_MANUFACTURER LIKE '%dell%'
    AND M.BIOS_SERIAL_NUMBER!=''
    AND DA.DISABLED != 1
    AND DW.END_DATE < NOW()
    AND DW2.SERVICE_TAG IS NULL
    AND L.NAME LIKE '<LABEL NAME>' - dugullett 11 years ago
  • We do not have seperate labels for Departments at this time, but I like this idea - svierneisel 11 years ago
    • Actually, the above will work for my other offices. We do use labels for each facility. Thank You for this as well! - svierneisel 11 years ago
  • I think you nailed it! Great job! Thankyou SO much!

    I would love to be able to do this with the Windows 7 Readiness Report as well. I am having the same issues with it. could you look at this one as well?

    select *
    from
    (
    SELECT

    MACHINE.ID,
    MACHINE.NAME AS NAME,
    USER_FULLNAME,
    BUDGET_CODE as DEPT,
    OS_NAME,
    OS_ARCH as ARCH,
    if(OS_NAME like '%Vista%' and SERVICE_PACK RLIKE '[12]',
    if(OS_NAME like '%RTM%' OR OS_NAME like '%Starter%','Install Only', 'Can be Upgraded'),'Install Only'
    ) as UPGRADABLE,
    PROCESSORS,
    RAM_TOTAL,
    SUM(MACHINE_DISKS.DISK_SIZE) AS DISK_SIZE,
    MACHINE_DISKS.DISK_FREE AS DISK_FREE,
    VIDEO_CONTROLLERS as VIDEO
    FROM (MACHINE, ORG1.USER)
    LEFT JOIN MACHINE_DISKS
    ON
    (MACHINE_DISKS.ID = MACHINE.ID)
    WHERE OS_NAME not like '%Mac%'
    and OS_NAME not like '%Windows 7%'
    and
    (1 in
    (
    select 1 from MACHINE_DISKS where MACHINE_DISKS.ID = MACHINE.ID
    and
    if(MACHINE.OS_ARCH like '%x86%',MACHINE_DISKS.DISK_FREE >= '16',MACHINE_DISKS.DISK_FREE >= '20')
    )
    )
    AND
    if(MACHINE.OS_ARCH like '%x86%', RAM_TOTAL >= '1000',RAM_TOTAL >= '2000')
    AND PROCESSORS NOT LIKE '%Mhz%'
    group by MACHINE.ID
    ) as test

    union all

    select *
    from
    (
    SELECT

    MACHINE.ID,
    MACHINE.NAME AS NAME,
    USER_FULLNAME,
    BUDGET_CODE as Dept,
    OS_NAME,
    OS_ARCH as ARCH,
    'Not Enough Disk' as UPGRADABLE,
    PROCESSORS,
    RAM_TOTAL,
    DISK_SIZE AS DISK_SIZE,
    MACHINE_DISKS.DISK_FREE AS MACHINE_DISKS_DISK_FREE,
    VIDEO_CONTROLLERS as VIDEO
    FROM (MACHINE, ORG1.USER)
    LEFT JOIN MACHINE_DISKS
    ON
    (MACHINE_DISKS.ID = MACHINE.ID)
    where OS_NAME NOT LIKE '%Mac%'
    and OS_NAME not like '%Windows 7%'
    and
    (1 not in
    (
    select 1 from MACHINE_DISKS where MACHINE_DISKS.ID = MACHINE.ID
    and
    if(MACHINE.OS_ARCH like '%x86%',MACHINE_DISKS.DISK_FREE >= '16',MACHINE_DISKS.DISK_FREE >= '20')
    )
    )
    GROUP BY MACHINE.ID
    ) as disk

    UNION ALL

    select *
    from
    (
    SELECT

    MACHINE.ID,
    MACHINE.NAME AS NAME,
    USER_FULLNAME,
    BUDGET_CODE as DEPT,
    OS_NAME,
    OS_ARCH as ARCH,
    'Not enough Memory' as UPGRADABLE,
    PROCESSORS,
    RAM_TOTAL,
    MACHINE_DISKS.DISK_SIZE AS DISK_SIZE,
    DISK_FREE AS MACHINE_DISKS_DISK_FREE,
    VIDEO_CONTROLLERS as VIDEO
    FROM (MACHINE, ORG1.USER)
    LEFT JOIN MACHINE_DISKS
    ON
    (MACHINE_DISKS.ID = MACHINE.ID)
    where OS_NAME NOT LIKE '%Mac%'
    and OS_NAME not like '%Windows 7%'
    and
    if(MACHINE.OS_ARCH like '%x86%', RAM_TOTAL < '1000',RAM_TOTAL < '2000')
    GROUP BY MACHINE.ID
    ) as ram

    union all

    select *
    from
    (
    SELECT

    MACHINE.ID,
    MACHINE.NAME AS NAME,
    USER_FULLNAME,
    BUDGET_CODE as DEPT,
    OS_NAME,
    OS_ARCH as ARCH,
    'Not enough Processor Speed' as UPGRADABLE,
    PROCESSORS,
    RAM_TOTAL,
    MACHINE_DISKS.DISK_SIZE AS DISK_SIZE,
    MACHINE_DISKS.DISK_FREE AS DISK_FREE,
    VIDEO_CONTROLLERS as VIDEO
    FROM (MACHINE, ORG1.USER)
    LEFT JOIN MACHINE_DISKS
    ON
    (MACHINE_DISKS.ID = MACHINE.ID)
    where OS_NAME NOT LIKE '%Mac%'
    and OS_NAME not like '%Windows 7%'
    AND
    PROCESSORS LIKE '%Mhz%'
    GROUP BY MACHINE.ID
    ) as processor

    union all

    select *
    from
    (
    SELECT

    MACHINE.ID,
    MACHINE.NAME AS NAME,
    USER_FULLNAME,
    BUDGET_CODE as DEPT,
    OS_NAME,
    OS_ARCH as ARCH,
    'Not Required (Windows 7 already Installed)' as UPGRADABLE,
    PROCESSORS,
    RAM_TOTAL,
    MACHINE_DISKS.DISK_SIZE AS DISK_SIZE,
    MACHINE_DISKS.DISK_FREE AS DISK_FREE,
    VIDEO_CONTROLLERS as VIDEO
    FROM (MACHINE, ORG1.USER)
    LEFT JOIN MACHINE_DISKS
    ON
    (MACHINE_DISKS.ID = MACHINE.ID)
    where OS_NAME like '%Windows 7%'
    GROUP BY MACHINE.ID
    ) as Win7


    order by UPGRADABLE, NAME - svierneisel 11 years ago
    • This should work. (Not tested)

      select *
      from
      ( SELECT
      MACHINE.ID,
      MACHINE.NAME AS NAME,
      USER_FULLNAME,
      U.BUDGET_CODE AS DEPT,
      OS_NAME,
      OS_ARCH as ARCH,
      if(OS_NAME like '%Vista%' and SERVICE_PACK RLIKE '[12]',
      if(OS_NAME like '%RTM%' OR OS_NAME like '%Starter%','Install Only', 'Can be Upgraded'),'Install Only'
      ) as UPGRADABLE,
      PROCESSORS,
      RAM_TOTAL,
      SUM(MACHINE_DISKS.DISK_SIZE) AS DISK_SIZE,
      MACHINE_DISKS.DISK_FREE AS DISK_FREE,
      VIDEO_CONTROLLERS as VIDEO
      FROM MACHINE
      LEFT JOIN MACHINE_DISKS
      ON (MACHINE_DISKS.ID = MACHINE.ID)
      LEFT JOIN USER U ON U.USER_NAME = MACHINE.USER
      WHERE OS_NAME not like '%Mac%'
      and OS_NAME not like '%Windows 7%'
      and
      (1 in
      (
      select 1 from MACHINE_DISKS where MACHINE_DISKS.ID = MACHINE.ID
      and
      if(MACHINE.OS_ARCH like '%x86%',MACHINE_DISKS.DISK_FREE >= '16',MACHINE_DISKS.DISK_FREE >= '20')
      )
      )
      AND
      if(MACHINE.OS_ARCH like '%x86%', RAM_TOTAL >= '1000',RAM_TOTAL >= '2000')
      AND PROCESSORS NOT LIKE '%Mhz%'
      group by MACHINE.ID
      ) as test

      union all

      select *
      from
      (
      SELECT
      MACHINE.ID,
      MACHINE.NAME AS NAME,
      USER_FULLNAME,
      U.BUDGET_CODE AS DEPT,
      OS_NAME,
      OS_ARCH as ARCH,
      'Not Enough Disk' as UPGRADABLE,
      PROCESSORS,
      RAM_TOTAL,
      DISK_SIZE AS DISK_SIZE,
      MACHINE_DISKS.DISK_FREE AS MACHINE_DISKS_DISK_FREE,
      VIDEO_CONTROLLERS as VIDEO
      FROM MACHINE
      LEFT JOIN MACHINE_DISKS
      ON
      (MACHINE_DISKS.ID = MACHINE.ID)
      LEFT JOIN USER U ON U.USER_NAME = MACHINE.USER
      where OS_NAME NOT LIKE '%Mac%'
      and OS_NAME not like '%Windows 7%'
      and
      (1 not in
      (
      select 1 from MACHINE_DISKS where MACHINE_DISKS.ID = MACHINE.ID
      and
      if(MACHINE.OS_ARCH like '%x86%',MACHINE_DISKS.DISK_FREE >= '16',MACHINE_DISKS.DISK_FREE >= '20')
      )
      )
      GROUP BY MACHINE.ID
      ) as disk

      UNION ALL

      select *
      from
      (
      SELECT
      MACHINE.ID,
      MACHINE.NAME AS NAME,
      USER_FULLNAME,
      U.BUDGET_CODE AS DEPT,
      OS_NAME,
      OS_ARCH as ARCH,
      'Not enough Memory' as UPGRADABLE,
      PROCESSORS,
      RAM_TOTAL,
      MACHINE_DISKS.DISK_SIZE AS DISK_SIZE,
      DISK_FREE AS MACHINE_DISKS_DISK_FREE,
      VIDEO_CONTROLLERS as VIDEO
      FROM MACHINE
      LEFT JOIN MACHINE_DISKS
      ON
      (MACHINE_DISKS.ID = MACHINE.ID)
      LEFT JOIN USER U ON U.USER_NAME = MACHINE.USER
      where OS_NAME NOT LIKE '%Mac%'
      and OS_NAME not like '%Windows 7%'
      and
      if(MACHINE.OS_ARCH like '%x86%', RAM_TOTAL < '1000',RAM_TOTAL < '2000')
      GROUP BY MACHINE.ID
      ) as ram

      union all

      select *
      from
      (
      SELECT
      MACHINE.ID,
      MACHINE.NAME AS NAME,
      USER_FULLNAME,
      U.BUDGET_CODE AS DEPT,
      OS_NAME,
      OS_ARCH as ARCH,
      'Not enough Processor Speed' as UPGRADABLE,
      PROCESSORS,
      RAM_TOTAL,
      MACHINE_DISKS.DISK_SIZE AS DISK_SIZE,
      MACHINE_DISKS.DISK_FREE AS DISK_FREE,
      VIDEO_CONTROLLERS as VIDEO
      FROM MACHINE
      LEFT JOIN MACHINE_DISKS
      ON
      (MACHINE_DISKS.ID = MACHINE.ID)
      LEFT JOIN USER U ON U.USER_NAME = MACHINE.USER
      Where OS_NAME NOT LIKE '%Mac%'
      and OS_NAME not like '%Windows 7%'
      AND
      PROCESSORS LIKE '%Mhz%'
      GROUP BY MACHINE.ID
      ) as processor

      union all

      select *
      from
      (
      SELECT
      MACHINE.ID,
      MACHINE.NAME AS NAME,
      USER_FULLNAME,
      U.BUDGET_CODE AS DEPT,
      OS_NAME,
      OS_ARCH as ARCH,
      'Not Required (Windows 7 already Installed)' as UPGRADABLE,
      PROCESSORS,
      RAM_TOTAL,
      MACHINE_DISKS.DISK_SIZE AS DISK_SIZE,
      MACHINE_DISKS.DISK_FREE AS DISK_FREE,
      VIDEO_CONTROLLERS as VIDEO
      FROM MACHINE
      LEFT JOIN MACHINE_DISKS
      ON
      (MACHINE_DISKS.ID = MACHINE.ID)
      LEFT JOIN USER U ON U.USER_NAME = MACHINE.USER
      where OS_NAME like '%Windows 7%'
      GROUP BY MACHINE.ID
      ) as Win7
      order by UPGRADABLE, NAME - dugullett 11 years ago
      • Once again, you nailed it! Thanks for your help! - svierneisel 11 years ago
 
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