Queries on report studio or data metrics

[sunilyadav]

Hi again,

This thread only for reporting queries.

1. Host Name are discovered in mix of shortname or FQDN (physical servers and ESX) when I login to java. A part of my report is pulling host names but customer want only short names. I tried to add new data item and used functions like substring, left, position etc but either it throw error during validation or while running report.

But when I run same function in mysql query it is working fine. For eg, below is giving me results what I need:

select name, if(position('.' in name) > 0, left(name, position('.' in name)-1), name) as new_host from host

Output of that would be: xxx.yyy.zzz   xxx

But if I use same function in cognos report studio, and run it, I get error. Attached is the screenshot. For simplicity I am just using hostname in a report and another data item where I use function, pass hostname as input. Screenshot is 3 in 1, query explorer, new data item expression and result of that query.

2. In 1 of the report I am pulling host IOPS data. I can see the data when I run it in tabular form in query explorer but not in HTML. A point to note here is those host have 0 IOPS as that is test environment. So just want to confirm that is there any kind of validation that 0 IOPS wont show in HTML or excel version of reports. FYI there is no filter to exclude hosts which have 0 IOPS.

3. Storage Utilization reports working well for some devices but not with HDS VSP G series. In OCI server I can see values recorded for IOPS but reports are blank. Again this is test environment so no load on that array but I expect "0" as a value in report rather than showing blank.

[ostiguy]

#1. I am very lame in Cognos, but I believe you cannot use MySQL expressions in the Cognos view you are working with. You can either write a query entirely in SQL, or use Cognos' own string manipulation functions.

#2-3. 0s in the OCI operational server should manifest themselves as 0s in the DWH.Values should show up the same way, regardless of the output format as far as I know

Is the DWH ETL running reliably?

[sunilyadav]

We are using OCI 7.2.3

#1. If you can get me help to do the same within cognos on this specific fuction from internally I would appreciate. On the other note, I thought to build the same by writing SQL query but then I realized the multiple joins which cognos is doing for me in backend. 

I am working in Volume Capacity DM and there is data item "Additional Hosts" in Host Visible Volumes - Host. I am not able to locate that in the schema. Then I realized that my schema pictures are old so I was looking directly into MySQL DB but not able to locate such field in host table.

Now I need assistance in :

            a) In which table I can see that "Additionals Hosts" data?

            b) How do I know that "Additional hosts" shown in cognos is renamed/formulated from some other data, if not directly fetched? Like from which table cognos data items coming. I know there was Framework Manager which I used to create custom packages in older version of OCI, but you guys removed direct link to lauch that in DWH.

           c) I need latest DB schema

#2. Yes, all jobs are working fine without any failure.

[hotz]

Hi sunilyadav,

I needed a bit of experimenting, but if you want to do this in cognos, here's what worked for me:

1. Make sure you have something like the "Host Name" from the Inventory package in your query.

2. Create an additional data item in the query and call it Host.

3. As an expression, you could be usig something like this (please adjust if you're using a diffrent package than inventory according to your needs):

if ([Host Name] contains '.') then (substring([Host Name],1,position('.',[Host Name])-1)) else ([Host Name])

As for the "Additional Hosts" in the Volume Capacity datamart, I've got the feeling it's broken. At least I wasn't able to do anything with it - except producing a cognos error, and I have no Idea what sort of data is supposed to be stored there. Generally speaking, datamarts are a cognos thing and they don't necessarily map 1:1 to the database schema. The database schema you should be able to view in a webbrowser by navigating to https://dwh-server/dwh => Help Icon => Documentation => Database schema - or on thy mysql cli (example): show full columns dwh_inventory.host

I hope this helps.

Cheers,

Gerhard

[sunilyadav]

Thanks hotz.

Given expression is working in other DM too, but another wierd behaviour observed.

I can run the report in HTML and see the expected output in the newly added data item but when I try to save report in excel data/format, it throw below error:

RQP-DEF-0177
An error occurred while performing operation 'sqlPrepareWithOptions' status='-69'.

So just to confirm if that error is related to new change, I removed that new data item then run the report. Report run in HTML and it can be saved in excel data/format.

I compared the properties of original host name and new data item in case data type was different but everything is same except expression.

Attaching the xml.

Regarding "Additional Hosts" item, it is working fine at my end, showing other host names of the same cluster.

[hotz]

Hi sunilyadav,

This is really odd. I was able to reproduce it. For me too, the expression produces an error if I try to run the report in Excel or PDF format.

I'm not sure why this is happening, I suspect it's some sort of cognos bug. I was trying to find a workaround, but so far no luck.

Cheers,

Gerhard

[sunilyadav]

Any luck?

[hotz]

Hi there,

sorry, got a bit distracted by other things, so I haven't given it a ton of thought.

I think the problem with my original approach is somehow the position function. But I wasn't able ti figure out exactly how and why and it's getting late so I won't spend much more time on it right now.

I found a workaround, sort of. Well, it depends on whether or not your domain suffix(es) is/are of a fixed length. If you have only one domain suffix, then this should work. If you have more than one, than chances are it wont work.

So instead of the if statement I tried the following case-when statement:

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

- Please adjust to the length of your domain suffix.

Note that the substring function is still in place and this works perfectly well for me on a database with only a single domain suffix.

Let me know how it goes.

Cheers,

Gerhard

[sunilyadav]

Hey Gerhard,

Thanks for the hint. I tried your expression, working well for host name with small tweak as FQDN in my environment are mix of lower and capital cases and required % in last.

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

But in same report I have another column additional hosts from volume capacity DM which list all other hostnames part of same cluster. Unfortunately number of additional hosts varies from 1 to n (n <10). When I tiried to use same expression for additional host, it only convert fqdn to short for last host name in that cell, leaving remaining host in fqdn form.

For eg:

Actual Data in Additional 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 expression: 

CASE

    WHEN LOWER([Additional Hosts]) like LOWER('%my.domain.name%')

    THEN substring([Additional Hosts],1,char_length([Additional Hosts])-16)

    ELSE [Additional Hosts]

END

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.

[sunilyadav]

So is it not possible to achieve what I am looking for?

[ostiguy]

Hey Sunil,

This thread is very hard to read because you asked 3 questions in the original post. I would recommend trying to ask one question per post, as that makes it much easier to understand if questions have been answered or not

Thanks

Matt

[hotz]

Hi sunilyadav,

in regards of the additional hosts I wasn't able to find a solution yet, sorry.

The problem is that the additional hosts would be a string of comma separeted hosts of varying lenght, such as 'host1.domain.com, host2.domain.com, host3, host4.domain.com', but still just a string.

This would have to be broken down into idividual elements that would allow us to iterate through them and apply some logic.

To that end, I was playing around with macro funtions, specificaly split and csv - but so far nothing I tried really worked.

Which doesn't mean there's no solution. I just haven't found it yet and I'm starting to loose confidence that I ever will. 😞

It would probably be easier to tackle this on SQL level instead of doing it in cognos. Would that be an aproach that could work for you?

Another possibility could be to throw up this question on a forum frequented by cognos specialsts (which I am not, by any means).

Cheers,

Gerhard