SQL query on R_Inventory view to omit deleted devices

[Tom Maher]

I'm having a bit of an issue running a standard inventory query that does not return "deleted" devices - I have read on here in the past that while devices that are deleted from the database via the system table view page diappear from the table view they still remain as physical records in the devices table.

I've tried using the "Show SQL" functionality in the Reports section but when running the copied TSQL -

select R_Inventory.DeviceName, R_Inventory.IPAddress, R_Inventory.DeviceStatus,
R_Inventory.ProductType, R_Inventory.ProductSubType, R_Inventory.ProductId, R_Inventory.ProductName,
R_Inventory.SerialNumber, R_Inventory.AssetTag, R_Inventory.MemorySize, R_Inventory.ROMVersion,
R_Inventory.OSName, R_Inventory.OSVendor, R_Inventory.ServerRole, R_Inventory.Location, R_Inventory.DeviceOwner,
R_Inventory.DeviceBootTime, R_Inventory.IPXAddress
from R_Inventory
WHERE ((R_Inventory.SnapShotID=-1) OR (R_Inventory.SnapShotID is NULL))
AND DeviceKey in
(select devices.deviceKey
from devices JOIN notices ON devices.deviceKey = notices.deviceKey and notices.NoticeType = 1
LEFT OUTER JOIN DB_deviceInfoEx ON devices.deviceKey = DB_deviceInfoEx.deviceKey
LEFT OUTER JOIN IPAddress ON devices.deviceKey = IPAddress.deviceKey and IPAddress.ipIndex = 0
LEFT OUTER JOIN IPXAddress ON devices.deviceKey = IPXAddress.deviceKey and IPXAddress.ipxIndex = 0
LEFT OUTER JOIN associated_device_data ON devices.deviceKey = associated_device_data.deviceKey
LEFT OUTER JOIN deviceProtocolInfo ON devices.deviceKey = deviceProtocolInfo.deviceKey
LEFT OUTER JOIN LockdownStatus ON devices.deviceKey = LockdownStatus.deviceKey
LEFT OUTER JOIN vmmStatus ON devices.deviceKey = vmmStatus.deviceKey
LEFT OUTER JOIN DeviceStatusValue ON devices.deviceKey = DeviceStatusValue.deviceKey and DeviceStatusValue.StatusSourceId= 6
JOIN consolidatedNodeAuths ON devices.mxGuid = consolidatedNodeAuths.NodeId and consolidatedNodeAuths.userID = '00000000864162de0000000400000013'
WHERE ( ( devices.productType = 1) ) ) order by R_Inventory.DeviceName ASC

Nothing is returned. By commenting out the

JOIN consolidatedNodeAuths ON devices.mxGuid = consolidatedNodeAuths.NodeId and consolidatedNodeAuths.userID = '00000000864162de0000000400000013'

statement, the query runs fine and returns all devices however, it still returns devices I know to have been deleted.

Can anyone advise?

[Derek_56]

Tom,

You may want to start by paring down your SQL statement if you don't need all that information. Here's what I use on our HP SIM 5.1 SP2 (+hotfixes) server:
SELECT R_Inventory.DeviceName, R_Inventory.SerialNumber, R_Inventory.AssetTag, R_Inventory.ProductName, R_Inventory.MemorySize, R_Inventory.IPAddress
FROM R_Inventory
WHERE R_Inventory.ProductType='Server' order by R_Inventory.DeviceName ASC

This gets me all the servers that I want and none of them are known deletions. If you just remove the WHERE clause, you'ld get all (presumably non-deleted) nodes in SIM. I used to have an issue on SIM 4 where my deletes would hang around until we had a SQL restart or server reboot. There may be some database issues going on in your system.