Grafana Pie charts

Hello.

Is there a way to pull interfaces data from Solarwinds to create pie charts or utilization charts in grafana?

I was able to pull cpu data but that all not for one but for all nodes'

using

SELECT downsample(ObservationTimeStamp) as time, a.Node.Caption, AVG(AvgLoad) as CpuLoad, AVG(AvgMemoryUsed) as MemoryUsed FROM Orion.CPULoad a WHERE ObservationTimeStamp BETWEEN $from AND $to GROUP BY downsample(ObservationTimeStamp), a.Node.Caption, a.NodeID ORDER BY time DESC

Find more posts tagged with

grafana

interfaces

Accepted answers

dan_jagnow

You'll find some example SWQL queries for interfaces here:

SWQL CUSTOM QUERY TO SHOW INTERFACE PERCENT USAGE LIKE TOP 10 CHARTS with Search option

There is information about interfaces in the SWIS schema here:

Orion.NPM.Interfaces | Orion SDK Schemas

All comments

dan_jagnow

You'll find some example SWQL queries for interfaces here:

SWQL CUSTOM QUERY TO SHOW INTERFACE PERCENT USAGE LIKE TOP 10 CHARTS with Search option

There is information about interfaces in the SWIS schema here:

Orion.NPM.Interfaces | Orion SDK Schemas

mrkuwait

No, This did not help

mesverrum

I think the point that was being made was that if you already have it set up to the point where you are getting the CPU data then all you need to do to get interface data is to figure out the correct query for interfaces. For interface data most of what you want would probably be under orion.npm.interfaces and orion.npm.interfacetraffic.

dan_jagnow

Perhaps a query like this?

SELECT    n.NodeName,    n.DetailsUrl AS NodeDetailsUrl,    i.InterfaceName,    i.DetailsUrl AS InterfaceDetailsUrl,    i.InPercentUtil,    i.OutPercentUtilFROM Orion.NPM.Interfaces AS iLEFT JOIN Orion.Nodes AS n ON n.NodeID = i.NodeIDORDER BY i.PercentUtil DESC

If not, perhaps you could provide some additional detail about exactly what interface data you would like to gather.

bmallon

I do the same type of thing for a variety of data. I run a bunch of python scripts that go out and poll the SolarWinds SQL DB on some frequency to grab the data I want to graph.

So for interface use, I'm typically looking for how much bandwidth we are using inbound and outbound. We have a number of carriers, so I poll various routers looking for specific interfaces. I take the data and put it in Graphite and then Grafana simply graphs the data from there.

Here's a rudimentary sample of a SQL query I use. I'm telling it to look for the name of some specific router, then telling it I want interfaces that have a name like 'TenGig', and to ignore port channels.

SELECT inbps FROM [dbo].[Interfaces] WHERE FullName LIKE '%SOME PORTION OF YOUR ROUTER NAME HERE%' AND InterfaceName LIKE '%TenGig%' AND Caption NOT LIKE '%Port%

SELECT outbps FROM [dbo].[Interfaces] WHERE FullName LIKE '%SOME PORTION OF YOUR ROUTER NAME HERE%' AND InterfaceName LIKE '%TenGig%' AND Caption NOT LIKE '%Port%

Now, you can go much deeper with this using far more complex SQL queries, but I didn't want to make things too complex for this example here. Depending on your naming scheme in your environment, you will need to customize a query based device names and descriptions you have put on your interfaces. I recommend you put descriptions on the interfaces you want to monitor like this to make it as simple as possible.

If you have some specific questions about how to do this, I'll be happy to help you with some more advanced queries.

mrkuwait

Ok, this is very good. I can collect data. now how to you SUM 2 interfaces together? thanks

wes.farrow

What could you change in the query if you wanted to look at specific devices or a custom group of devices?

dan_jagnow

You can add a WHERE clause that filters the results by the node name, ID, or some other property of your choice. An example that looks for two specific node names follows:

SELECT  
    n.NodeName,  
    n.DetailsUrl AS NodeDetailsUrl,  
    i.InterfaceName,  
    i.DetailsUrl AS InterfaceDetailsUrl,  
    i.InPercentUtil,  
    i.OutPercentUtil  
FROM Orion.NPM.Interfaces AS i  
INNER JOIN Orion.Nodes AS n ON n.NodeID = i.NodeID  
WHERE n.NodeName IN ('myserver.mydomain.local', 'someotherbox')
ORDER BY i.PercentUtil DESC

You could also use the LIKE operator to do wildcard-based pattern matching on the node name. You can also filter based on properties of the interface in addition to using properties of the node.

If you'd like to filter nodes by group, see this thread. You should be able to do something like this (untested), where you substitute the name of the group that you would like to filter on:

SELECT  
    c.Name AS NodeGroup,
    n.NodeName,  
    n.DetailsUrl AS NodeDetailsUrl,
    i.InterfaceName,  
    i.DetailsUrl AS InterfaceDetailsUrl,  
    i.InPercentUtil,  
    i.OutPercentUtil
FROM Orion.NPM.Interfaces AS i  
INNER JOIN Orion.Nodes AS n ON n.NodeID = i.NodeID  
INNER JOIN Orion.ContainerMemberSnapshots AS cms ON n.NodeID = cms.EntityID AND cms.EntityType='Orion.Nodes'
INNER JOIN Orion.Container AS c ON c.ContainerID = cms.ContainerID
WHERE c.Name = 'MyNodeGroup'
ORDER BY i.PercentUtil DESC