Question on Maximum records returned by log query (30000)

Question

Question on Maximum records returned by log query (30000)

kumar kaushal 176 Microsoft Employee

We have a subscription which comprise of 430 VM's. Making use of Inventory and change tracking feature of automation account. The Software piece that is listed under inventory has almost 33K entries . Now when we run the query :

ConfigurationData
| where ConfigDataType == "Software"
| order by TimeGenerated desc

We are only able to see 30000 entries and when we export in CSV Filter with Computer name , we see that we have results for only for some 45 machines for last 24 hours. If we increase the selection to last 7 days we get like 65 machines but the record still see 30000 entries .

I think we are hitting the limitation listed below , Is their a way we can get a list for all 430 VM's ?

https://learn.microsoft.com/en-us/azure/azure-resource-manager/management/azure-subscription-service-limits

Azure portal

Category Limit Comments
Maximum records returned by a log query 30,000 Reduce results using query scope, time range, and filters in the query.

1 answer

Your answer

Answer 1

SwathiDhanwada-MSFT 18,996 Moderator

@kumar kaushal Kindly note that at any point of time, maximum records that any query can return is 30000 which you are aware from the prompt you have seen. In your case, I would suggest you to split the time duration and retrieve the records in blocks and export it.

For your reference ,same information is documented in this article.

kumar kaushal 176 Reputation points Microsoft Employee

2021-06-22T08:36:14.627+00:00

@SwathiDhanwada-MSFT .. Thank you for your response. I have one more question in addition to this: By default log analytics workspace keep the data for last 30 days . Now when i use inventory -->Software -->Query log analytics .. and i am running the query for last 30 days ..

ConfigurationData
| where ConfigDataType == "Software"
| order by TimeGenerated desc

And i get like 57 machines ..

This means that 57 machines have reported a software change in these last 30 days and their data is available in the log analytics workspace .

Iis that my understanding correct ?
SwathiDhanwada-MSFT 18,996 Reputation points Moderator

2021-06-22T14:34:32.477+00:00
@kumar kaushal I think you might already be aware that to view the last reported state for in-guest configuration data such as Files Software Registry Keys Windows Services and Linux Daemons you can use the ConfigurationData table. This data is tracked by Change Tracking feature of Azure Automation. The above query does not retrieve the list of machines that have reported the state. To view the number of software changes that has been on each of virtual machine for last 30 days, you can use below query.

ConfigurationData| | where TimeGenerated>= ago(30d) | summarize count() by Computer, ConfigDataType
kumar kaushal 176 Reputation points Microsoft Employee

2021-06-22T14:59:16.483+00:00

@SwathiDhanwada-MSFT .. .. I executed the below query and what i found is : It gives the count of softwares installed against the each VM.

I
ConfigurationData|
| where TimeGenerated>= ago(30d)
| summarize count() by Computer, ConfigDataType

How can we get a list of softwares that are installed on each VM , which query will help us in this case ?

Because with the below query i was able to get the list/names of softwares installed on the VM although i got only for like 57 VM's as we reached the limit of 30000

ConfigurationData
| where ConfigDataType == "Software"
| order by TimeGenerated desc

I think i know the answer .. what we can do is filter the query using _resourceid or apply any other filters ..
SwathiDhanwada-MSFT 18,996 Reputation points Moderator

2021-06-23T11:17:35.07+00:00
@kumar kaushal To get a list of softwares that are installed on each VM, you can use below query.

ConfigurationData | where ConfigDataType == "Software" | summarize SoftwareName = make_set(SoftwareName) by Computer
kumar kaushal 176 Reputation points Microsoft Employee

2021-06-28T07:54:05.273+00:00

@SwathiDhanwada-MSFT ... I made use of https://learn.microsoft.com/en-us/azure/azure-monitor/visualize/powerbi and we were able to get like results for 250 VM's. For smaller subscription that has almost like 250 VM's it works but larger subscription that have like 450 VM's it only shows the data for 250 VM's.

Is their a limitation on the no. of VM's that power BI can populate data for ?

Share via

Question on Maximum records returned by log query (30000)

1 answer

Your answer