sql to update records

[billster]

Hello-

I want to use SQL to update all user records with a specific query.

SELECT h_user_id, h_employee_id, SUBSTRING_INDEX(`h_employee_id`, "@" , 1) AS stripped FROM h_sys_accounts WHERE `h_employee_id` = "billy@blah.net"

basically this would return h_employee_id as just 'billy'.

 

UPDATE h_sys_accounts SET h_attrib1 = SUBSTRING_INDEX(`h_employee_id`, "@" , 1) WHERE h_employee_id = "billy@blah.net"

 

however, the database direct is only for SELECT statements....

Is there someone at Hornbill HQ who can do this for me? There are about 1000 users so doing it manually or reimporting all users is not something i can do.

 

Basically what this is for is our SupportWorks import to Hornbill tool.

so.... on the WIKI page, it says that the employee ids on both Hornbill and SupportWorks must match for them to map. However, on Hornbill it uses UPN user names and not on SupportWorks.

so on Hornbill our IDs would be like billy@blah.net whereas on SupportWorks they would be just like 'billy'.

 

Then, what I want to do is map the h_attrib1 from Hornbill to the cust_id on the config for import tool.

CoreFieldMapping": {
"h_attrib1":[cust_id]
}

 

[Jim]

Which import are you using, is this for requests?

 

Slightly unrelated answer but in some scripts this applies: 

UPN does not have to be the matching criteria in the json config it can be any of the below

[billster]

hi Jim-yes it is for requests.  yes, I realise this, however, none of the records in Hornbill are not UPN so I need to do this query to whack the non UPN id into h_attrib1 so we don't have to reinput all users or modify manually. 

[Jim]

Ahh I see your problem, no matching data to tie them together anymore, 

Have you considered doing this in the user import script? not sure if this allows anything that way 

 

What I would end up doing in your circumstance is using PowerShell to do this via the API's linked below

GitHub - hornbill/powershellHornbillAPIModule: Powershell module for making XMLMC calls against the Hornbill platform

/ admin / userProfileSet (hornbill.com) 

[billster]

Hi Jim,

exactly right, no matching data to tie them together! 

I will have a look at this PS stuff on Monday... 

In the meantime, on the SupportWorks import utility config, do you know where I can edit the SQL statement to just import a specific job?

[Jim]

If you need any help let me know I've done a fair bit with the new powershell api module 

 

I didn't get much hands on the support works config but I believe we stripped a lot of it for testing and used 5 incidents in this section 'SQL Statement'

We used the references in an 'IN()' operator hopefully this helps

[Steve Giller]

Are you using the Supportworks Request Import tool?

If so, the config file contains the SQL to extract the Calls that you are importing as Requests - why not just manipulate the cust_id value there so that it matches whatever you need to in Hornbill?

[billster]

On 02/02/2024 at 17:07, Steve Giller said:

Are you using the Supportworks Request Import tool?

If so, the config file contains the SQL to extract the Calls that you are importing as Requests - why not just manipulate the cust_id value there so that it matches whatever you need to in Hornbill?

Hi Steve, that would work for getting customer names into imported tickets. However, within "opencall" table in SWDATA where the sql query gets all the data, there is no analyst email column, only for "owner id" so I would still need to do what I need in my original post to get it working.

 

On 02/02/2024 at 16:58, Jim said:

If you need any help let me know I've done a fair bit with the new powershell api module 

 

I didn't get much hands on the support works config but I believe we stripped a lot of it for testing and used 5 incidents in this section 'SQL Statement'

We used the references in an 'IN()' operator hopefully this helps

Hi Jim,

I tried to use an IN() operator, but, the import tool failed. I tried this... does it look right to you? I also tried AND opencall.callref = "F10245915"

"SQLStatement": "SELECT opencall.callref,  logdatex, closedatex,email, cust_id, cust_name, subject, owner, suppgroup, status, updatedb.updatetxt, priority, itsm_impact_level, itsm_urgency_level, withinfix, withinresp, probcode, fixcode, site FROM opencall, updatedb WHERE updatedb.callref = opencall.callref AND updatedb.udindex = 0 AND callclass = 'Incident' AND status in (6,16,18) AND opencall.callref IN('F10245915') ",

 

