converting FQDN to short names

[sunil2244]

Hi All,

I am beginner in cognos and this community. Been working on creating simple reports with drag and drop, but now going deeper by writing queries and some logic.

So I have a requirement that I have some host names in mysql db and they are in fqdn form like myhost.mydomain.com. But customer need only myhost in the report, elimiating .mydomain.com. I wrote a logic like:

CASE
WHEN LOWER([Host Name]) like LOWER('%my.domain.name%')
THEN substring([Host Name],1,char_length([Host Name])-16)
ELSE [Host Name]
END

The problem is this work for a single host, but if there are many hosts in a cluster then it won't.

Unfortunately number of hosts varies from 1 to n (n <10). When I tiried to use same expression, it only convert fqdn to short for last host name in that cell, leaving remaining host in fqdn form.

For eg:
Actual Data in host:
abcyq09.my.domain.name, abcyq0a.my.domain.name, abcyq0b.my.domain.name, abcyq0c.my.domain.name, abcyq0d.my.domain.name, abcyq0e.my.domain.name, abcyq0f.my.domain.name, abcyq0g.my.domain.name

After using above expression, result is:

abcyq09.my.domain.name, abcyq0a.my.domain.name, abcyq0b.my.domain.name, abcyq0c.my.domain.name, abcyq0d.my.domain.name, abcyq0e.my.domain.name, abcyq0f.my.domain.name, abcyq0g

Leaving the remaining hosts in fqdn form.

BTW, there is no fixed limit of number of character of host names and domain name.

Can I get some help to fix that?

[AnalyticsWithJay]

Code Select Expand

substring('abcyq09.my.domain.name', 1, position ('.', 'abcyq09.my.domain.name') - 1)

In the above code, we get the position of the first 'period' in the string, and then we grab all characters up to that position.

[sunil2244]

Thanks for response. But you have hardcoded the host name which I don't want. And like I said the host name length may vary from rows to rows.

[AnalyticsWithJay]

Thanks for response. But you have hardcoded the host name which I don't want. And like I said the host name length may vary from rows to rows.

The hardcoded value is just a sample. You can replace the string with your dynamic data item.

The code provided doesn't care about the length of the string. It gets the position of the period after the host name.

[BigChris]

I'm probably stating the obvious, but just in case, your expression would become something like:

Code Select Expand

substring([YourDomainNameField], 1, position ('.', [YourDomainNameField]) - 1)

[sunil2244]

Thanks BigChris and Jay.

I applied the given expression but problem remain the same. Don't know if I failed to explain it properly or what.
Let me repeat it again.

If I have single record in a cell, then even my expression is also working but if there are multiple servers in a cluster then given expression is only providing me first server name. For eg:

Original record                                                                                                                                  After using expression
-----------------                                                                                                                               ---------------------------
abcyq09.my.domain.name, abcyq0a.my.domain.name, abcyq0b.my.domain.name                               abcyq09

It also removed other 2 records which are separated by comma.

Result I am expecting is: abcyq09, abcyq0a, abcyq0b

[AnalyticsWithJay]

Original record                                                                                                                                  After using expression
-----------------                                                                                                                               ---------------------------
abcyq09.my.domain.name, abcyq0a.my.domain.name, abcyq0b.my.domain.name                               abcyq09

It also removed other 2 records which are separated by comma.

Result I am expecting is: abcyq09, abcyq0a, abcyq0b

Oh, I think Chris and I misunderstood. I thought it was one domain per record, not multiple separated by commas.

This is not difficult. You want to break up the string into an array, use the expression I gave you on the array elements, and then re-construct the string. Luckily the Cognos macro functions do all of this.

Take a look at the split(), csv(), and array() macro functions in Cognos and let me know if you have trouble constructing the syntax.

[sunil2244]

Hi Jay,

Since the requirement is urgent, I would appreciate if you can provide that syntax. I am also not in touch with programming so will take longer time to do many hit and trials.

And if you can provide me pointers to learn more about these, it would be of great help.

Also to give a background of application I am using. Application name is OnCommand Insight from Netapp which have their own front end but for backend and reporting cognos is used. So not sure if I have all the functionalities of full cognos application. I have 10.2.2 version.

[sunil2244]

Thread bump

[BigChris]

Recognise that you want to get to the answer as quickly as possible, but you should really try this first:

Take a look at the split(), csv(), and array() macro functions in Cognos and let me know if you have trouble constructing the syntax.

Then come back with any specific problems that you come across

[AnalyticsWithJay]

I re-read your requirement and I see in your example that the domain name is the same within a single row. If so, there's an easier method:

I used Oracle as my data source so you'll have to change the function syntax to use MYSQL.


String Being Tested:
'abcyq09.my.domain.name, abcyq0a.my.domain.name, abcyq0b.my.domain.name'

Domain Position Start

Code Select Expand

instr([String], '.')
This is the Oracle equivalent of the position() function.

Domain Position End

Code Select Expand

instr([String], ',')
This is the Oracle equivalent of the position() function.

Domain

Code Select Expand

SUBSTR([String], [Domain Position Start], [Domain Position End] - [Domain Position Start])
Substring to get the domain.

Final String

Code Select Expand

replace ([String], [Domain], NULL)

To summarize, we simply get the first period (.) and first comma (,) occurrence in the string. This also marks the end points of the domain name.

We get the domain using the string position, and replace any instances of it with null. I think this is the simplest method given that you're new to Cognos.

If the domains are different within a row, you'll likely have to use a regular expression. There are DB functions like regexp_replace() (oracle) that allow this.

[sunil2244]

Hi Jay, thanks for providing the logic and making to look way more simpler. I am writing the syntax but cognos is throwing error while validating the substr expression:

substr ([Additional Hosts], instr([Additional Hosts], '.'), instr([Additional Hosts], ',') - instr([Additional Hosts], '.'))

[Additional Hosts] is the string to be tested.

Error is:
UDA-QOS-0006 Error
UDA-SQL-0223 A sort is attempted on a blob or text field.
QE-DEF-0459 CCLException
RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status = '-253'.
UDS-SQL-0223 A sort is attempted on a blob or text field.
RSV-VAL-0004 Unable to find query information for the item <all_items_used_in_report>

[sunil2244]

Hi,

I also tried to put last expression into another bracket to consider that as single expression, still same error:

substr ([Additional Hosts], instr([Additional Hosts], '.'), instr([Additional Hosts], ',') - instr([Additional Hosts], '.'))

to

substr ([Additional Hosts], instr([Additional Hosts], '.'), (instr([Additional Hosts], ',') - instr([Additional Hosts], '.')))

Tried another sample test on substr function. When ever there is expression in last, that expression is failed in validation. For eg:

substr ([Additional Hosts], instr([Additional Hosts], '.'), 9) - Validation successful

substr ([Additional Hosts], 9, instr([Additional Hosts], ',')) - Validation failed

Error message is same as mentioned above.

[AnalyticsWithJay]

Hi Sunil,

The syntax that I provided works, as I tested it against Oracle. As mentioned in my previous post, you will need to substitute the functions that I used with the MySQL equivalent functions.

Unfortunately I don't have a MySQL server available to me to give you your specific syntax.

Make sure you're testing this in a brand new query to eliminate possible errors from other data items.