SOLVED

(KQL) Searching for all tables of a customer for MSSPs

%3CLINGO-SUB%20id%3D%22lingo-sub-2108006%22%20slang%3D%22en-US%22%3E(KQL)%20Searching%20for%20all%20tables%20of%20a%20customer%20for%20MSSPs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2108006%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%2C%20I%20need%2Fwant%20to%20the%20number%20of%20records%20in%20each%20table%20(datatype)%20of%20a%20customer%20(accessed%20via%20delegation%2Flighthouse).%20So%2C%20I%20would%20like%20to%20perform%20a%20search%20*%20but%20restrict%20it%20to%20a%20specific%20workspace.%20The%20following%20KQL%20searchs%20for%20the%20tables%20in%20the%20current%20workspace%20(not%20in%20a%20customer's%20workspaces).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esearch%20*%20%7C%20summarize%20count()%20by%20%24table%20%7Csort%20by%20count_%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Note%3A%20I%20didn't%20find%20the%20documentation%20for%20the%20workspace()%20function%20(%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsearch%2F%3Fscope%3Dkusto%26amp%3Bterms%3Dworkspace%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsearch%2F%3Fscope%3Dkusto%26amp%3Bterms%3Dworkspace)%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20imagine%20something%20like%20the%20following%20(but%20of%20course%20it%20doesn't%20work)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esearch%20in%20(workspace(%22ABC%22).*)%20and%20*%7C%20summarize%20count()%20by%20%24table%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20folloow-up%20question%20is%20on%20how%20to%20restrict%20the%20time%20to%20the%20last%20month%20(like%20where%20TimeGenerated%20%26gt%3Bago(30d)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20anyone%20with%20super%20master%20of%20KQL%20skills%20to%20help%20me%3F%20or%20forwarding%20this%20to%20the%20dev%20team%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2149000%22%20slang%3D%22en-US%22%3ERe%3A%20(KQL)%20Searching%20for%20all%20tables%20of%20a%20customer%20for%20MSSPs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2149000%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F584375%22%20target%3D%22_blank%22%3E%40jjsantanna%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20is%20easy%20in%20a%20Workbook%20(it%20handles%20the%20complex%20part)%2C%20however%20these%20queries%20should%20be%20used%20infrequently%20as%20they%20are%20resource%20heavy%20-%20its%20far%20better%20to%20query%20a%20small%20set%20of%20known%20tables%20where%20possible.%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Flogs%2Fcross-workspace-query%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EQuery%20across%20resources%20with%20Azure%20Monitor%20-%20Azure%20Monitor%20%7C%20Microsoft%20Docs%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20you%20need%20to%20have%20a%20friendly%20name%2C%20you%20can%20look%20at%20my%20Workbook%20that%20maps%20the%20Workspace%20ID%20to%20the%20name%20here%3A%26nbsp%3B%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditorClive%20Watson_0%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2FCliveW-MSFT%2FKQLpublic%2Ftree%2Fmaster%2FKQL%2FWorkbooks%2FAzure%2520Sentinel%2520Central%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EKQLpublic%2FKQL%2FWorkbooks%2FAzure%20Sentinel%20Central%20at%20master%20%C2%B7%20CliveW-MSFT%2FKQLpublic%20(github.com)%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSTRONG%3ETip%3C%2FSTRONG%3E%3A%20Clone%20the%20report%20called%20%22%3CEM%3ECount%20of%20Security%20Incidents%20for%20selected%20Workspaces%20and%20Severity%3C%2FEM%3E%22%20and%20change%20the%20query%20to%20this%20(I%20suggest%20you%20keep%20the%20TimeRange%20parameter%20in%20the%20workbook%20to%20as%20few%20days%20as%20possible%20as%20well)%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3Eunion%20*%0A%2F%2F%20Get%20the%20Workspace%20Name(s)%20from%20a%20parameter%0A%7C%20extend%20stringtoSplit%20%3D%20split(%22%7BWorkspaceIDguid%7D%22%2C%22%2C%22)%0A%7C%20mv-expand%20stringtoSplit%0A%7C%20where%20stringtoSplit%20has%20TenantId%0A%7C%20extend%20workSpacename%20%3D%20trim(%40%22%5B%5E%5Cw%5D%2B%22%2Ctostring(split(stringtoSplit%2C%22%3A%22).%5B1%5D))%0A%2F%2F%20end%20of%20get%20workspace%20name%20section%0A%7C%20summarize%20count()%20by%20TableName%20%3D%20Type%2C%20workSpacename%0A%7C%20order%20by%20workSpacename%20asc%2C%20count_%20desc%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3Eresult%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-02-19%20114929.jpg%22%20style%3D%22width%3A%20884px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F256011iADCB422A7F6CD838%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-02-19%20114929.jpg%22%20alt%3D%22Screenshot%202021-02-19%20114929.jpg%22%20%2F%3E%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EKQL%20Timerange%20examples%20are%20here%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-sentinel%2Fhow-to-align-your-analytics-with-time-windows-in-azure-sentinel%2Fba-p%2F1667574%22%20target%3D%22_blank%22%3EHow%20to%20align%20your%20Analytics%20with%20time%20windows%20in%20Azure%20Sentinel%20using%20KQL%20(Kusto%20Query%20Language)%20-%20Microsoft%20Tech%20Community%3C%2FA%3E%26nbsp%3B%20%26nbsp%3BLook%20for%20%3CSTRONG%3Estartofmonth()%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hi guys, I need/want to the number of records in each table (datatype) of a customer (accessed via delegation/lighthouse). So, I would like to perform a search * but restrict it to a specific workspace. The following KQL searchs for the tables in the current workspace (not in a customer's workspaces).

 

search * | summarize count() by $table |sort by count_ 

 

* Note: I didn't find the documentation for the workspace() function (https://docs.microsoft.com/en-us/search/?scope=kusto&terms=workspace)

 

I imagine something like the following (but of course it doesn't work)

 

search in (workspace("ABC").*) and *| summarize count() by $table

 

A folloow-up question is on how to restrict the time to the last month (like where TimeGenerated >ago(30d)))

 

Is there anyone with super master of KQL skills to help me? or forwarding this to the dev team?

 

 

 

 

4 Replies
Best Response confirmed by jjsantanna (Contributor)
Solution

@jjsantanna 

 

This is easy in a Workbook (it handles the complex part), however these queries should be used infrequently as they are resource heavy - its far better to query a small set of known tables where possible.  Query across resources with Azure Monitor - Azure Monitor | Microsoft Docs

If you need to have a friendly name, you can look at my Workbook that maps the Workspace ID to the name here: 

 

KQLpublic/KQL/Workbooks/Azure Sentinel Central at master · CliveW-MSFT/KQLpublic (github.com)

Tip: Clone the report called "Count of Security Incidents for selected Workspaces and Severity" and change the query to this (I suggest you keep the TimeRange parameter in the workbook to as few days as possible as well):

union *
// Get the Workspace Name(s) from a parameter
| extend stringtoSplit = split("{WorkspaceIDguid}",",")
| mv-expand stringtoSplit
| where stringtoSplit has TenantId
| extend workSpacename = trim(@"[^\w]+",tostring(split(stringtoSplit,":").[1]))
// end of get workspace name section
| summarize count() by TableName = Type, workSpacename
| order by workSpacename asc, count_ desc



result:

 

Screenshot 2021-02-19 114929.jpg


KQL Timerange examples are here: How to align your Analytics with time windows in Azure Sentinel using KQL (Kusto Query Language) - M...   Look for startofmonth()


Excellent answer @Clive Watson 

I'm using this in a Jupyter notebook for creating customized reports for our customers.

 

I still have one question that is: how to call a workspace from a string variable. For example:

 

let variableName = 'workspaceNameX';

workspace(variableName).TableY

 

Unfortunately, workspace() doesn't accept the string. Do you know how could I do in a different way>

thanks for your fast @Clive Watson I will try it today