On 02/02/2024 at 16:58, Jim said:

If you need any help let me know I've done a fair bit with the new powershell api module 

I'll let you know for sure!

[Steve Giller]

If you're simply removing the "@blah.com" from the Hornbill value, can you not just add the "@blah.com" to the cust_id in the Importer query, was what I was thinking.

If you're going to be manipulating data this is not something you can do in Hornbill, and even if you raised an Expert Services Request to ask Hornbill to do it this may not be accepted.
It's far simpler to do this in Supportworks - you directly own and control that database, you could inject the email into an unused field, or even a new one to the schema within Supportworks, and then match against that for the Import.

[billster]

15 minutes ago, Steve Giller said:

If you're simply removing the "@blah.com" from the Hornbill value, can you not just add the "@blah.com" to the cust_id in the Importer query, was what I was thinking.

If you're going to be manipulating data this is not something you can do in Hornbill, and even if you raised an Expert Services Request to ask Hornbill to do it this may not be accepted.
It's far simpler to do this in Supportworks - you directly own and control that database, you could inject the email into an unused field, or even a new one to the schema within Supportworks, and then match against that for the Import.

Hi Steve,

I have tried this, the @blah.com is on the "h_employee_id" column in HB and when I try to add this to the import utility, it gives an error.

2024/02/05 10:37:59 [ERROR] Log Request Failed [Column 'h_employee_id' is not valid]

after [cust_id] I have added @blah.com so like....... 

"h_employee_id":"[cust_id]@blah.com",

 

[Jim]

4 hours ago, billster said:

I tried to use an IN() operator, but, the import tool failed. I tried this... does it look right to you? I also tried AND opencall.callref = "F10245915"

I'm not sure if this is relevant, but ours doesn't include the F's, 

 

"SQLStatement":"SELECT opencall.callref, logdatex, closedatex, cust_id, cust_name, itsm_title, owner, suppgroup, status, updatedb.updatetxt, priority, itsm_impact_level, itsm_urgency_level, withinfix, withinresp, bpm_workflow_id, probcode, fixcode, site FROM opencall, updatedb WHERE updatedb.callref = opencall.callref AND updatedb.udindex = 0 AND callclass = 'Incident' AND status < 15 AND status != 6 AND opencall.callref IN (0789852,0779402,0788229,0793492)",

[Steve Giller]

12 minutes ago, Jim said:

I'm not sure if this is relevant, but ours doesn't include the F's

callref has never included the F, nor any preceding zeros - it's an Integer field. Including them will not effectively match.
I believe that some versions have an additional field, which I cannot remember the name of off the top of my head, that holds the full reference to make reporting a little simpler.

I'm not sure where you got a requirement for matching h_employee_id from, I can't see it mentioned in the Documentation, and it does not exist in the h_itsm_requests table.

[Jim]

6 minutes ago, Steve Giller said:

callref has never included the F, nor any preceding zeros - it's an Integer field. Including them will not effectively match.
I believe that some versions have an additional field, which I cannot remember the name of off the top of my head, that holds the full reference to make reporting a little simpler.

Well that explains our lack of F's in our query, I'm guessing the next part is aimed at @billster, From what I can gather its to help resolve h_fk_user_id to a [cust_id] as he has no matching data between the same users on the 2 systems, I think this ought to match the userID to the userid within hornbill though so rather than stripping the domain name, it actually needs to concatenated to the user id they have in support works ??  

[billster]

20 minutes ago, Jim said:

I'm not sure if this is relevant, but ours doesn't include the F's, 

 

"SQLStatement":"SELECT opencall.callref, logdatex, closedatex, cust_id, cust_name, itsm_title, owner, suppgroup, status, updatedb.updatetxt, priority, itsm_impact_level, itsm_urgency_level, withinfix, withinresp, bpm_workflow_id, probcode, fixcode, site FROM opencall, updatedb WHERE updatedb.callref = opencall.callref AND updatedb.udindex = 0 AND callclass = 'Incident' AND status < 15 AND status != 6 AND opencall.callref IN (0789852,0779402,0788229,0793492)",

 

