Hornbill Clean Utility - last logged on

[Everton1878]

Hi everyone,

 

I'm trying to use the clean utility to delete assets where the last logged on date is over 90 days ago but I'm struggling

{
	"CleanRequests": false,
	"RequestServices": [],
	"RequestCatalogItems": [],
	"RequestStatuses": [],
	"RequestTypes": [],
	"KeepRequestsCancelBPTasks": false,
	"RequestReferences": [],
	"RequestLogDateFrom": "",
	"RequestLogDateTo": "",
	"RequestClosedDateFrom": "",
	"RequestClosedDateTo": "",
	"CleanAssets": true,
	"AssetClassID": "computer",
	"AssetFilters": [{
		"ColumnName": "h_last_logged_on",
		"ColumnValue": "30",
		"Operator": "BeforeXDays",
		"IsGeneralProperty": true
	}],
	"CleanUsers": false,
	"Users": [],
	"CleanServiceAvailabilityHistory": false,
	"ServiceAvailabilityServiceIDs": [],
	"CleanContacts": false,
	"ContactIDs": [],
	"CleanOrganisations": false,
	"OrganisationIDs": [],
	"CleanSuppliers": false,
	"SupplierIDs": [],
	"CleanSupplierContracts": false,
	"SupplierContractIDs": [],
	"CleanEmails": false,
	"EmailFilters": {
		"FolderIDs": [],
		"RecipientAddress": "",
		"RecipientClass": "",
		"ReceivedFrom": "",
		"ReceivedTo": "",
		"Subject": ""
	},
	"CleanReports": false,
	"ReportIDs": []
}

when running the utility it gives me the following error

Quote

