Log Analytics with IOTHUb

%3CLINGO-SUB%20id%3D%22lingo-sub-1264358%22%20slang%3D%22en-US%22%3ELog%20Analytics%20with%20IOTHUb%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1264358%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Members%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BI%20am%20in%20the%20process%20of%20fetching%20the%20distinct%20devices%20which%20are%20sending%20the%20data%20from%20log%20analytics%20but%20not%20quite%20getting%20the%20query%20to%20work%20as%20I%20know%20some%20syntax%20I%20am%20missing%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3EAzureDiagnostics%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%3C%2FSPAN%3E%3CSPAN%3Edistinct%3C%2FSPAN%3E%3CSPAN%3E%20(properties_s.deviceId)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20ResourceType%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22IOTHUBS%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20properties%3Dparse_json(properties_s)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20deviceid%3Dproperties.deviceId%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EThanks%20%26amp%3B%20Regards%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3ESSK%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1264358%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1265378%22%20slang%3D%22en-US%22%3ERe%3A%20Log%20Analytics%20with%20IOTHUb%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1265378%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F600857%22%20target%3D%22_blank%22%3E%40sarfrazkhs%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSPAN%3EAzureDiagnostics%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20where%20ResourceType%20%3D%3D%20%22IOTHUBS%22%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Edistinct%3C%2FSPAN%3E%3CSPAN%3E%20_ResourceId%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3EReplace%20_ResourceId%20with%20a%20working%20column%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1267194%22%20slang%3D%22en-US%22%3ERe%3A%20Log%20Analytics%20with%20IOTHUb%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1267194%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20used%20the%20query%20as%20per%20the%20one%20but%20would%20like%20to%20know%20how%20can%20I%20get%20deviceId%20in%20the%20query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3EAzureDiagnostics%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20ResourceType%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22IOTHUBS%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Edistinct%3C%2FSPAN%3E%3CSPAN%3E%20properties_s%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22sarfrazkhs_0-1585645345239.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F180818iBC3D9AAD809E1AC5%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22sarfrazkhs_0-1585645345239.png%22%20alt%3D%22sarfrazkhs_0-1585645345239.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi Members,

         I am in the process of fetching the distinct devices which are sending the data from log analytics but not quite getting the query to work as I know some syntax I am missing it.

 

AzureDiagnostics
|distinct (properties_s.deviceId)
| where ResourceType == "IOTHUBS"
| extend properties=parse_json(properties_s)
| extend deviceid=properties.deviceId
 
Thanks & Regards,
SSK
3 Replies

@sarfrazkhs 

 

AzureDiagnostics
| where ResourceType == "IOTHUBS"
| distinct _ResourceId
 
Replace _ResourceId with a working column 
 

Thanks @Clive Watson 

 

I used the query as per the one but would like to know how can I get deviceId in the query.

 

AzureDiagnostics
| where ResourceType == "IOTHUBS"
| distinct properties_s
 

sarfrazkhs_0-1585645345239.png

 

@sarfrazkhs 

 

I don't have that data, but these are a near example:

 

AzureActivity
| extend clientRequestId_ = tostring(parse_json(HTTPRequest).clientRequestId) 
| distinct clientRequestId_


AzureActivity
| extend clientRequestId_ = tostring(parse_json(HTTPRequest).clientRequestId) 
| summarize count() by clientRequestId_

 Go to Log Analytics and run query

 

You can press the "..." to extend the data into a column

Annotation 2020-03-31 160937.jpg