query InsightMetrics with where clause on Tags

%3CLINGO-SUB%20id%3D%22lingo-sub-1288185%22%20slang%3D%22en-US%22%3Equery%20InsightMetrics%20with%20where%20clause%20on%20Tags%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1288185%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20I%20want%20to%20run%20a%20query%20to%20match%20an%20entry%20on%20tags%20which%20is%20a%20json%20like%20-%3C%2FP%3E%3CTABLE%20width%3D%22832%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%3ETenantId%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ESourceSystem%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ETimeGenerated%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EComputer%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EOrigin%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ENamespace%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EName%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EVal%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ETags%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EAgentId%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EType%3C%2FTD%3E%3CTD%20width%3D%22128%22%3E_ResourceId%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EValues%20of%20Tags%20Column%20-%26gt%3B%3C%2FP%3E%3CP%3E%7B%22address%22%3A%22x.x.x.x%22%2C%22app.kubernetes.io%2Fcomponent%22%3A%22compact%22%2C%22app.kubernetes.io%2Finstance%22%3A%22app%22%2C%22app.kubernetes.io%2Fmanaged-by%22%3A%22app2%22%2C%22app.kubernetes.io%2Fname%22%3A%22mayank%22%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20something%20like%3C%2FP%3E%3CP%3E-----------------------------%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3EInsightsMetrics%20%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20Tags%20%3D%20parse_json(Tags)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20Namespace%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22prometheus%22%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%7C%20where%20Tags.app.kubernetes.io%2Fname%20%3D%3D%20mayank%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I%20am%20not%20able%20to%20do%20so%20and%20getting%20empty%20records.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1288185%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EApplication%20Insights%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1289519%22%20slang%3D%22en-US%22%3ERe%3A%20query%20InsightMetrics%20with%20where%20clause%20on%20Tags%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1289519%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F612613%22%20target%3D%22_blank%22%3E%40mayanks%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20%22%2F%22%20is%20the%20issue%20as%20its%20a%20control%20character%20in%20KQL.%26nbsp%3B%20%26nbsp%3BThis%20would%20be%20an%20example%20that%20works%2C%20you%20can%20alter%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3EInsightsMetrics%0A%7C%20extend%20Tags%20%3D%20parse_json(Tags)%0A%2F%2F%7C%20where%20Namespace%20%3D%3D%20%22prometheus%22%0A%7C%20extend%20memorySizeMB_%20%3D%20tostring(Tags.%5B%22vm.azm.ms%2FmemorySizeMB%22%5D)%20%0A%7C%20where%20memorySizeMB_%20%3D%3D%20%22mayank%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAA13NvQ6CMBSG4Z2EezjpJAu9gi5uDrjoRgw5wRNazWlJT%25252F2BcPEWXYjrly%25252FPe%25252FDiBpukoRRdL2WxAL0T%25252BSuccRAwMGIU6m4S%25252FG5dqrLQeoGXpUhwRCYZsScwBtQYA1Oy9BC1YZg4xOnkZmr2XfZSkFzyw1erW%25252FXkGmeuWfT2qS4VrMiv82fkFuOE%25252Fq4%25252BWIsom74AAAA%25253D%2Ftimespan%2FP1D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20run%20query%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eor%20better%20still%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3EInsightsMetrics%0A%7C%20extend%20Tags%20%3D%20parse_json(Tags)%0A%2F%2F%7C%20where%20Namespace%20%3D%3D%20%22prometheus%22%0A%7C%20where%20Tags.%5B%22vm.azm.ms%2FmemorySizeMB%22%5D%20%20%26gt%3B%205%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1291030%22%20slang%3D%22en-US%22%3ERe%3A%20query%20InsightMetrics%20with%20where%20clause%20on%20Tags%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1291030%22%20slang%3D%22en-US%22%3EThanks%20Clive%20that%20worked%20for%20me%20%3A)%3C%2Fimg%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi I want to run a query to match an entry on tags which is a json like -

TenantIdSourceSystemTimeGeneratedComputerOriginNamespaceNameValTagsAgentIdType_ResourceId

Values of Tags Column ->

{"address":"x.x.x.x","app.kubernetes.io/component":"compact","app.kubernetes.io/instance":"app","app.kubernetes.io/managed-by":"app2","app.kubernetes.io/name":"mayank"}

 

I want something like

-----------------------------

InsightsMetrics
| extend Tags = parse_json(Tags)
| where Namespace == "prometheus"

| where Tags.app.kubernetes.io/name == mayank

 

But I am not able to do so and getting empty records.

2 Replies

@mayanks

 

The "/" is the issue as its a control character in KQL.   This would be an example that works, you can alter

InsightsMetrics
| extend Tags = parse_json(Tags)
//| where Namespace == "prometheus"
| extend memorySizeMB_ = tostring(Tags.["vm.azm.ms/memorySizeMB"]) 
| where memorySizeMB_ == "mayank"

 

 Go to Log Analytics and run query

 

or better still

 

InsightsMetrics
| extend Tags = parse_json(Tags)
//| where Namespace == "prometheus"
| where Tags.["vm.azm.ms/memorySizeMB"]  > 5

 

Thanks Clive that worked for me :)