query? Service Manager Request Import - Published Known Errors and Problems

[Martyn Houghton]

@Steve G

I trying to import Known Errors into to Service Manager but hitting an issue with how I import their published status. Looking in the database there is a separate table h_itsm_published_requests which sits over Problem and Known Errors. As part of the import I need to be able to pass a flag to indicate whether the request being imported should be published.

Is there anyway to currently do this as I am on a tight timetable to migrate the data from a system which has to be decommissioned very soon.

Cheers

Martyn

[Steve G]

@Martyn Houghton I don't think there is currently, I'll have a look and get back to you...

Cheers,

Steve

[Martyn Houghton]

@Steve G

From looks of it we would need to being to import into the h_itsm_published_requests as a request type, or have these common fields adding to the AdditionalFieldMapping section.

I as presuming just adding them to the above section in the config file would not work at the moment?

Cheers

Martyn

[Steve G]

Hi @Martyn Houghton,

No that wouldn't work I'm afraid. It should be a very small change that's required to the tool though, will take a look tomorrow and hopefully get a release out then.

Cheers,

Steve 

[Martyn Houghton]

@Steve G

It also looks like the date conversion process needs to be extended to include the 'h_last_updated' additional field mapping for the column in h_itsm_knownerrors (same field is also present in the h_itsm_problems as well).

"DateTimeFormat": "02/01/2006 15:04",

MethodResult not OK: New Request : Invalid date/time format provided: [27/04/2022 12:29] for column h_last_updated

Cheers

Martyn

[Steve G]

@Martyn Houghton We've just released v1.9.0 of the tool, which includes the ability to publish problems & known errors, fixed the datetime parsing issue for extended properties, and added the owner mapping ability too. It's on GitHub now, the wiki docs will be updated shortly.

https://github.com/hornbill/goHornbillRequestImport/releases/tag/v1.9.0

Cheers,

Steve 

[Steve G]

@Martyn Houghton Actually, it's v1.9.1 you need, as I'd missed off the date parsing from the new published requests feature. It's in there now: https://github.com/hornbill/goHornbillRequestImport/releases/tag/v1.9.1

Cheers,

Steve

[Martyn Houghton]

@Steve G

That's great and thanks for the fast turnaround.

Cheers 

Martyn

[Martyn Houghton]

@Steve G

We tested this and are able to import and publish the requests. One issue though is the last updated date is not being honoured and this is getting set to the current import time.

I tried setting both the 'h_last_updated' and the 'DatePublihed' but it still seems to show the import time. Is it possible to override this?

Cheers

