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;
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
dugullett
10 years ago
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 10 years ago -
We do not have seperate labels for Departments at this time, but I like this idea - svierneisel 10 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 10 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 10 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 10 years ago-
Once again, you nailed it! Thanks for your help! - svierneisel 10 years ago
