This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

[SWQL] Custom queries with text/hyperlink within table

Hi,

First of all, apologies for the lengthy inquiry and if this is posted in the wrong section, I'm quite new to SWQL. So here goes, as per the title, there are a few inquiries regarding the matter:

1. My custom query is to display "nodes up/nodes available" and "transactions up/transactions available" in a custom table widget

Currently looks like:

App NameNodes UpTotal NodesTransactions UpTotal Transactions
Application #110102020

Prefer to look like (Nodes/Transactions < Total, text should turn red, else, text is green):

App NameNodes UpTotal Transactions
Application #110/1018/20
Application #2 10/10 20/20

2. Would like to ask whether it is possible to use simple hyperlink referencing ("_LinkFor_" seems to require the URLs being available within the DB) within a SWQL query. As I understand and tried so far, using "<a href>" does not seem to work with SWQL

--Working example that displays each node link

SELECT

n.caption AS [Node],

n.DetailsURL AS [_Linkfor_Node]

FROM orion.nodes as n

WHERE ((([n].[CustomProperties].[AppFilter]) = ('AppTag'))) WITH NOLOCK

--Example of what is needed

SELECT

'TEST' AS [App],

'https://google.com/' AS [_Linkfor_App]

FROM orion.nodes as n

WHERE((([n].[CustomProperties].[AppFilter]) = ('AppTag'))) WITH NOLOCK




3. How can I apply custom colours within my query (using CASE, so far have not been able to make it work). Had referred to a sample here: Text colour to display alerts using custom query

4. Merging data within two separate columns with custom format (per above preferred table)

5. The table should display Application #1, Application #2, etc. When using "Count", I am unable to add rows for the applications nor being able to use "TOP #".

I've managed to display each nodes/transactions available within the Applications instead (also by default it allows to create Hyperlinks via the "_LinkFor_Node"), however, what is needed is the summary count of all nodes/transactions

Your advice and guidance is highly appreciated, thank you in advance.

Below is my work-in-progress sample query:

--Counts total nodes available
SELECT
'AppName' AS [App],
(
     SELECT
     Count([N].[SystemUpTime]) AS [Nodes Up]
     FROM orion.nodes as N
     WHERE
     ((([N].[CustomProperties].[AppFilter]) = ('AppTag')))
     WITH NOLOCK
) AS [Nodes Up],

--Counts total nodes that are up
(
     SELECT
     Count([NUp].[Category]) AS [Total Nodes]
     FROM orion.nodes as NUp
     WHERE
     ((([NUp].[CustomProperties].[AppFilter]) = ('AppTag')))
     WITH NOLOCK
) AS [Total Nodes],

--Counts total transactions that are up
COUNT ([TUp].[Status]) AS [Transactions Up],
(
     (
          --Counts total transactions available (based on status)
          SELECT
          COUNT ([T].[Status]) AS [Total Transactions]
          FROM orion.nodes AS T
          WHERE
          ((([T].[Agent].[Probe].[Engine].[SEUMAgents].[Transactions].[CustomProperties].[AppFilter]) = ('AppTag'))
          AND
          (([T].[Agent].[Probe].[Engine].[SEUMAgents].[Transactions].[Status]) IS NOT NULL))
          WITH NOLOCK
     )
) as [Total Transactions]
FROM orion.nodes AS TUp
WHERE
((([TUp].[Agent].[Probe].[Engine].[SEUMAgents].[Transactions].[CustomProperties].[AppFilter]) = ('AppTag'))
AND
(([TUp].[Agent].[Probe].[Engine].[SEUMAgents].[Transactions].[Status]) = (1)))

  • My lazy work around for this is just to leverage the custom query resource with swql and the [_iconfor_] syntax and throw red and green check marks and such next to the lines im interested in with case logic.

    pastedImage_0.png

    , CASE 

    WHEN MinutesSinceKeepAlive > 2 THEN '/Orion/images/ActiveAlerts/Serious.png' 

    WHEN MinutesSinceKeepAlive <2 THEN '/Orion/images/ActiveAlerts/Check.png' 

    END AS [_IconFor_MinutesSinceKeepAlive] 

    If you want to get more fancy with it this solution from antonis.athanasiou​ allows you to embed CSS styles directly into his modified custom query resource.

    Custom Query resource with colors and styles

  • Thanks for the suggestion, will take a look into that. Just to update, already managed to create the outcome desired via the use of SQL as there seems to be some limitations with SWQL. Yet to figure out how change the text colours based on each count statement (works on text without statements though) - most likely incorporating CASE statements.

    Found that for "Transactions", when converted to SQL, uses different name/syntax. Now is the matter to figure out the correct naming context per comparison below:

    For Comparison of columns:

    SWQLSQL

    From orion.nodes

    From dbo.Nodes

    [Nodes].[<column selected>], e.g: [Nodes].[SystemUpTime]

    Nodes.<column selected>, e.g: Nodes.SystemUpTime

    [Nodes].[Agent].[Probe].[Engine].[SEUMAgents].[Transactions].[CustomProperties].[AppFilter]

    To find out
  • You may want to take a look at this write up by animelov  SWQL Walkthrough

    This may help you see how SWQL relates to SQL, and how to "read" [Nodes].[Agent].[Probe].[Engine].[SEUMAgents].[Transactions].[CustomProperties].[AppFilter] to be able to convert to SQL

    In layman terms, SWQL is performing a function that is going to involve a bunch of JOIN statement is SQL.