2024/04/15 12:06:30 [DRYRUN] ********** Cleaner Utility Started **********
2024/04/15 12:06:34 [DRYRUN] Confirmation Prompts Accepted
2024/04/15 12:06:34 [DRYRUN] Config File Name: conf.json
2024/04/15 12:06:34 [DRYRUN] Dry Run: true
2024/04/15 12:06:34 [DRYRUN] Skip Prompts: false
2024/04/15 12:06:34 [DRYRUN] CleanRequests: false
2024/04/15 12:06:34 [DRYRUN] CleanAssets: true
2024/04/15 12:06:34 [DRYRUN] Filtered by AssetClassID: computer
2024/04/15 12:06:34 [DRYRUN] Asset Filters:
2024/04/15 12:06:34 [DRYRUN] ==== Filter 0 ====
2024/04/15 12:06:34 [DRYRUN] Column Name: h_last_logged_on
2024/04/15 12:06:34 [DRYRUN] Column Value: 30
2024/04/15 12:06:34 [DRYRUN] Operator: BeforeXDays
2024/04/15 12:06:34 [DRYRUN] Is General Property: true
2024/04/15 12:06:34 [DRYRUN] CleanUsers: false
2024/04/15 12:06:34 [DRYRUN] CleanServiceAvailabilityHistory: false
2024/04/15 12:06:34 [DRYRUN] CleanContacts: false
2024/04/15 12:06:34 [DRYRUN] CleanOrganisations: false
2024/04/15 12:06:34 [DRYRUN] CleanSuppliers: false
2024/04/15 12:06:35 [DRYRUN] CleanSupplierContracts: false
2024/04/15 12:06:35 [DRYRUN] CleanEmails: false
2024/04/15 12:06:35 [DRYRUN] queryExec:MethodResult:com.hornbill.servicemanager:Asset.getAssetsFiltered:count:Error in SQL query, see log for more details
2024/04/15 12:06:35 [DRYRUN] Request XML: <params><application>com.hornbill.servicemanager</application><queryName>Asset.getAssetsFiltered</queryName><queryParams><resultType>count</resultType><assetClass>computer</assetClass><filters>[{&#34;column_name&#34;:&#34;h_last_logged_on&#34;,&#34;column_value&#34;:&#34;30&#34;,&#34;operator&#34;:&#34;BeforeXDays&#34;,&#34;isGeneralProperty&#34;:true}]</filters></queryParams></params>
2024/04/15 12:06:35 [DRYRUN] Response XML: <?xml version="1.0" encoding="utf-8" ?>
<methodCallResult status="fail">
    <state>
        <code>0200</code>
        <service>data</service>
        <operation>queryExec</operation>
        <error>Error in SQL query, see log for more details</error>
    </state>
</methodCallResult>

2024/04/15 12:06:35 [DRYRUN] There are no assets to delete.
2024/04/15 12:06:35 [DRYRUN] ********** Cleaner Utility Completed **********
 

I'm probably missing something obvious or being really stupid 

[SamS]

Hi @Everton1878,

h_last_logged_on is NOT a field/property of a generic (general) asset - it is located for computerSystem specific (or other Asset Class)

isGeneralProperty needs to be set to false for this field.

[Everton1878]

thanks @SamS I nearly tried that 

It is running without any error now but it isn't deleting anything

I have 4 test records in there and one of them has the last logged on date of 15/01/24 so I would expect it to be deleted

[Steve Giller]

@Everton1878 Apologies for the obvious question, but the logs above show that dryrun was set to on - have you remembered to remove that parameter?

[Everton1878]

@Steve Giller yes, I tried it without that as I thought that could be the issue 

[Steve Giller]

"AssetFilters": [{
		"ColumnName": "h_last_logged_on",
		"ColumnValue": "30",
		"Operator": "BeforeXDays",
		"IsGeneralProperty": true
	}]

That would only go back to 16th March, I believe - of course you may have updated the config. 

[Everton1878]

That's fine, but I've got a computer on there where that date is 15th Jan 2024 which is before 16th March 2024 but it isn't removing it

[SamS]

Hi @Everton1878,

I am pretty sure computer is not a valid Asset Class(*). I am quite sure you would need EITHER computerSystem OR AssetsComputer.

The former is the more likely, but do please try the latter before you let us know of success (or not - and I will doublecheck the code).

(*) I just checked the utility's documentation: it suggests computer should be OK. I'll double-check the code.

-----

I have confirmed in code that the documentation IS correct and, thus, computer IS the correct Asset Class to be used here.

Edited April 15 by SamS
checked doc & code

[SamS]

Hi @Everton1878,

As mentioned above, I've had a look at the code.

Could you please run the following on your system (using Database Direct) and let me know what the result is:

SELECT                                           
    COUNT(DISTINCT a.h_pk_asset_id) AS count
FROM
    h_cmdb_assets a
JOIN
    h_cmdb_asset_types AS t ON (a.h_type = t.h_pk_type_id)
JOIN h_cmdb_assets_computer AS ext ON a.h_pk_asset_id = ext.h_pk_asset_id 
WHERE
    t.h_class = 'computer' AND (TIMESTAMP(ext.h_last_logged_on) <= TIMESTAMP('2024-03-16 00:00:00'))

 

[Everton1878]

Hi @SamS,

The query came back with a count of 1 which is what I would expect based on the test assets I have in there

[SamS]

Hi @Everton1878,

Curious, because that is pretty much the resulting SQL.

Are you sure you are not running with -dryrun? And how are you checking whether the asset still exists? If one leaves the browser open on the particular asset's details page, then a refresh (Ctrl-F5) will likely be required in order for the browser to know the asset doesn't exist anymore.

Assuming you are affirmative on the above, could you please ensure the query results in at least TWO items...(*)

(*) there's a very old edge-case which I am suspecting here. IF I have time this afternoon, I will run through this edge-case myself, but no promises.

[Everton1878]

Hi @SamS,

I've made sure I'm not running it with -dryrun

When I run the program it tells me that there are no assets to delete and when I go into the assets module and search for the asset it is still listed

I appreciate your help with this

 

[SamS]

Hi @Everton1878,

I just ran the edge-case I suspected (issue with single asset) and I can rule that OUT

A little stumped at the moment. You are using the latest version of the utility?

[Everton1878]

Hi @SamS,

I'll try some more data to see if it is a problem with that one record and I'll make sure that I'm running the latest version of the tool

I'll let you know how I get on

[Everton1878]

Hi @SamS,

I'm using v1.20.0 of the cleaner tool and I've now got 3 assets that should be removed by the criteria but it is still reporting that there are no assets to delete

I thought it could be to do with it treating the ColumValue as text rather than a number but I tried with 30 rather than "30" and that gave an error

If I set the filter to use the operator Before and set the value as "2024-04-01 00:00:00" then it deletes the records so it is reading the correct area and recognising the dates

However, I want to be able to run it on a schedule to remove old assets