Server Hardware Report by Manufcturer

[aragones]

Hey how would get report on LS 5.0 like this one

report.aspx?det=Web50findbyvendor&title=Computer%20Manufacturer%20%20Dell+Inc.&@vendor=Dell+Inc.

but that we can add more fields like OS (Linux, Windows, VMware) for example.

I used to be able to find the reports on SQL by the name but with the new 5.0 version i cannot find the "Web50findbyvendor" that I would've on the old 4 version. if I mix assest table , custom and Tsysos it wont give me Linux and windows servers.

Id like to be a able to basically find out how many Dell server's I have physically regardless of the OS they have.

Is there any way to decipher the new reports to match them to SQL views on the DB? that's how I used to create reports on the old version by copying and modyfing the canned reports.

thx!

[aragones · Accepted solution]

yay and I figured out how to merge the OS Columns 🙂

SELECT TOP (1000000)
dbo.tblAssets.AssetName,
dbo.tblAssetCustom.Model,
dbo.tblAssetCustom.Serialnumber,
coalesce(dbo.tsysOS.OSname,dbo.tblVmwareInfo.Version,'Unknown') AS OSVersion,
dbo.tblAssets.AssetID
FROM dbo.tblAssets INNER JOIN
dbo.tblAssetCustom ON dbo.tblAssets.AssetID = dbo.tblAssetCustom.AssetID LEFT OUTER JOIN
dbo.tblVmwareInfo ON dbo.tblAssets.AssetID = dbo.tblVmwareInfo.AssetID LEFT OUTER JOIN
dbo.tsysOS ON dbo.tblAssets.OScode = dbo.tsysOS.OScode
WHERE (dbo.tblAssetCustom.Model LIKE '%poweredge%')
ORDER BY dbo.tblAssetCustom.Model

[aragones]

Okay I kinda sort of figured it out, this work's except my output for OS is on two columns but that's fine for now

SELECT TOP (1000000) dbo.tblAssets.AssetName, dbo.tblAssetCustom.Model, dbo.tblAssetCustom.Serialnumber, dbo.tsysOS.OSname AS [Windows OS],
dbo.tblVmwareInfo.Version AS [ESX OS], dbo.tblAssets.AssetID
FROM dbo.tblAssets INNER JOIN
dbo.tblAssetCustom ON dbo.tblAssets.AssetID = dbo.tblAssetCustom.AssetID LEFT OUTER JOIN
dbo.tblVmwareInfo ON dbo.tblAssets.AssetID = dbo.tblVmwareInfo.AssetID LEFT OUTER JOIN
dbo.tsysOS ON dbo.tblAssets.OScode = dbo.tsysOS.OScode
WHERE (dbo.tblAssetCustom.Model LIKE '%poweredge%')
ORDER BY dbo.tblAssetCustom.Model