Cleaning up is always a bit of a chore. Luckily this script makes a little easier by pointing to objects which are "unknown".
The script will list up, down, disabled, unknown, and total for three objects - Interfaces, volumes, and hardware. I'm sure it can be expanded to other objects as well.
To get just the unknowns, comment out the three lines and uncomment the fourth.
select
n.Caption ,n.vendor
--,isnull(i.up,0) as [Int-Up], isnull(i.Down,0) as [Int-Down], isnull(i.dis,0) as [Int-Dis], isnull(i.Unk,0) as [Int-Unk], isnull(i.total,0) as [Int-Total]
--,isnull(vol.up,0) as [Vol-Up], isnull(vol.Down,0) as [Vol-Down], isnull(vol.dis,0) as [Vil-Dis], isnull(vol.Unk,0) as [Vol-Unk], isnull(vol.total,0) as [Vol-Total]
--,isnull(hi.up,0) as [Hard-Up], isnull(hi.down,0) as [Hard-Down], isnull(hi.Dis,0) as [Hard-Dis], isnull(hi.unk,0) as [Hard-Unk], isnull(hi.total,0) as [Hard-Total]
,isnull(i.unk,0) as [Int-Unk], isnull(vol.unk,0) as [Vol-Unk], isnull(hi.unk,0) as [Hard-Unk]
from orion.nodes n
left join (select v.nodeid
,sum(case when v.status=1 and v.VolumeType like 'Fix%' then 1 else 0 end) as [Up]
,sum(case when v.status=0 and v.VolumeType like 'Fix%'then 1 else 0 end) as [Unk]
,sum(case when v.status=2 and v.VolumeType like 'Fix%'then 1 else 0 end) as [Down]
,sum(case when v.status=27 and v.VolumeType like 'Fix%'then 1 else 0 end) as [Dis]
,sum(case when v.VolumeType like 'Fix%' then 1 end) as [Total]
from orion.Volumes v
group by v.nodeid ) vol on vol.nodeid=n.nodeid
left join (SELECT hi.NodeID
,sum(case when hi.statusdescription ='up' then 1 else 0 end) as [Up]
,sum(case when hi.statusdescription ='down' then 1 else 0 end) as [Down]
,sum(case when hi.statusdescription ='unknown' then 1 else 0 end) as [Unk]
,sum(case when hi.statusdescription ='disabled' then 1 else 0 end) as [Dis]
,count(*) as [Total]
FROM Orion.HardwareHealth.HardwareItem hi
group by hi.nodeid) hi on hi.nodeid=n.nodeid
left join (select i.nodeid
,sum(case when i.status=1 then 1 else 0 end) as [Up]
,sum(case when i.status=2 then 1 else 0 end) as [Down]
,sum(case when i.status=0 then 1 else 0 end) as [Unk]
,sum(case when i.status=27 then 1 else 0 end) as [Dis]
,count(*) as [Total]
FROM Orion.NPM.Interfaces i
group by i.nodeid) i on i.nodeid=n.nodeid
where n.status=1 and n.ObjectSubType not like 'ICMP'
order by isnull(i.unk,0)+isnull(vol.unk,0)+isnull(hi.unk,0) desc
Thanks
Amit
