Query Acceleration for Delta External Tables (Preview)
An external table is a schema entity that references data stored external to a Kusto database. Queries run over external tables can be less performant than on data that is ingested due to various factors such as network calls to fetch data from storage, the absence of indexes, and more. Query acceleration allows specifying a policy on top of external delta tables. This policy defines a number of days to cache data for high-performance queries. Query Acceleration policy allows customers to set a policy on top of external delta tables to define the number of days to cache. Behind the scenes, Kusto continuously indexes and caches the data for that period, allowing customers to run performant queries on top. QAP is supported by Azure Data Explorer (ADX) over ADLSgen2/blob storage and Eventhouse over OneLake/ADLSgen2/blob storage. Query Acceleration policy We are introducing a new policy to enable acceleration for delta external tables: Syntax .alter external table <TableName> policy query_acceleration 'Policy' Where: <TableName> is the name of a Delta Parquet external table. <Policy> is a string literal holding a JSON property bag with the following properties: IsEnabled : Boolean, required. - If true, query acceleration is enabled. Hot: TimeSpan, last 'N' days of data to cache. Steps to enable Query Acceleration Create a delta external table as described inthis document: .create-or-alter external table <TableName> kind=delta ( h@'https://storageaccount.blob.core.windows.net/container;<credentials> ) Set a query acceleration policy .alter external table <TableName> policy query_acceleration ```{ "IsEnabled": true, "Hot": "36500d" }``` Query the table. external_table('TableName') Note: Indexing and caching might take some time depending on the volume of data and cluster size. For monitoring the progress, see Monitoring command Costs/Billing Enabling Query Acceleration does come with some additional costs. The accelerated data will be ingested in Kusto and count towards the SSD storage, similar to native Kusto tables. You can control the amount of data to accelerate by configuring number of days to cache. Conclusion Query Acceleration is a powerful feature designed to enhance your data querying capabilities on PetaBytes of data. By understanding when and how to use this feature, you can significantly improve the efficiency and speed of your data operations - whether you are dealing with large datasets, complex queries, or real-time analytics, Query Acceleration provides the performance boost you need to stay ahead. Get started with Azure Data Explorer. Get started with Eventhouse in Microsoft Fabric.
Sysmon /operational is not in Event table
Hi Team, Need to create usecase base onSysmon /operational and with Event ID = 1. But Sysmon is not configured. Usecase is based on process. It is github usecase. Need to create with the help of defender table. Windows Binaries Lolbins Renamed KQL : Event | where EventLog =~ "Microsoft-Windows-Sysmon/Operational" and EventID==1 | parse EventData with * 'Image">' Image "<" * 'OriginalFileName">' OriginalFileName "<" * | where OriginalFileName has_any (procList) and not (Image has_any (procList)) | parse EventData with * 'ProcessGuid">' ProcessGuid "<" * 'Description">' Description "<" * 'CommandLine">' CommandLine "<" * 'CurrentDirectory">' CurrentDirectory "<" * 'User">' User "<" * 'LogonGuid">' LogonGuid "<" * 'Hashes">' Hashes "<" * 'ParentProcessGuid">' ParentProcessGuid "<" * 'ParentImage">' ParentImage "<" * 'ParentCommandLine">' ParentCommandLine "<" * 'ParentUser">' ParentUser "<" * | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated) by EventID, Computer, User, ParentImage, ParentProcessGuid, ParentCommandLine, ParentUser, Image, ProcessGuid, CommandLine, Description, OriginalFileName, CurrentDirectory, Hashes | extend HostName = iif(Computer has '.',substring(Computer,0,indexof(Computer,'.')),Computer) , DnsDomain = iif(Computer has '.',substring(Computer,indexof(Computer,'.')+1),'') Now same usecase need to be configured with the help of defender table "DeviceProcessEvents". But don't now how to find out Image information which is in Event Table.Auto Disabled (Rule Name)
Hi Team, One of scheduled rule is auto disabled 2 days ago (31-aug) and showing like "The alert rule was disabled due to too many consecutive failures. Reason: The query was blocked as it was consuming too many resources." When I tried to re-enabled and it showing: "Failed to save analytics rule 'rule name'. Conflict:Newer instance of rule 'ID' exists for workspace 'workspace id' (Etag does not match). Data was not saved." I made some changes in KQL but still showing same message. Can someone help me to find out solution ? "
Workbook with multiple visualizations using lowest number of queries
Coming from Splunk world and didn't found answer to this in the workbook documentation. Is it possible to chains searches, like in Splunk, explained here: https://docs.splunk.com/Documentation/Splunk/9.3.1/DashStudio/dsChain Trying to explain in KQL terms: suppose there are3 very similar queries, like same base search | condition 1 same base search | condition 2 same base search | condition 3 feeding 3 vizualizations. Goal is to execute the "same base search" part only once in the workbook. Defining a new function for "same base search" still means 3 executions, I guess. Your response is appreciated. Thank you.Help to write KQL for some of the use case
Hi Team, Please help me to write a KQL for below scenario. Log sources are (Palo alto, checkpoint, F5, Citrix, Akamai, Vectra, oracle, Linux) Use case - Source sending more events than usual Description - This correlation search identifies source hosts sending more data than usual. The search runs against data from the last 7 days, and compare only the same hour of the last 7 days (this helps avoiding alerts in the beginning of business hours). The threshold is the sum of average events plus the standard deviation of the source times 3. Usecase - Unexpected Host Reporting events Description - Discovers hosts that are reporting events but are not on the expected reporting host on Sentinel. This rule is used to monitor hosts not expected. Usecase - New User Account Created on multiple Hosts Description - Alerts when numerous new accounts are created for a username account on multiple hosts. Note : All above usecase are deployed in Splunk and need to migrate into sentinel.
Splunk eventstats equivalent in kql?
is there an equivalent eventstats command in kql similar to splunk? If not, is there a way to achieve same result in kql? eventstats command generates summary statistics from fields in your events and saves those statistics into a new field. The eventstats command places the generated statistics in new field that is added to the original raw events.
KQL- in/has-any usage
For the below query, when I use "contains" for single app its works fine but have bulk AppIDs to check, how can i use "in' here? query fails when I replace contains with in or has-any. please help. thank you. let AppIDList = dynamic(["APPID01", "APPID02", "APPID03"]); resources |wheretype!in~("microsoft.compute/snapshots","microsoft.compute/virtualmachines/extensions") | project subscriptionId, type, resourceGroup, name,AppID = tostring(['tags']['AppID']) //Here AppID is comma sepeated list os AppIDs |whereAppIDin(AppIDList) |joinkind=inner( resourcecontainers |where['type']=="microsoft.resources/subscriptions" |projectsubscriptionId,name,subname=name )on$left.subscriptionId==$right.subscriptionId | project subname, subscriptionId, type, resourceGroup, name
