User Profile
Clive_Watson
Bronze Contributor
Joined 5 years ago
User Widgets
Recent Discussions
Re: need to create monitoring queries to track the health status of data connectors
Some data connectors (Monitor the health of your Microsoft Sentinel data connectors | Microsoft Learn) do write health the SentinelHealth table, Monitor the health of your Microsoft Sentinel data connectors | Microsoft Learn However for the majority you need to employ techniques like looking for when the last record was received or anomalies (this has long been the case) You can use KQL to find a Table and when it last ingested data, however you cant map a Table easily back to a Connector (or a Connector to a Table) Otherwise you need to use a Rest api to access Data Connector info (and ingest the results to use KQL) or use a Workbook.114Views1like0CommentsRe: Log Ingestion Delay in all Data connectors
Hello, I've not seen this before across such a range of connectors (assuming its not a blip and fixed itself) you might want to confirm in the Logs what the "lastSeen" or Last log recieved is. KQL union * | extend lastSeen = datetime_diff('minute',now(), TimeGenerated) | summarize arg_max(TimeGenerated,lastSeen) by TableName=Type | order by lastSeen desc65Views0likes0CommentsRe: Cannot use union * for Defender Hunting query to Create Detection Rule, so what other workarounds?
I'm sure this is because Sentinel excludes union * in the Logs blade within Sentinel (it will work outside Sentinel in similar looking logs blades, like in Log Analytics). It was excluded for performance reasons for Detections, as you could be looking through 10's, 100s or more tables and the results may not come back in enough time for the next alert trigger. More relevant for NRT or rules that trigger every 5mins. Can you union by named Table (e.g. union IdentityInfo) or Join or lookup? The screen shot you provided doesn't show the union * just a join.348Views1like1CommentRe: create incident in sentinel using logic apps after running query in azure data explorer
ADX doesn't have a trigger like you have for Sentinel. So you probably need to run your logic app on a schedule (every 5, 10, 15mins etc...) and have it run the code and then use the HTTP control to talk to the Incident API Incidents - Create Or Update - REST API (Azure Sentinel) | Microsoft Learn110Views0likes0CommentsRe: Advanced Hunting Visualize Results
If you have some PowerBI skills you can try this below? Or us the API to extract the data for another tool? Create custom Microsoft Defender XDR reports using Microsoft Graph security API and Power BI - Microsoft Defender XDR | Microsoft Learn220Views1like1CommentRe: Sentinel Log Volume vs Defender Log Volume
btw, you could have saved some typing and made sure all Tables were included, you can group with the union for example all tables that start with DEVICE or even multiple groups, as per this example union Device* , Email* | summarize RecordCount = count(), MDETotalSizeMB = round(sum(estimate_data_size(*))/pow(1024,2),2) You also have to assess if you need all the Tables duplicated in Sentinel, you normally do it for one of two reasons: 1. You need to retain the data for a longer period for ad-hoc Hunting/reporting or to meet a compliance obligation 2. You have Analytics that need the data in Sentinel - so make sure you are using the data you are syncing154Views1like0CommentsRe: How to use KQL to associate alerts with incidents?
If you do have Sentinel integration it would be this (just sharing in case you haven't seen it, and I know it wasn't the request you asked for, but the way I know that works) SecurityIncident | extend Alerts = extract("\\[(.*?)\\]", 1, tostring(AlertIds)) | mv-expand AlertIds to typeof(string), Labels to typeof(string), Comments to typeof(string), AdditionalData to typeof(string) | join kind=leftouter ( SecurityAlert //| where TimeGenerated > ago(10m) ) on $right.SystemAlertId == $left.AlertIds | summarize AlertCount=dcount(AlertIds), arg_max ( TimeGenerated, * ) by IncidentNumber72Views0likes0CommentsRe: Scheduled Analytics Rule not triggering...
I'd look to remove the ago(1d) line from the SigninLogs part as the portal applies those, and retest You can also avoid the lowercase conversion and use the ID's let PrivilgedRoles = dynamic(["Global Administrator"]); let PrivilegedIdentities = IdentityInfo | summarize arg_max(TimeGenerated, *) by AccountObjectId | mv-expand AssignedRoles | where AssignedRoles in~ (PrivilgedRoles) | summarize AssignedRoles=make_set(AssignedRoles) by AccountObjectId, AccountSID, AccountUPN, AccountDisplayName, JobTitle, Department; SigninLogs | join kind=inner PrivilegedIdentities on $left.UserId == $right.AccountObjectId | project TimeGenerated, AccountDisplayName, AccountObjectId, AccountUPN, UserPrincipalName, AppDisplayName, ResultType, ResultDescription, IPAddress, LocationDetails31Views0likes0CommentsRe: Get Custom Details from Sentinel
Hello, so if you have defined Custom Details You can then query for those (example basic query) SecurityAlert | where * contains "Custom Details" | extend CustomDetails_ = tostring(parse_json(tostring(parse_json(tostring(parse_json(ExtendedProperties).["Custom Details"])).ParentFileName))[0]) | where isnotempty(CustomDetails_) | project CustomDetails_ | take 199Views0likes0CommentsRe: Add Search Results to alert details in Microsoft Sentinel
Hello, you already get a few places that take you to the results in the Investigation UI (see below), so I guess you need this data for some other use like in your ITSM tool? You'll see a query like this SecurityAlert | summarize arg_max(TimeGenerated, *) by SystemAlertId | where SystemAlertId in("a guid will go in here") and if there are events you'll see But if you really need it again, then as Gary says, a playbook will be the option Note even with the contents of OriginalQuery you may need to amend it to re-run at the same time as the original, so you may need something like this as a new line 1 of the query set query_now = datetime("1/1/2025, 2:20:46.333 PM");22Views1like1CommentRe: KQL to extract URL from TI Feeds
or let url_ = '"3430907","2025-02-07 11:02:07","http://chmod0777kk.com/main","online","2025-02-07 11:02:07","malware_download","elf","https://urlhaus.abuse.ch/url/3430907/","anonymous"'; print url_ // assumes that HTTP is always in the 3 column (counting from 0) // trim is used to removed any " in the column | extend http_ = trim(@"[^\w]+",tostring(split(url_,',')[2]))108Views0likes0CommentsRe: Fetch security events with their underlying log entries
I tend to use this, which is very similar, you'd have to add you exclusion line into it: SecurityIncident | extend Alerts = extract("\\[(.*?)\\]", 1, tostring(AlertIds)) | mv-expand AlertIds to typeof(string), Labels to typeof(string), Comments to typeof(string), AdditionalData to typeof(string) | join kind=leftouter ( SecurityAlert //| where TimeGenerated > ago(10m) ) on $right.SystemAlertId == $left.AlertIds | summarize AlertCount=dcount(AlertIds), arg_max ( TimeGenerated, Title, Severity, Status, Owner, ModifiedBy, CreatedTime, FirstModifiedTime, LastModifiedTime, Tags=tostring(parse_json(Labels).labelName), Comments=tostring(parse_json(Comments).message), AdditionalData, Tactics, Techniques, SubTechniques, Classification, ClassificationComment, ClassificationReason, ProviderName, Description, ExtendedProperties ) by IncidentNumber or just this if you want all raw columns SecurityIncident | extend Alerts = extract("\\[(.*?)\\]", 1, tostring(AlertIds)) | mv-expand AlertIds to typeof(string), Labels to typeof(string), Comments to typeof(string), AdditionalData to typeof(string) | join kind=leftouter ( SecurityAlert //| where TimeGenerated > ago(10m) ) on $right.SystemAlertId == $left.AlertIds | summarize AlertCount=dcount(AlertIds), arg_max ( TimeGenerated, * ) by IncidentNumber25Views0likes0CommentsRe: Fetch Sentinel admin activity
There is some data in the Activity logs, here is a brief example AzureActivity | where TimeGenerated > ago(90d) | where ResourceProviderValue =~ "Microsoft.SecurityInsights" | extend eventCategory_ = tostring(parse_json(Properties).eventCategory)133Views1like0Comments
Recent Blog Articles
No content to show