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

Graph percentage but need to join tables with different table DateTime rows?

I would like create a graph that shows a percentage of dropped packets but I'm having trouble figuring out how I would join the custom poller poll time per row to the Orion.NPM.InterfaceTraffic table DateTime for InTotalPkts. My thought was I could do (CST.RawStatus / IT.InTotalPkts) * 100 and get a percentage. CST.RawStatus being a custom poller for Juniper policer packets being dropped and dividing that by Total Rx packets for that interface.

I'm not sure how I can get those values to be joined so the calculation is done for the values taken during the same time frame though.

For example below the custom poller DateTime is 2022-06-02 13:23:25.647

And here in the InterfaceTraffic table the closest poll time is 2022-06-02 13:21:25.8475035

Any idea how to do this? Right now I'm just graphing the dropped packets but it would nice to visualize what the actual impact is with a comparison to total packets.

Find more posts tagged with

SWQL

Accepted answers

KMSigma.SWI

I'm not sure if a percentage would be as valuable for discarded packets. Before you cut me off at the knees, let me make a case.

If it was a slow line and only sent 2 packets, but one was dropped, then the the percentage would be 50% - very bad percentage, but not technically a bad metric.
If the line was busy and you sent 20,000 packets, and dropped 5,000, then it would be 25% - less bad as a percentage, but more bad as an actual symptom.

What you are asking for can be done, and I'd probably fall back on the DOWNSAMPLE function as defined on the SDK Wiki. Once you have each resultant "table" with matching date/time blocks, then you can do your division to determine the percentage.

Personally, I'd rather just graph total packets and dropped on the same graph and look at it that way, but that's just my 2 cents.

All comments

KMSigma.SWI

Is there a specific reason that a PerfStack chart won't work for this?

jajao555

I'm not sure I can I haven't figured out how to use PerfStack for a custom poller and also make it display stats for an individual Interface Details page. In the example query I just used an InterfaceID to test but I would want the graph to show up on any interface details page and show results for that page. So I would replace the Interface ID with the ${InterfaceID} variable.

This jnxPolicerPacketCount is the custom poller I'm trying to compare to InTotalPkts if that was sort of buried in the query and I wasn't clear.

So this is the actual graph we use now but I'd want to have it as a percent or add a line to show percent. I'm just not sure how to get the Rx packet counts to do the calculation in the same time frames.

KMSigma.SWI

I'm not sure if a percentage would be as valuable for discarded packets. Before you cut me off at the knees, let me make a case.

If it was a slow line and only sent 2 packets, but one was dropped, then the the percentage would be 50% - very bad percentage, but not technically a bad metric.
If the line was busy and you sent 20,000 packets, and dropped 5,000, then it would be 25% - less bad as a percentage, but more bad as an actual symptom.

Personally, I'd rather just graph total packets and dropped on the same graph and look at it that way, but that's just my 2 cents.

jajao555

Thanks @KMSigma.SWI that helped. I see your point but having a percentage would allow you look at for example all interfaces with more then 1% traffic discarded or whatever your policy is. This is usually only an issue when we have customers that don't setup shaping on the other side.

I did end up seeing weird calculations using the InTotalPkts stats I think the poll intervals were different and throwing off how I was trying to join them.

I ended up just creating a new poller for Rx packets and it looks like those will always be polled at the same time from what I've tested. I then used DOWNSAMPLE and that seems to be pretty accurate now. How the Juniper MIB for policers works though made it really awkward trying to tie the policer to an interface.

SELECT
N.Caption,
N.IP,
N.NodeID,
CPT.RowLabel,
I.Index,
DOWNSAMPLE(CST.DateTime, '00:10:00') AS Time,
SUM(CST.RawStatus) AS Policed_Discards,
SUM(CST1.RawStatus) AS Rx_Pkts_Total,
ROUND(SUM(CST.RawStatus)/SUM(CST1.RawStatus) *100,2) AS Rx_Percent_Discarded
FROM Orion.NPM.CustomPollerStatistics CST

JOIN Orion.NPM.CustomPollerStatusOnNodeTabular CPT
    ON CST.CustomPollerAssignmentID = CPT.CustomPollerAssignmentID AND CST.RowID = CPT.RowID

JOIN Orion.Nodes N
    ON CST.CustomPollerAssignment.NodeID = N.NodeID

JOIN Orion.NPM.Interfaces I
    ON CPT.NodeID = I.NodeID AND CASE WHEN CPT.RowLabel LIKE '%-inet-i' THEN SubString(CPT.RowLabel,(CharIndex('r-',CPT.RowLabel))+2,((CharIndex('-i',CPT.RowLabel))-((CharIndex('i',CPT.RowLabel))+5))) 
     WHEN CPT.RowLabel LIKE '%-ccc-i' THEN SubString(CPT.RowLabel,(CharIndex('r-',CPT.RowLabel))+2,((CharIndex('-i',CPT.RowLabel))-((CharIndex('c',CPT.RowLabel))+8))) 
    END = I.Name

JOIN Orion.NPM.CustomPollerStatistics CST1
    ON I.Index = CST1.RowID AND CST1.CustomPollerAssignment.NodeID = I.NodeID AND CST1.CustomPollerAssignment.CustomPollerName = 'ifInUcastPkts' AND DOWNSAMPLE(CST1.DateTime, '00:10:00') = DOWNSAMPLE(CST.DateTime, '00:10:00')

WHERE CST.CustomPollerAssignment.CustomPollerName = 'jnxPolicerPacketCount' AND CPT.RowLabel LIKE '%-Policer-%' AND I.InterfaceID = ${InterfaceID} AND CST.DateTime >= AddDAY( -1, GETDATE())
GROUP BY CST.CustomPollerAssignmentID, CST.CustomPollerAssignment.NodeID, CPT.RowLabel, N.Caption, N.IP, I.Index, CST.DateTime, N.NodeID

I tried your suggestion with just having the total and discard but found you couldn't really tell where the discards were unless you hovered over the graph so I ended up trying the separate poller and got the percent calculation working.

I am having an issue getting a full report like below but for all interfaces. Ideally I'd want last 24 hours stats SUM for Discards, Rx packets, and the percent but it seems like the query takes too long or I'm not doing the best logic for that? It only works if I specify a single interface. It won't even do a node if I back the time down to last hour. Any suggestions for that?