12 minutes ago, Steve Giller said:

callref has never included the F, nor any preceding zeros - it's an Integer field. Including them will not effectively match.
I believe that some versions have an additional field, which I cannot remember the name of off the top of my head, that holds the full reference to make reporting a little simpler.

I'm not sure where you got a requirement for matching h_employee_id from, I can't see it mentioned in the Documentation, and it does not exist in the h_itsm_requests table.

Thanks guys, I have it only importing a test ticket now.

3 minutes ago, Jim said:

I think this ought to match the userID to the userid within hornbill though so rather than stripping the domain name, it actually needs to concatenated to the user id they have in support works ??  

yes, correct, however, the only field with UPN name is h_employee_id the user IDs in our Hornbill instance use SID format.  TBH I was hoping it would have been possible to link using email but I doubt it..

[Jim]

Okay I see the issue, does the email address look right? and is that available in Supportworks? 

 

My not so clean but may work solution would be as follows - don't import customers but store the email in a custom field, in the process do the following -> Get Request details - > get user by email in

the screenshot

 

--> update customer with the retrieved user id 

[Jim]

This made sense in my head, the layout doesn't help  but essentially store the email address in the custom fields, use that custom field to search for the user and retrieve the hornbill user id, update the customer, all as part of the hornbill workflow 

[billster]

1 minute ago, Jim said:

Okay I see the issue, does the email address look right? and is that available in Supportworks? 

 

My not so clean but may work solution would be as follows - don't import customers but store the email in a custom field, in the process do the following -> Get Request details - > get user by email in

the screenshot

 

--> update customer with the retrieved user id 

Hi Jim - this is what I'm exactly trying to achieve. I have mapped h_custom_a as the email address from SW in the config file. All good. however, now, the BPM does not work when importing the jobs LOL! 

[billster]

Just now, billster said:

Hi Jim - this is what I'm exactly trying to achieve. I have mapped h_custom_a as the email address from SW in the config file. All good. however, now, the BPM does not work when importing the jobs LOL! 

Like, if I copy a request from an imported one then the workflow activates. just not when I import a job  

[Jim]

Ahhhh great minds  I can't think why a process would not execute once a catalog item is provided, I have seen this issue before though I can't think what it was at the time.  I think it was an error when scheduled requests were added as a feature, so wouldn't relate to your issue, Ironically I think not setting a customer may cause some issues to do with subscriptions etc, maybe try adding a default value of a dummy account that lives in Hornbill

[Jim]

I would also check the log file to see if it complains of any access issues if its permissions related, although I don't think that should matter considering its gotten as far as actually logging the request, I would assume the catalog item is actually published?

[billster]

10 minutes ago, Jim said:

Ahhhh great minds  I can't think why a process would not execute once a catalog item is provided, I have seen this issue before though I can't think what it was at the time.  I think it was an error when scheduled requests were added as a feature, so wouldn't relate to your issue, Ironically I think not setting a customer may cause some issues to do with subscriptions etc, maybe try adding a default value of a dummy account that lives in Hornbill

I know right   I set the owner and customer IDs as the built in admin account but, the workflow still doesn't run. All the workflow is doing is sending a "direct message send" mail, going into the direct outbound page, it doesn't show it tried to send anything neither does it show on just the normal sent items mailbox folder.

 

7 minutes ago, Jim said:

I would also check the log file to see if it complains of any access issues if its permissions related, although I don't think that should matter considering its gotten as far as actually logging the request, I would assume the catalog item is actually published?

yes, the catalog item is published and I can create a request manually in there and it is all ok.  TBH I've no idea how to go through these log files on Hornbill, although the log file from the job importer has no errors.

[Jim]

hmmm, A button which you will learn to love as Admin, create a custom button for each request type which opens a URL as the following -- replace the {instanceName} with your own, it will link you straight to the running process (when it exists mind)

https://live.hornbill.com/{instanceName}/admin/app/com.hornbill.servicemanager/manage-executed-processes/![[h_bpm_id]]/?stateFilter=other