Error in stored query when using Get Asset By Class option

[Alisha]

Hello,

I have used the Data Query and Get Assets By Class option in one of our captures. However, I am receiving the below errors when the capture loads.  How can I get to the log to see the details about the error please?

Many thanks,
Alisha

[Hornbill Staff DR]

Hi Alisha,

 thanks for your post.

The logs can be found in Hornbill Administration via Home > System > Monitor > Monitor Log Files and this error will be logged in EspServerService.log

The logs are only accessible by someone with the "Admin Role" security role and typically only show the activity for the current day (as they're archived when they reach a certain size).

If you don't have permission to access the logs I would advise speaking with the person responsible for administering Hornbill as they will be the ones who have the access.

Once you've found the error, please post it here and we will be able to offer further assistance.

I hope that helps,

Dan

[Alisha]

Hi @DanielRi,

We think this is the error.

583991	20-10-2018 08:55	error	perf	6684	data:queryExec() Method call results: failure (398520320 B, 10 ms, -20 kB, 1 ms, 0 kB)
583990	20-10-2018 08:55	warn	system	6684	XMLMC Request Failed: Error in stored query, see log for more details
583989	20-10-2018 08:55	error	sql	6684	The maximum number of rows allowed (1000) has been exceeded, query aborted
583988	20-10-2018 08:55	info	sql	6684	SELECT

   a.h_name as VALUE, a.h_name as TEXT

    FROM h_cmdb_assets AS a

	JOIN h_cmdb_asset_types AS t ON (a.h_type = t.h_pk_type_id)

	LEFT JOIN h_cmdb_asset_substate AS s ON (a.h_substate_id = s.h_pk_asset_substate_id)

    WHERE 1

	   AND (

			a.h_pk_asset_id like CONCAT('', '%') OR

			a.h_version like CONCAT('', '%') OR

			a.h_description like CONCAT('', '%') OR

			a.h_notes like CONCAT('', '%') OR

			a.h_class like CONCAT('', '%') OR

			a.h_company_name like CONCAT('', '%') OR

			t.h_name like CONCAT('', '%') OR

            a.h_name like CONCAT('', '%') OR

            a.h_used_by_name like CONCAT('', '%') OR

            a.h_owned_by_name like CONCAT('', '%') OR

            a.h_created_date like CONCAT('', '%') OR

			a.h_last_updated like CONCAT('', '%') OR

			a.h_operational_state like CONCAT('', '%') OR

			a.h_location like CONCAT('', '%') OR

			a.h_country like CONCAT('', '%') OR

			a.h_site like CONCAT('', '%') OR

			a.h_building like CONCAT('', '%') OR

			a.h_floor like CONCAT('', '%') OR

			a.h_room like CONCAT('', '%') OR

			a.h_rack like CONCAT('', '%') OR

			a.h_u_height like CONCAT('', '%') OR

			a.h_u_position like CONCAT('', '%') OR

			a.h_asset_tag like CONCAT('', '%') OR

			a.h_order_number like CONCAT('', '%') OR

			a.h_supplier_id like CONCAT('', '%') OR

			a.h_substate_name like CONCAT('', '%') OR

			a.h_product_id like CONCAT('', '%') OR

			a.h_warranty_expires like CONCAT('', '%')

		)

		 AND t.h_class = 'Computer'

 [5877 us]
583987	20-10-2018 08:55	info	system	6684	Using application context: com.hornbill.servicemanager
583986	20-10-2018 08:55	info	perf	6684	Method Call: data:queryExec (27019038416)
583985	20-10-2018 08:55	info	general	6684	Invocations (8235279b-ab01-4c9b-8124-500b913dafee): data/queryExec

Many thanks,
Alisha

[Alisha]

Could this be happening because we have thousands of imported assets?

[Victor]

@Alisha - we have a (temp) solution for this but it should really be fixed by paginating the query results. But this is something for dev team to look into.

Quick (and temporary) fix for the error is to increase the max number of results returned by a query which can be done by amending this system setting: api.xmlmc.queryExec.maxResultsAllowed

Note: increasing this number affects the results returned by all queries in the system which can potentially mean a performance impact. Although unlikely, it needs to be mentioned so you are aware.