SQL Report: Combine Machine, Asset and DELL Service Data
Hi there,
I am trying to run a custom report in which I combine Machine Data like Hostname and Service Tag, Dell Service Data like if the Support is still running and Asset data I can customize. I am no SQL specialist - I tried to come up with a solution in FlySpeed SQL Query but don't know where to get the Asset information from. I found some views in our DB which provide the information I need. But when I insert them into my query it does not show any data.
Who could help?`
This is my query:
Select
frm.NAME As Firmenname,
mach.NAME As Computer,
mach.CS_MODEL As Modell,
mach.BIOS_SERIAL_NUMBER As Servicetag,
xtern.DESCR As Servicelevel,
xtern.ENDD As Enddatum,
Case When xtern.ENDD > Now() Then 'Ja' Else 'Nein' End As Service,
Case When xtern.ENDD < Now() Then 0 Else DateDiff(xtern.ENDD, Now())
End As Tage,
mach.CS_MANUFACTURER As Hersteller,
lctn.NAME As Standort,
mach.USER_FULLNAME As Benutzer,
cstctr.NAME As KST
From
ORG1.MACHINE mach Left Join
ORG1.ASSET ast
On ast.MAPPED_ID = mach.ID,
ORG1.ASSET_DATA_VIEW__Firma frm,
ORG1.ASSET_DATA_VIEW_Location lctn,
ORG1.ASSET_DATA_VIEW_Cost_Center cstctr,
(Select
dellw.SERVICE_TAG As ST,
Max(dellw.END_DATE) As ENDD,
dellw.SERVICE_LEVEL_DESCRIPTION As DESCR
From
ORG1.DELL_WARRANTY dellw
Where
dellw.SERVICE_LEVEL_GROUP = '5'
Group By
dellw.SERVICE_TAG) As xtern
Where
xtern.ST = mach.BIOS_SERIAL_NUMBER And
ast.ASSET_TYPE_ID = 5
Order By
Enddatum Desc
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
7 years ago
Here is a query that I use which should be instructive. Explanations below:
SELECT DISTINCT(MACHINE.NAME),
MACHINE.OS_NAME,
MACHINE.CS_MODEL,
MACHINE.CS_MANUFACTURER,
DA.SHIP_DATE AS "Ship Date",
MAX(DW.END_DATE) AS "Warranty End Date",
DW.SERVICE_LEVEL_DESCRIPTION as "Service Level",
ASSETDATA.FIELD_32 as "PO Number"
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
LEFT JOIN ASSET on ASSET.NAME = MACHINE.BIOS_SERIAL_NUMBER
LEFT JOIN ASSET_DATA_5 ASSETDATA on ASSETDATA.ID = ASSET.ASSET_DATA_ID
WHERE MACHINE.CS_MANUFACTURER like 'Dell%'
GROUP BY MACHINE.NAME
ORDER BY "Ship Date", MACHINE.NAME
These explanations are out of order but it makes things easier to understand. I'll start with the join statements:
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
LEFT JOIN ASSET on ASSET.NAME = MACHINE.BIOS_SERIAL_NUMBER
LEFT JOIN ASSET_DATA_5 ASSETDATA on ASSETDATA.ID = ASSET.ASSET_DATA_ID
These setup the associations between the MACHINE table and the tables that hold the warranty and Dell asset information.
The first one links us to the DELL_ASSET table (aliased as DA) via the service tag:
LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG
The next join links the DELL_WARRANTY table (aliased as DW), also via the service tag:
LEFT JOIN DELL_WARRANTY DW on MACHINE.BIOS_SERIAL_NUMBER = DW.SERVICE_TAG
Those two joins will allow us to query the ship date and warranty information respectively. Next we will link to the ASSET table which holds information about all assets in the database:
LEFT JOIN ASSET on ASSET.NAME = MACHINE.BIOS_SERIAL_NUMBER
In my environment the serial number of computers in the inventory is matched to the name of the asset. To verify if this is true in your environment edit the Computer asset type (Assets, Asset Types, Computer) and check the value for Mapped Inventory Field. If if isn't BIOS Serial Number then your join will need to be different.
The asset table itself does not contain much information regarding the actual assets. Each asset type has its own ASSET_DATA table. Computers have an ASSET_TYPE_ID of 5 so we need to make an association to the ASSET_DATA_5 table (aliased as ASSETDATA) via the ASSET_DATA_ID:
LEFT JOIN ASSET_DATA_5 ASSETDATA on ASSETDATA.ID = ASSET.ASSET_DATA_ID
Now that we have our joins in place the fields we are selecting make sense. You will need to get the proper column names for the data you want from the asset data table and you can do this by browsing the ASSET_DATA_5 table. In my case FIELD_32 contains the PO Number for the asset so I selected that field and then called it "PO Number" so it looks nicer in the output.
Comments:
-
I think I got most of it - what I did not find out yet is where I can find additional Asset Data? ASSET_DATA_5 is for Computers - I for example have additional info like Company Name, Location and so on. I tried with all Tables but can not find those infos. I found them in a View, but can not Left Join... - e.meier 7 years ago
-
Each asset type will have it's own ASSET_DATA_X table where X is the ID of the asset type. If you need data from another table in your report then you need to relate the data from the machine table to the table that contains the data you want. So, what asset type contains the Company Name data is the first question you need to answer, then what is that asset type's ID? Then what is the relationship between this asset type and the machine? - chucksteel 7 years ago
-
So once I find out in what ASSET_DATA_X table I can find my data I have to link it to the machine table (not the asset table?), right? I'll try that tomorrow. Thanks for your help. :) - e.meier 7 years ago
-
Depending on how the data is related you may need to go through the asset table first, like I did to get from serial number to asset id to asset data id (MACHINE.BIOS_SERIAL_NUMBER = ASSET.NAME and ASSET.ASSET_DATA_ID = ASSET_DATA_5.ID) - chucksteel 7 years ago
-
I was able to get to work. Sorry for the late reply. - jgarcia29 7 years ago
-
Yes it should work. To test I just created a new report (SQL) and pasted the above code into the report and ran it. If you don't have a FIELD_32 in your ASSET_DATA_5 table then it probably won't work for you, however. Can you paste your query? - chucksteel 7 years ago
