Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Need help w/Simple SQL query

Hello all,

Not familiar with SQL / Queries, but I would like to modify the following to incude only nodenames that beging witth XXX, and possible filter to only ethernet interfaces.

declare @prev60 smalldatetime

SET @prev60=dateadd(dd,-60,getdate())

select i.InterfaceName, i.NodeID, i.interfaceid, sa.StatusName as AdminStatus, so.statusname as OperStatus, ss.Statusname as status, Interfacelastchange, n.caption

from

Interfaces i left join StatusInfo sa on i.adminStatus=sa.statusid

left join StatusInfo so on i.OperStatus=so.StatusId

left join StatusInfo ss on i.Status=ss.StatusId

left join Nodes n on i.NodeID=n.NodeID

where (Interfacelastchange < @prev60) and i.Status <> 1 and i.adminstatus <> 4 and i.NodeName like WBX

-- group by n.caption, i.InterfaceName,

order by InterfaceLastChange

Thanks in advance !!

Find more posts tagged with

Accepted answers

The node name is in the n.Caption field. You could change the line to be n.caption AS NodeName if you need node name as the column heading.

All comments

Hi,

Try this:

declare @prev60 smalldatetime

SET @prev60=dateadd(dd,-60,getdate())

select

i.InterfaceName,

i.InterfaceTypeDescription,

i.NodeID,

i.interfaceid,

sa.StatusName as AdminStatus,

so.statusname as OperStatus,

ss.Statusname as status,

Interfacelastchange,

n.caption

from

Interfaces i left join StatusInfo sa on i.adminStatus=sa.statusid

left join StatusInfo so on i.OperStatus=so.StatusId

left join StatusInfo ss on i.Status=ss.StatusId

left join Nodes n on i.NodeID=n.NodeID

where (Interfacelastchange < @prev60) and i.Status <> 1 and i.adminstatus <> 4 and n.Caption like 'xxx%' and i.InterfaceTypeDescription = 'Ethernet'

-- group by n.caption, i.InterfaceName,

order by InterfaceLastChange

Hope this helps

Stuart

Hello Stuart..

Thankyou so much !!!

Looks good.. but I’m trying to report by nodename.. tried to change nodeid to nodename, but have something wrong..

The node name is in the n.Caption field. You could change the line to be n.caption AS NodeName if you need node name as the column heading.

Don't forget to mark 's answer as the correct answer for this thread if he's hit the mark!

Stuart !

Thank you . That's Great,,, Can I ask one more q ? I want to expand my search from just XXX to wither XXX or YYY, I've attempted it, and although I don't get any error messages, I only show the XXX devices..

This is the line I adjusted.

where (Interfacelastchange < @prev60) and i.Status <> 1 and i.adminstatus <> 4 and (n.Caption like '%WBX%' or n.Caption like '%NYX%')

Should I open a separate query for this ?

Quick Links