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

how to show three months availability history in modern dashboard for all the interfaces or any custom property ?

hello,

need your help to add last 3 months average availability to modern dashboard. i tried different SWQL queries but its not giving desired output, can anybody support me in writing query ? thank you.

custom property : "Link_PING"

@all @superfly @jpaluch

Find more posts tagged with

Accepted answers

All comments

amer10286

@superfly @jpaluch @KMSigma.SWI

please support

amer10286

i need it in this format.

stuartd

Can you share your current query.?

It may just need a simple tweak rather than starting from scratch again.

amer10286

@stuartd this is what i have, but its not working.

custom property i have is "test".

SELECT
it.InterfaceID,
it.InterfaceName,
n.Caption AS NodeName,
icp.Test AS CustomPropertyTest,
CASE
WHEN it.Status = 1 THEN 'Up'
WHEN it.Status = 2 THEN 'Down'
ELSE 'Unknown'
END AS AvailabilityStatus,
it.LastSync AS LastUpdated
FROM
InterfaceTraffic it
JOIN
Nodes n ON it.NodeID = n.NodeID
JOIN
InterfaceCustomProperties icp ON it.InterfaceID = icp.InterfaceID
WHERE
icp.Test = 'test' -- Replace 'test' with the desired value of your custom property
AND it.LastSync >= DATEADD(MONTH, -3, GETDATE())
ORDER BY
n.Caption, it.InterfaceName

jasonclair

Hey, I have a similar requirement, were you ever able to get a working SWQL query?

amer10286

@jasonclair not yet. nobody answeredthis query. if you ever get it, please share it with me. thanks in advance.

stuartd

Hey apologies for no reply but I didn't get a notification.

That said, your use of CASE is beyond my skill set for SQL or SWQL so all I can do is leave it and hope its right

Looking at our InterfaceTable in DB Manager shows that there is no entry called InterfaceName but that entry does exist in the Interfaces table. I have taken your query, reduced it to a bare minimum and it work.

Here's the working query (with the CP commented out): BTW, this is SQL

SELECT 
  it.InterfaceID
  ,it.InterfaceName
  ,n.Caption AS NodeName
  ,it.LastSync AS LastUpdated
  ,CASE
       WHEN it.Status = 1 THEN 'Up'
       WHEN it.Status = 2 THEN 'Down'
       ELSE 'Unknown'
   END AS AvailabilityStatus
FROM InterfaceS AS it
JOIN Nodes n ON it.NodeID = n.NodeID
--JOIN    InterfaceCustomProperties icp ON it.InterfaceID = icp.InterfaceID
WHERE it.LastSync >= DATEADD(MONTH, -3, GETDATE())
ORDER BY n.Caption, it.InterfaceName

And a screenshot of the first few lines in DB Manager.

N.B. I can't see a table in our DB called InterfaceCustomProperties so you would need to double check that on your side. I do see that the CPs for interfaces are directly in the Interfaces table - maybe yours are as well? If so, then no JOIN to that table would be needed.

In which case, an edited version of your query and pulling in one of ours, would be:

SELECT 
  it.InterfaceID
  ,it.InterfaceName
  ,n.Caption AS NodeName
  ,it.LastSync AS LastUpdated
  ,it.Interface_Priority
  ,CASE
       WHEN it.Status = 1 THEN 'Up'
       WHEN it.Status = 2 THEN 'Down'
       ELSE 'Unknown'
   END AS AvailabilityStatus
FROM Interfaces AS it
JOIN Nodes n ON it.NodeID = n.NodeID
WHERE it.LastSync >= DATEADD(MONTH, -3, GETDATE())
AND it.Interface_Priority = 'PRIORITY_4'
ORDER BY n.Caption, it.InterfaceName