list all log sources
So I'm just getting started with Microsoft Sentinel, and am looking for a way to produce a single report "listing all log sources". I'm looking for something like: TableName, SourceType, ComputerName I have tried CommonSecurityLog | summarize by DeviceVendor, Computer From Link, but Only get the first DeviceVendor (Fortinet in my case) but I don't get all other types.
Escape character in KQL?
Hello guys, I have a problem where I'm unable to escape characters in my KQL query. We are ingesting some custom windows event logs that are being "normalized" in XML format. However, when sentinel normalizes these events, it puts all of them nested fields. Does this for some of the Azure logs as well. However, usually I can retrieve some data from those by using the following query AzureActivity | where OperationName == "signin" | expand Identity == OperationName.AdditionalFields.LoginIdentity So basically using the expand I extract identity field that is nested within other 2 fields. This works fine, however, the log I'm working with has numbers as field. So when I try the same thing but with AzureActivity | where OperationName == "signin" | expand Identity == OperationName.01.LoginIdentity It errors, it doesn't like that "01" part and query wont run, not that because 01 doesn't exist because it does but because its an integer? not sure. So I am trying to escape the "01" part. See below for the log example that might help explaining So in the image above, I want to extract fields like operation, processIdentity etc. So then I can use this fields for further filtering or use it to build a workbook. So then in my query I can say, WindowsEvent | where EventID = "7777" | where processIdentity = "identity" any help apricated 🙂
Combine 2 columns in Single coulmn in KQL
Hi , I have data in sign-in logs as username and location, I want to combine username, location columns and add it to 3rd column. How I can do it in KQL. I have data like- User Name Location User-1 IN User-2 US User-3 GB User-4 MX I want it like following- User Details User-1 - IN User-2 - US User-3 - GB User-4 - MXKQL query in sentinel for users first activity
Hi all, since I am new to writing queries I would really appreciate your help. I need to write a query that will show a specific users first activity and last activity in a day. I need to also project which activity it was and to sum it all up, I need the results to be in a row for each day. I would really appreciate any suggestions or help. Thank you
Sentinel KQL Query to retrieve last sign-in date.
Can someone take a look at my queries and see if they can find any errors please? My original query below provides as output all disabled accounts for the previous month and includes the admin who took the action, the disabled user along with their information and the time the account was disabled. //All User account that were disabled the previous month let lastmonth = getmonth(datetime(now)) - 1; let year = getyear(datetime(now)); let monthEnd = endofmonth(datetime(now), -1); SecurityEvent | where TimeGenerated >= make_datetime(year, lastmonth, 01) and TimeGenerated <= monthEnd | extend Disabled_EST = datetime_utc_to_local(TimeGenerated, "US/Eastern") | where EventID == "4725" | where AccountType == "User" |join IdentityInfo on $left.TargetSid== $right.AccountSID | summarize by TimeGenerated, Disabled_EST, Account, Activity, MemberName, TargetAccount, Computer, AccountDisplayName, GivenName, Surname | order by Disabled_EST asc Now the auditors want to also see when the disabled account was last signed-in so I need to add another column to the above, however I could not find any values from the IdentityInfo, the SecurityEvent and the SigninLogs tables that can be used to join the tables. So what I did was to start from scratch and use the slit function to create a field that I could use as a key for the join operation. The query below works as expected although I believe the builtin datetime_utc_to_local() function no longer works? As I'm still getting the UTC time it appears. SigninLogs |extend LastLoginTimeEST = datetime_utc_to_local(TimeGenerated, "US/Eastern") | extend NetAccount_ = tostring(split(AlternateSignInName, "@")[0]) | project-away AlternateSignInName | summarize max(LastLoginTimeEST) by NetAccount_, OperationName, AuthenticationRequirement So then the original query was modified to include the above query. Now I'm able to get as output the required columns and the last sign-in of the user. However, as mentioned earlier, the results appear to be incorrect as the date/time in this query does not match the output of the standalone SigninLogs query above. //Working but incorrect results shown in lastLogin_EST column let lastmonth = getmonth(datetime(now)) - 1; let year = getyear(datetime(now)); let monthEnd = endofmonth(datetime(now), -1); let SecurityEvents = SecurityEvent | where TimeGenerated >= make_datetime(year, lastmonth, 01) and TimeGenerated <= monthEnd //| extend EST_Disabled = datetime_utc_to_local(TimeGenerated, "US/Eastern") | where EventID == "4725" | where AccountType == "User" | join kind=leftouter (IdentityInfo | project AccountName, AccountDisplayName, GivenName, Surname) on $left.TargetUserName == $right.AccountName; let LastSigninLogs = SigninLogs //| extend LastLogin_EST = datetime_utc_to_local(TimeGenerated, "US/Eastern") | extend IdName=split(AlternateSignInName,"@", 0) | extend NetAccount_ = tostring(IdName[0]) | project-away IdName | summarize LastLogin_EST = max(TimeGenerated) by NetAccount_, OperationName, AuthenticationRequirement; SecurityEvents //|where Surname == "xyz" //use a lastname to reduce output for verification | join kind=leftouter LastSigninLogs on $left.TargetUserName == $right.NetAccount_ | summarize max(TimeGenerated)by TimeGenerated,Account, Activity, TargetAccount, Computer, AccountDisplayName, GivenName, Surname, LastLogin_EST, OperationName, AuthenticationRequirement //|extend DisabledEST= datetime_utc_to_local(max_TimeGenerated, "US/Eastern") Can someone take a look at help me find the bug or is this actually correct?