Martyn

 "AdditionalFieldMapping": 
	  {
        "h_custom_a": "",
        "h_custom_b": "",
        "h_custom_c": "",
        "h_custom_d": "",
        "h_custom_e": "",
        "h_custom_f": "",
        "h_custom_g": "",
        "h_custom_h": "",
        "h_custom_i": "",
        "h_custom_j": "",
        "h_custom_k": "",
        "h_custom_l": "",
        "h_custom_m": "",
        "h_custom_n": "",
        "h_custom_o": "",
        "h_custom_p": "",
        "h_custom_q": "DTKE1",
		"h_custom_r": "",
		"h_last_updated":"[announcement_date]",
		"h_root_cause":"[announcement]",
		"h_steps_to_resolve":"[workaround]"
      },
      "RequestHistoricUpdateQuery": "SELECT 'Imported announcement from MyUniform' as h_description, announcement_date FROM knownerrors WHERE ke_id={RequestGUID}",
      "HistoricUpdateMapping": {
        "h_updatedate": "[announcement_date]",
        "h_timespent": "",
        "h_updatetype": "",
        "h_updateindex": "",
        "h_updateby": "",
        "h_updatebyname": "[ke_owner]",
        "h_updatebygroup": "Data Transfer",
        "h_actiontype": "",
        "h_actionsource": "[ke_owner]",
        "h_description": "[h_description]"
      },
	  "PublishedMapping":{
		"Publish":true,
		"Description":"[announcement]",
		"LanguageCode":"en-GB",
		"CreateEnglish":false,
		"PublishedStatus":"publish",
		"DatePublished":"[announcement_date]",
		"ShowWorkaround":true,
		"Workaround":"[workaround]"

 

[Steve G]

@Martyn Houghton,

Looks like the Customer Portal is indeed showing the Last Updated date for the published known issue rather than the published date. I'll add in support to set that field too. The last updated field in the additional field mapping sets that field in the extended attributes of the request rather than in the published requests table. 

Cheers,

Steve

[Martyn Houghton]

@Steve G

Ok, so though I had the h_last_updated in my config above for the AdditionalFieldMapping, the current version does not process it?

Cheers

Martyn

[Steve G]

@Martyn Houghton

No, it was processing h_last_updated against the extended details table(s) for Problems and Known Errors, but published requests are stored in their own table, and it was those that needed updating. I've added support for that in v1.10.0, on GitHub: https://github.com/hornbill/goHornbillRequestImport/releases/tag/v1.10.0

Take a look at the LastUpdated property of the PublishedMapping object.

Cheers,

Steve

[Martyn Houghton]

@Steve G

Having an issue with the LastUpdated pramater, as it does not seem to be working and inserting the current date/time.

 

	  "PublishedMapping":{
		"Publish":true,
		"Description":"[announcement]",
		"LanguageCode":"en-GB",
		"CreateEnglish":false,
		"PublishedStatus":"publish",
		"DatePublished":"[announcement_date]",
		"ShowWorkaround":true,
		"Workaround":"Originally Published on MyUniform [announcement_date]",
		"LastUpdated":"[announcement_date]"
	  }

I am passing in the value in the date time format

  "DateTimeFormat": "02/01/2006 15:04",

It seems to be just inserting a maximum value like it is a type conversion issue.

Any idea?

Cheers

Martyn

[Steve G]

Hi @Martyn Houghton,

I'll take a look at that this morning, these were imported with the correct date using v1.10.0 last week prior to release, so I'm wondering if there's an issue with the parsing. I presume the dates provided for your fields are all in the same format?

Thanks,

Steve

[Martyn Houghton]

@Steve G

I take another look at the data, as due to the known bug in the pagination on the Known Issue page on the portal you cannot see the other pages at the moment. I suspect there may be some differences between my test record sample and the full load I did when the issue occurred.

Cheers

Martyn

[Martyn Houghton]

@Steve G

Issue does indeed seem to be down to the date time parsing.

2022/05/23 08:20:35 [ERROR] parseDateTime Failed for [h_datelogged]: parsing time " 18/05/2022 16:04" as "02/01/2006 15:04": cannot parse " 18/05/2022 16:04" as "02"
2022/05/23 08:20:35 [DEBUG] [DATETIME] Failed to parse DateTime stamp  18/05/2022 16:04 against configuration DateTimeFormat 02/01/2006 15:04

2022/05/23 08:20:35 [ERROR] parseDateTime Failed for [h_datelogged]: parsing time " 18/05/2022 16:04" as "02/01/2006 15:04": cannot parse " 18/05/2022 16:04" as "02"
2022/05/23 08:20:35 [DEBUG] [DATETIME] Failed to parse DateTime stamp  18/05/2022 16:04 against configuration DateTimeFormat 02/01/2006 15:04

Configuration is 

  "DateTimeFormat": "02/01/2006 15:04",

What I found is that the was a leading space on the date field on some of the records, but enough records in the dataset to mean all I could see was them on the first page on the customer portal.

Cleared it out with MySQL TRIM() to get rid of leading and following spaces.

Cheers

Martyn

[Steve G]

@Martyn Houghton Thanks for confirming it was a data issue, glad you worked around it with TRIM(). I'll trim whitespace from date fields when parsing in the code for the next release so we're not caught out by this again!

Cheers,

Steve