/bundles/itninjaweb/img/Breadcrumb_cap_w.png
All of our PC's are have a location assigned and are labeled accordingly.  I'm trying to generate a report with just the warranty expiration date (without the silly Dell Digital Delivery option), but I can't get it by location assigned.  I'd like to break it out to just the location assigned so I can provide it to department heads.  Here's what I've got so far:

SELECT DISTINCT(MACHINE.NAME), 
MACHINE.OS_NAME, 
MACHINE.CS_MODEL, 
MACHINE.CS_MANUFACTURER, 
DA.SHIP_DATE AS "Ship Date",
DATE(DW.END_DATE) AS "Warranty End Date",
DW.SERVICE_LEVEL_DESCRIPTION as "Service Level",DATE(MACHINE.LAST_SYNC)
FROM MACHINE
LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG
LEFT JOIN DELL_WARRANTY DW on MACHINE.BIOS_SERIAL_NUMBER = DW.SERVICE_TAG
WHERE MACHINE.CS_MANUFACTURER like 'Dell%'
and DW.SERVICE_LEVEL_CODE not like 'D' 
and DW.SERVICE_LEVEL_CODE not like 'DL'
GROUP BY MACHINE.NAME
ORDER BY "Ship Date", MACHINE.NAME
0 Comments   [ + ] Show comments

Comments

Please log in to comment


Answers

1
Add this to the query:
L.NAME

and this to the Join section:
JOIN ASSET A ON A.MAPPED_ID = MACHINE.ID
JOIN ASSET L ON A.LOCATION_ID = L.ID

This maps the Device (MACHINE.ID) to the Asset it's attached to (A.Mapped_ID), then maps Location Assets (L.ID) to the Location ID of all Assets (A.LOCATION_ID).  
Then when we query L.NAME, we get the name of the Location Asset to which the Device Asset is assigned.

That's a nice report.  I think I'll use it, too!
Answered 08/08/2018 by: ondrar
Second Degree Blue Belt

  • Forgive my ignorance, I have zero experience with SQL. Where does the L.NAME go, and where do I add the Join section? Ideally I'd like to be able to just input the location name to generate a report that's isolated just to those PC's. Thank you for your help!
    • No problem. I started there, too. It'll look like this:

      SELECT DISTINCT(MACHINE.NAME),
      MACHINE.OS_NAME,
      MACHINE.CS_MODEL,
      MACHINE.CS_MANUFACTURER,
      DA.SHIP_DATE AS 'Ship Date',
      DATE(DW.END_DATE) AS 'Warranty End Date',
      DW.SERVICE_LEVEL_DESCRIPTION as 'Service Level',
      DATE(MACHINE.LAST_SYNC),
      L.NAME

      FROM MACHINE

      LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG
      LEFT JOIN DELL_WARRANTY DW on MACHINE.BIOS_SERIAL_NUMBER = DW.SERVICE_TAG

      JOIN ASSET A ON A.MAPPED_ID = MACHINE.ID
      JOIN ASSET L ON A.LOCATION_ID = L.ID


      WHERE MACHINE.CS_MANUFACTURER like 'Dell%'
      and DW.SERVICE_LEVEL_CODE not like 'D'
      and DW.SERVICE_LEVEL_CODE not like 'DL'

      GROUP BY MACHINE.NAME

      ORDER BY "Ship Date", MACHINE.NAME
      • And if you want a specific location, put this under WHERE

        AND L.NAME = '<insert location name here>'
  • When joining to the ASSET table I have found that it is best to specify that the asset is type 5, otherwise you risk getting an asset that happens to have the same ID as the machine.

    JOIN ASSET A on A.MAPPED_ID = MACHINE.ID and A.ASSET_TYPE_ID = 5

    It doesn't happen often, but it is possible.
Please log in to comment
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share