iDrac Audit Report

[RandomITDude232]

Good Morning All,

I was hoping someone on here could help me out with a report I am needing to have generated. We are a Dell shop and I have been tasked with getting a list of the versions of iDrac running on all servers. This is due to a recent exploit in iDrac that needs to be patched. I have tried to create a report, but I am not very good an SQL and am slowly learning it. The report needs to include Asset name, model, Serial Number, and iDrac Version.

Here is what i started with:

Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tsysassettypes.AssetTypename,
tsysassettypes.AssetTypeIcon10 As icon,
tblassets.IPAddress,
tsysassettypes.AssetType,
tblassets.Lastseen,
tblassets.Lasttried,
tblOIDTarget.Manufacturer,
tblOIDKey.TargetID,
tblOIDKey.OIDKeyID,
tblAssetGroups.AssetGroupID,
tblAssetGroups.AssetGroup,
tblOIDTarget.AssetGroupID As AssetGroupID1,
tblOIDTarget.Model
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Inner Join tblOIDTarget On tblassets.AssetID = tblOIDTarget.AssetID And
tsysassettypes.AssetType = tblOIDTarget.AssetType
Inner Join tblOIDKey On tblOIDTarget.TargetID = tblOIDKey.TargetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblOIDTarget.AssetGroupID
Inner Join tblOIDTargetType On tblOIDTarget.TargetTypeID =
tblOIDTargetType.TargetTypeID
Where tblOIDTarget.Manufacturer = 'DELL' And tblassetcustom.State = 1

But it comes up with zero results.

[Mister_Nobody]

@RandomITDude232 Could you accept Solution?

[RandomITDude232]

This was amazing and helped me get it going. Just a question if I wanted to add another column that took specific OID's and populated that column with that data what would I need to add into the report. For instance, if I wanted a column to be the idrac OID firmware version, what would I need to add to the code to get that added in!

[Mister_Nobody]

See my reports here

https://community.lansweeper.com/t5/general-discussions/sql-split-column-values-into-separate-column...

[Mister_Nobody]

Query example

Select Top (1000000) tblAssets.AssetID,
  tsysAssetTypes.AssetTypeIcon10 As Icon,
  tblAssets.AssetName,
  tblOIDData.Label As [OID Label],
  tblOIDData.[Key] As OID,
  tblOIDData.Data As [OID Data],
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tblAssets.Firstseen As [Created at],
  tblAssets.Lastseen As [Last successful scan]
From tblAssets
  Inner Join tblOIDData On tblAssets.AssetID = tblOIDData.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
Where tblAssetCustom.Manufacturer Like '%dell%'

[Mister_Nobody]

Hardware serial number
system.hw.serialnumber
1.3.6.1.4.1.674.10892.5.1.3.2.0

Model is same as server name
Hardware model name
system.hw.model
1.3.6.1.4.1.674.10892.5.1.3.12.0

[Mister_Nobody]

You have to add OIDs to collect fimware version
1.3.6.1.4.1.674.10892.5.1.1.8.0