Historical Traceroutes report for Summary View

Hi guys,

I would really like to get a report on the IP SLA Summary page that displays ALL Historical Traceroutes for today, sorted by highest Latency.

Been stuck in Report writer for the past couple of days trying to get this sort of report but can't manage to pick and match the right fields required to make this work.

Could anyone please help with this? I'm after the following type of report, ICMP Path Jitter based, for ALL Operations monitored.

Find more posts tagged with

traceroutes

historical

report

Accepted answers

Radim.Keseg

to limit the resultset by ICMP Path Jitter, there needs to be a condition VoipOperationTypeID = 14

SELECT vphor.*

FROM VoipPathHopOperationResults vphor

JOIN VoipOperationInstances voi ON voi.VoipOperationInstanceID = vphor.VoipOperationInstanceID

WHERE RecordTime > DATEADD(hh, -24, GETUTCDATE()) AND voi.VoipOperationTypeID = 14

ORDER BY vphor.RecordTime DESC, vphor.VoipOperationInstanceID ASC, vphor.VoipPathID ASC, vphor.HopIndex ASC

All comments

michal.hrncirik

Hi Deltona,

we are working on custom report that will do what you need.

Radim.Keseg

Have you tried Advanced SQL in report writer. I would start with this SQL for instance, you can then play with more details, the only thing needed is to limit it on is the operations instance and time range.

SELECT *

FROM VoipPathHopOperationResults

ORDER BY RecordTime DESC, VoipOperationInstanceID ASC, VoipPathID ASC, HopIndex ASC

if you need more details, please come back.

Radim,

Deltona

Hi Radim,

Thanks for the query, although it doesn't seem to ever complete

IP SLA SQL Report.png

Radim.Keseg

I see the query was just example, as mentioned it is necessary to limit it for instance:

SELECT *

FROM VoipPathHopOperationResults

WHERE RecordTime > DATEADD(hh, -24, GETUTCDATE())

ORDER BY RecordTime DESC, VoipOperationInstanceID ASC, VoipPathID ASC, HopIndex ASC

this query shows records for last 24 hours.

Deltona

Sorry, not well versed in the custom SQL query field...

The latest query works great, I just need to limit it to display ICMP Path Jitter.

How do I call from VoipOperationTypes?