Nested "case-when" with SWQL

sw44

Hi 

According to CHATGPT it is possible to use nesetd "case-when" with MSSQL(code example below). 

Do you know if this can work some how with SWQL? 

SELECT 
    CASE
        WHEN COUNT(CASE WHEN state = 'critical' THEN 1 END) > 0 THEN 1  -- If there's at least one Critical alert, return 1
        WHEN COUNT(CASE WHEN state = 'warning' THEN 1 END) > 0 THEN 2  -- If there's no Critical but at least one Warning alert, return 2
        ELSE 0  -- If there are no Critical or Warning alerts, return 0
    END AS alert_status
FROM your_table_name;

bobmarley

Here is an example:

SELECT tolocal(TimeLoggedUtc) AS [Last Login],

CASE
 WHEN AccountID LIKE 'domain/user1' THEN 'BobMarley'
 WHEN AccountID LIKE 'domain/user2' THEN 'Fred Flintstone'


  ELSE AccountID
END AS [Name]   , 

    AuditEventMessage

     FROM Orion.AuditingEvents AS AE
     WHERE daydiff(timeloggedutc ,getutcdate())<1
     AND AE.AuditingActionType.ActionType = 'Orion.UserLogin'
     AND AccountID NOT LIKE '%_system%'
     AND AccountID NOT LIKE '%websitemaintenance_user%'

     ORDER BY TimeLoggedUtc DESC</pre>