Service Desk Practitioners Forum
cancel

Data Exchange , How to create new field Search_Code when exporting ?

SOLVED
Go to solution
Highlighted
brian ryberg
Trusted Contributor.

Data Exchange , How to create new field Search_Code when exporting ?

The aim is to create a Search_Code value when exporting from source (Fluke), and check if the read value from Fluke is with a ".", if so we only want the leftmost part of the string - otherwise (if no ".") then we want the entire string assigned to be a unique "search_code" in Service Desk.

I am trying to solve a problem with integrating Fluke tool into Servicedesk 4.5 , but am having a hard time finding which string functions are allowed in defining the DataExchange .INI file ?!

It seems that stuff like STR() are supported, and then I stumbled over
"OpenView Operations Integration Administrator’s
Guide" , and in there they show this nifty little example og SUBSTR/INSTR as well.
Just what I think I need, but I need to adopt something thats understood by SQL Server (This seems to be Oracle stuff??)

Currently I have this .ini file, but it fails if there ARE no "." in the name, error is

Section[HOSTS]: SqlError: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid length parameter passed to the substring function.

.INIfile command is :
[hosts]
SOURCE=[DeviceType], [Hosts]
ATT=[SearchCode], \
[Hosts_Host_ID], \
[Hosts_Description], \
[Hosts_DescType], \
[Hosts_MacAddr], \
[Hosts_Manufacturer], \
[Hosts_NetbiosName], \
[Hosts_IpAddr], \
[DeviceType_Description]
COLUMNS=replace(upper(LEFT([Hosts].[Description],CHARINDEX([Hosts].[Description],'.')-1)), '-','') as [SearchCode], \
[Hosts].[Host_ID] AS [Hosts_Host_ID], \
[Hosts].[Description] AS [Hosts_Description], \
[Hosts].[DescType] AS \
[Hosts_DescType], [Hosts].[MacAddr] AS \
[Hosts_MacAddr], [Hosts].[Manufacturer] AS \
[Hosts_Manufacturer], [Hosts].[NetbiosName] AS \
[Hosts_NetbiosName], [Hosts].[IpAddr] AS [Hosts_IpAddr], \
[DeviceType].[Description] AS [DeviceType_Description]
LOADTABLE=TRUE
CONDITION=[Hosts].[DeviceType_ID] = [DeviceType].[DeviceType_ID] AND [Hosts].[Description] is NOT NULL



Anyone out there have a suggestion to get me further on this ??
jadajadajada
4 REPLIES
David Borojevic
Outstanding Contributor.

Re: Data Exchange , How to create new field Search_Code when exporting ?

Hi Brian,

We use replace function successfully to remove spaces from our searchcode. ie.

replace([WPUsers].[WPLastName] + ',' +[WPUsers].[WPFirstName],' ','') AS [WPUsers_SearchCode]

and what you have fro replace looks good. Are you sure that the Left and Charindex is OK? What if there is no "." - is LEFT([Hosts].[Description],-1) possibly causing the error?

Cheers
brian ryberg
Trusted Contributor.

Re: Data Exchange , How to create new field Search_Code when exporting ?

Yes, the error is caused by a missing "." , to avoid it , we would have to use some logic in the .inifile.

But I found that I needed the "-1" to remove the "." which would otherwise actually be included as the last char in the string.

So we need kind of :
1. Check if there's a "." in the string
2. if not - use entire string
else, do the
3. "replace(upper(LEFT([Hosts].[Description],CHARINDEX([Hosts].[Description],'.')-1))) as [SearchCode]"

- and in a single "replace" command !?

But I just cant find any examples, and I am apparently not experienced enough with SQL coding to get it working by trial'n error :)


Regards,
Brian
jadajadajada
Jasper Verweij
Acclaimed Contributor.
Solution

Re: Data Exchange , How to create new field Search_Code when exporting ?

CHARINDEX works the other way around. IN your case the string of ??? is going to be verified with the string '.' in stead of the other way around.

The correct syntax in your case would be:

replace(upper(case when CHARINDEX('.',[Hosts].[Description])> 0 then left([Hosts].[Description],CHARINDEX('.',[Hosts].[Description])-1)
else [Hosts].[Description] end),'-','')

Jasper
David Borojevic
Outstanding Contributor.

Re: Data Exchange , How to create new field Search_Code when exporting ?

Hi again Brian,

If after correcting the CHARINDEX as per jaspers comment, you still get errors when there is no "." (ie if LEFT() doesn't like -1 as an argument) then you could break it into 2 separate classes using the CONDITION - one for the Host.descriptions containing "." and one without and obviously only do the replace on the class with the "."

A SQL guru might know a better way - but this splitting it is easy and would work.

Cheers