Report

mjonathan

I need to put together a list of computers that start with a specific name, example AWS | XX | YY |

On that list, mark which ones had traps in the last 7 days.

On that list, mark which syslogs had in the last 7 days.

if you can help me with this I would appreciate it

Lofstrand

Hi @mjonathan 

Take this SWQL query into the report builder and I think you will have the report you want. Adjust the last line with the computer name filter to your liking.

SELECT 
    N.Caption
    ,ISNULL(LTraps.QTY,0) AS [Nr Traps]
    ,ISNULL(LSyslog.QTY,0) AS [Nr Syslog]
FROM Orion.Nodes AS N
LEFT OUTER JOIN 
(
    SELECT L1.NodeID
        ,Count(L1.LogEntryID) AS QTY
    FROM Orion.OLM.LogEntry as L1
    WHERE
        (L1.LogType.DisplayName = 'Traps')
        AND L1.DateTime>ADDDAY(-7,GETUTCDATE())
    GROUP BY
        L1.NodeID
) AS LTraps ON N.NodeID=LTRAPS.NodeID
LEFT OUTER JOIN 
(
    SELECT L2.NodeID
        ,Count(L2.LogEntryID) AS QTY
    FROM Orion.OLM.LogEntry as L2
    WHERE
        (L2.LogType.DisplayName = 'Syslog')
        AND L2.DateTime>ADDDAY(-7,GETUTCDATE())
    GROUP BY
        L2.NodeID
) AS LSyslog ON N.NodeID=LSyslog.NodeID
WHERE
    N.Caption LIKE 'CRK%'