1

I have a working query for my app data to be analyzed. currently it analyzes the last two weeks data with an ago(14d).

Now i want to use a value containing the release date of the apps current version. Since i havent found a way to add a new database table to the already existing database containing the log data in azure analytics, i created a new database in azure and entered my data there.

Now i just don't know, if i can get access to that database at all from within the web query interface of Azure log analytics, or if i have to use some other tool for that?.

i hope that somebody can help me on this. As always with azure there is a lot of stuff to read about it, but nothing concrete for my issue (or at least i haven't found it yet).

And yes, i know how to insert the data into the query with a let, but since I want to use the same data in different queries, an external location which can be accessed from all the queries would be the solution I prefer.

Thx in advance. Maverick

Chamberlain
  • 881
  • 5
  • 17
Maverick1st
  • 3,774
  • 2
  • 34
  • 50

2 Answers2

1

You cannot access a db directly. You are better of using a csv/json file in blob storage. In the following example I uploaded a txt file with csv data like this:

2a6c024f-9093-434c-b3b1-000821a15b1a,"Customer 1"
28a658a8-5466-45ea-862c-003b20507dd4,"Customer 2"
c46fb949-d807-4eea-8de4-005dd4beb39a,"Customer 3"
e05b67ee-ff83-4805-b004-0064449f196c,"Customer 4"

Then I can reference this data from log analytics / application insights in a query like this using the externaldata operator:

let customers = externaldata(id:string, companyName:string)  [
    h@"https://xxx.blob.core.windows.net/myblob.txt?sv=2019-10-10&st=2020-09-29T11%3A39%3A22Z&se=2050-09-30T11%3A39%3A00Z&sr=b&sp=r&sig=xxx"  
]  with(format="csv");
requests
| extend CompanyId = tostring(customDimensions.CustomerId)
| join kind=leftouter
(
    customers
)
on $left.CompanyId == $right.id

The url https://xxx.blob.core.windows.net/myblob.txt?sv=2019-10-10&st=2020-09-29T11%3A39%3A22Z&se=2050-09-30T11%3A39%3A00Z&sr=b&sp=r&sig=xxx is created by creating a url including a SAS token by using the Microsoft Azure Storage Explorer, selecting a blob and then right click -> Get Shared Access Signature. In the popup create a SAS and then copy the uri.

Peter Bons
  • 26,826
  • 4
  • 50
  • 74
  • 1
    tyvm for your answer. i will try it out as soon as i have time. even though i agreed with my boss on another solution now, this will definitely come in handy for some other analytics for me. – Maverick1st Oct 15 '20 at 08:16
0

i know Log Analytics uses Azure Data Explorer in the back-end and Azure Data Explorer has a feature to use External Tables within the queries but I am not sure if Log Analytics support External Tables.

External Tables in Azure Data Explorer https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/schema-entities/externaltables#:~:text=An%20external%20table%20is%20a,and%20managed%20outside%20the%20cluster.

Hassan Raza
  • 412
  • 2
  • 6
  • i guess the main problem here is to tell azure log analytics the connection string to being able to connect to that external database table. Haven't found any solution yet on this. – Maverick1st Aug 24 '20 at 10:31
  • log analytics can ingest data from Azure Storage BLOB, so in theory at least you can add your external data to Azure Storage BLOB and ingest it into Log Analytics and then write your query. Again this is all in theory I have never tried it myself. I have ingested my W3C and HTTP logs from an Azure BLOB Storage account into Log Analytics. – Hassan Raza Aug 24 '20 at 10:35
  • That sounds like a way to go, even though a blob might be a bit oversized for what i need. Do you mind giving me an example on how you did this as an answer? if this works for me, and it sounds like that, i will select it as the correct answer. – Maverick1st Aug 25 '20 at 15:16
  • i used following example: https://learn.microsoft.com/en-us/azure/azure-monitor/platform/data-sources-iis-logs – Hassan Raza Aug 26 '20 at 11:28