I am trying to search all Measures and Dimensions captured from Extended events of sql server analytics service.
index=xxx sourcetype=extendedevent NTUserName=xxx DatabaseName=xxx AND NOT (NTUserName IN (xxxxx) ) SELECT | rex "TextData=(?P.*);NTCanonicalUserName" | rex field=TextData1 max_match=10000 "\[Measures\].\[(?[a-z0-9A-Z _]+)]" |rex field=TextData1 max_match=10000 "\"(?[a-z0-9A-Z _]+)\"" |eval Measures1= "[Measures]"
|rex field=TextData1 max_match=10000 "[{(](?[a-z0-9A-Z -_\].[]+)" | table Measures1, Measures, Dimensions ,TextData1, Dim ,_raw, DatabaseName
when i try | where Dimensions NOT match(Dimensions,Measures1 ) it excludes all result for that match not just one result.
index=xxx sourcetype=extendedevent NTUserName=xxx DatabaseName=xxx AND NOT (NTUserName IN (xxxxx) ) SELECT | rex "TextData=(?P<TextData1>.*);NTCanonicalUserName" | rex field=TextData1 max_match=10000 "\[Measures\].\[(?<Measures>[a-z0-9A-Z _]+)]" |rex field=TextData1 max_match=10000 "\"(?<Measures>[a-z0-9A-Z _]+)\"" |eval Measures1= "[Measures]"
|rex field=TextData1 max_match=10000 "[{(](?<Dimensions>[a-z0-9A-Z -_\].[]+)" | where Dimensions NOT match(Dimensions,Measures1 ) | table Measures1, Measures, Dimensions ,TextData1, Dim ,_raw, DatabaseName
Example
SELECT {[Measures].[Responses],[Measures].[Scored Responses]} DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize({DrilldownLevel({[Sales User - Creator].[Manager Full Name].[All]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS FROM [MIA] WHERE ([Date - Created].[Fiscal Yr Qtr Period Day].[Fiscal Year].&[2019].&[2019-Q4].&[2019-10]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
It captures the perfectly the measures
Responses
Scored Responses
But not sure how can better do with capturing the Dimensions , my idea is to capture everything in brackets and then excludes the measures.
| makeresults
| eval _raw="SELECT {[Measures].[Responses],[Measures].[Scored Responses]} DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize({DrilldownLevel({[Sales User - Creator].[Manager Full Name].[All]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS FROM [MIA] WHERE ([Date - Created].[Fiscal Yr Qtr Period Day].[Fiscal Year].&[2019].&[2019-Q4].&[2019-10]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS"
| rex mode=sed "s/{.*}//"
| rex max_match=0 "\[(?<inblacket>.*?)\]"
Thanks It gave me different direction to think.
If you resolve your question, please accept it.