SOLVED

Storing static data in table to use in KQL

%3CLINGO-SUB%20id%3D%22lingo-sub-1091802%22%20slang%3D%22en-US%22%3EStoring%20static%20data%20in%20table%20to%20use%20in%20KQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1091802%22%20slang%3D%22en-US%22%3E%3CP%3EUnable%20to%20maintain%20static%2Fdynamic%20data%20sets%20for%20below%20sample%20use%20cases.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20Cases%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EIncrease%20in%20failed%20domain%20admin%20account%20logins%20detected%3C%2FLI%3E%3CLI%3EPassword%20change%20or%20rest%20on%20known%20privileged%20account%3C%2FLI%3E%3CLI%3EInteractive%20login%20(Success%20or%20Failed)%20from%20Service%20Account%3C%2FLI%3E%3C%2FOL%3E%3CP%3EEx%3A%20Interactive%20login%20(Success%20or%20Failed)%20from%20Service%20Account%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIdeally%20service%20accounts%20are%20used%20for%20application%20level%20integration.%20We%20need%20to%20trigger%20an%20alert%20if%20interactive%2Fremote%20interactive%20login%20observed%20from%20service%20accounts.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrent%20work%20around%3A%20I%20have%20hard%20coded%20the%20all%20our%20service%20accounts%20in%20the%20KQL%20query.%20Which%20is%20not%20feasible%20in%20long%20run.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChallenge%3A%20If%20new%20service%20accounts%20are%20provisioned.%20We%20are%20missing%20monitoring%20on%20those%20service%20accounts%20until%20I%20add%20them%20in%20KQL%20Query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAsk%3A%20Is%20there%20any%20workaround%2C%20KQL%20to%20get%20the%20data%20from%20storage%20account%20like%20blob%20%2F%20can%20I%20create%20table%20%2FAD%20using%20scripts%20on%20scheduled%20basis%20%2Fstore%20in%20log%20analytics.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1091802%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMaintain%20Static%20Data-KQL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1091884%22%20slang%3D%22en-US%22%3ERe%3A%20Storing%20static%20data%20in%20table%20to%20use%20in%20KQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1091884%22%20slang%3D%22en-US%22%3EWhat%20I%20would%20do%20in%20that%20moment%20is%20add%20the%20service%20accounts%20to%20a%20specific%20group%20or%20use%20a%20unique%20attribute%20and%20filter%20your%20KQL%20query%20to%20that%20attribute%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1092137%22%20slang%3D%22en-US%22%3ERe%3A%20Storing%20static%20data%20in%20table%20to%20use%20in%20KQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1092137%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F492724%22%20target%3D%22_blank%22%3E%40Pavan_Gelli1910%3C%2FA%3E%26nbsp%3BYou%20can%20create%20your%20own%20custom%20log%20table%20and%20add%20the%20entries%20there.%26nbsp%3B%20This%20page%20has%20a%20PowerShell%20script%20that%20shows%20you%20the%20steps.%26nbsp%3B%20It%20should%20be%20easy%20enough%20to%20modify%20for%20your%20needs%20or%20to%20use%20it%20as%20a%20basis%20for%20a%20different%20language.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fgallery.technet.microsoft.com%2FPowerShell-script-to-0823e09d%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgallery.technet.microsoft.com%2FPowerShell-script-to-0823e09d%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1092169%22%20slang%3D%22en-US%22%3ERe%3A%20Storing%20static%20data%20in%20table%20to%20use%20in%20KQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1092169%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F492724%22%20target%3D%22_blank%22%3E%40Pavan_Gelli1910%3C%2FA%3E%26nbsp%3BJust%20saw%20this%20timely%20post%20on%20the%20Azure%20Sentinel%20blog%20page.%26nbsp%3B%20Could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-sentinel%2Fimplementing-lookups-in-azure-sentinel-part-1-reference-files%2Fba-p%2F1091306%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-sentinel%2Fimplementing-lookups-in-azure-sentinel-part-1-reference-files%2Fba-p%2F1091306%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1093914%22%20slang%3D%22en-US%22%3ERe%3A%20Storing%20static%20data%20in%20table%20to%20use%20in%20KQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1093914%22%20slang%3D%22en-US%22%3EThis%20is%20really%20the%20best%20article%20to%20address%20my%20ask.%20Thanks%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Unable to maintain static/dynamic data sets for below sample use cases.

 

Use Cases:

 

  1. Increase in failed domain admin account logins detected
  2. Password change or rest on known privileged account
  3. Interactive login (Success or Failed) from Service Account

Ex: Interactive login (Success or Failed) from Service Account:

 

Ideally service accounts are used for application level integration. We need to trigger an alert if interactive/remote interactive login observed from service accounts.

 

Current work around: I have hard coded the all our service accounts in the KQL query. Which is not feasible in long run.

 

Challenge: If new service accounts are provisioned. We are missing monitoring on those service accounts until I add them in KQL Query.

 

Ask: Is there any workaround, KQL to get the data from storage account like blob / can I create table /AD using scripts on scheduled basis /store in log analytics.

 

Please help.

4 Replies
Highlighted
What I would do in that moment is add the service accounts to a specific group or use a unique attribute and filter your KQL query to that attribute
Highlighted

@Pavan_Gelli1910 You can create your own custom log table and add the entries there.  This page has a PowerShell script that shows you the steps.  It should be easy enough to modify for your needs or to use it as a basis for a different language.

 

https://gallery.technet.microsoft.com/PowerShell-script-to-0823e09d

 

Highlighted
Best Response confirmed by Pavan_Gelli1910 (Occasional Contributor)
Highlighted
This is really the best article to address my ask. Thanks