LogAnalytics Query (LogsQueryClient in python) returns 0 rows (and all column names)

Question

Here's a simple Kusto Query Language query (replaced the table name).

let endDateTime = now(); let startDateTime = ago(1d); AnyTableName | where TimeGenerated < endDateTime | where TimeGenerated >= startDateTime

When executed in the explorer, it returns proper results. But when used in the following manner, returns 0 rows.

credential  = DefaultAzureCredential()

client = LogsQueryClient(credential)

query="""let endDateTime = now();
let startDateTime = ago(1d);
AnyTableName
| where TimeGenerated < endDateTime 
| where TimeGenerated >= startDateTime"""

print (query)

os.environ['LOGS_WS_ID'] = 'XXXX'

start_time=datetime(2022, 11, 4, tzinfo=timezone.utc)
end_time=datetime(2022, 11, 5, tzinfo=timezone.utc)

try:
    response = client.query_workspace(os.environ['LOGS_WS_ID'], query, timespan=(start_time, end_time))    
    if response.status == LogsQueryStatus.PARTIAL:
        error = response.partial_error
        print(error.message)
    elif response.status == LogsQueryStatus.SUCCESS:
        print("SUCCESSSSS")
        data = response.tables
    for table in data:
        df = pd.DataFrame(data=table.rows, columns=table.columns)
        print(df)
except HttpResponseError as err:
    print("Well !!! This is no good")
    print (err)

Result >>

[0 rows x 29 columns]

I was expecting the same result as I see in the Logs Analytics explorer, but the script returns 0 rows.

Answer 1

I have used the same code to reproduce your issue. I can be able to get the get the result as per the query we given.

let endDateTime = now();
let startDateTime = ago(1d);
AnyTableName
| where TimeGenerated < endDateTime 
| where TimeGenerated >= startDateTime

The workaround follows:

I have used the below script with required query and collected all the metrics table data:

credential = DefaultAzureCredential()
client = LogsQueryClient(credential)  

query = """let endDateTime = now();
    let startDateTime = ago(1d);
    AppMetrics
    | where TimeGenerated < endDateTime
    | where TimeGenerated >= startDateTime"""

  
start_time=datetime(2022, 11, 6, tzinfo=timezone.utc)
end_time=datetime(2022, 11, 7, tzinfo=timezone.utc)

try:
    response = client.query_workspace(
    workspace_id='<Your Workspace Id>',
    query=query,
    timespan=(start_time, end_time)
)

if  response.status == LogsQueryStatus.PARTIAL:
    error = response.partial_error
    data = response.partial_data
    print(error.message)
elif  response.status == LogsQueryStatus.SUCCESS:
    data = response.tables
    print(data)
    for  table  in  data:
        df = pd.DataFrame(data=table.rows, columns=table.columns)
        print(df)

except  HttpResponseError  as  err:
print("Error Message:")
print (err)

In above I have collected all the App metrics data for the required time with the interval of 1 day and kept both in query and start_time & end_time both were same time interval. For confirmation I have made small query change which follows below. (Collect only 5 values)

# Modified Query
query = """let endDateTime = now();
    let startDateTime = ago(1d);
    AppMetrics | take 5
    | where TimeGenerated < endDateTime
    | where TimeGenerated >= startDateTime"""

Results