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;

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

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
Answered 07/02/2013 by: dugullett
Red Belt

  • 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>'
  • We do not have seperate labels for Departments at this time, but I like this idea
    • Actually, the above will work for my other offices. We do use labels for each facility. Thank You for this as well!
  • 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
    • 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
      • Once again, you nailed it! Thanks for your help!
Please log in to comment
Answer this question or Comment on this question for clarity

Share