Server Management - Systems Insight Manager
cancel
Showing results for 
Search instead for 
Did you mean: 

SQL query on R_Inventory view to omit deleted devices

 
Tom Maher
Occasional Advisor

SQL query on R_Inventory view to omit deleted devices

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?