I can pull record within SWQL studio but not able to use the same query in Modern Dashboard Widget or Report. I need to extract "MYUSER" from the message. What am I missing?
APM.WIndowsEvent has bunch of entries whenever an account is locked. There is no column which stores just the username and is part of the message which I need to extract.
Ex of an entry:
| A user account was locked out. Subject: Security ID: S-1-5-18 Account Name: OURDC-05$ Account Domain: MYDOMAIN Logon ID: 0x3E7 Account That Was Locked Out: Security ID: S-1-5-21-158212blah-373394blah-11026blah-14324 Account Name: MYUSER Additional Information: Caller Computer Name: MYCORPDC |
Today at 10:12 PM
|
SELECT DISTINCT
ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(ComputerName,'.','|§|§|')),0) AS DC_Name
, REPLACE(ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(Message,':','|§|§|')),3), ' ' , '|§|§|')),0), 'Account', '') AS created_by
, REPLACE(ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(Message,':','|§|§|')),8), ' ' , '|§|§|')),0), 'Account', '') AS user_name
FROM Orion.APM.WindowsEvent
WHERE EventCode Like '%4720%'
AND ComputerName LIKE '%TOUDC-%'
AND Day(GetDate()) = Day(TOLOCAL(TimeGeneratedUtc))
AND Month(GetDate()) = Month(ToLocal(TimeGeneratedUtc))
AND Year(GetDate()) = Year(ToLocal(TimeGeneratedUtc))
In Widget I get the below error:
Any help would be appreciated. Something which will be very helpful in a lot of similar use cases of data collected through OLV, LEM, Syslog, Trap, etc....
