SQL Select Script - Replace field value with wildcard search

[nigelapt]

Hi everyone

I just got Qlikview this week.  Apologies if I am asking a question which has already been answered.  I have been searching the discussion board all day, trying various suggestions but have been unable to find a solution to my problem.

I have a script setup to retrieve data from two SQL database tables and join the results into one table.  The tables just contains 5 fields - DEVICE_NAME, INSTALLED, AGENT_VER, ROLE, MGMT_GRP.

The DEVICE_NAME is a fully qualified domain name for servers located in different regions.  In order to be able to use the DEVICE_NAME to make relationships with other tables, I need to strip the domain part of the server name eg

servereurope1.eur.nsroot.net (I need to remove .eur.nsroot.net)

serverusa1.nam.nsroot.net (I need to remove .nam.nsroot.net)

The domain is always the suffix, but the server names vary in length.

This is my script which is successfully connecting and retrieving the data into one table:

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=OperationsManager_CTX;Data Source=SCMOPSWDCSQL2V,2431;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=LDNXXX26;Use Encryption for Data=False;Tag with column collation when possible=False];

SCOM_ServerInventory:
SQL SELECT DisplayName as DEVICE_NAME, AgentInstalled as INSTALLED, AgentVersion as AGENT_VER, 'StoreFront Server Computer Role' as ROLE, 'SCOMNAM12_CTX' as MGMT_GRP
FROM dbo.MTV_ComTrade$Citrix$StoreFront$Server$ComputerRole
ORDER BY DisplayName;

concatenate

SQL SELECT DisplayName as DEVICE_NAME, AgentInstalled AS INSTALLED, AgentVersion AS AGENT_VER, 'Provisioning Services Computer Role' as ROLE, 'SCOMNAM12_CTX' as MGMT_GRP
FROM dbo.MTV_Comtrade$Citrix$ProvisioningServices$Server$ComputerRole
ORDER BY DisplayName;

Any advice would be gratefully to strip the domain part from the server name during the selection.

Thanks

Nigel

[alexdataiq]

Well, you could do it in a preceding Load:

SCOM_ServerInventory:
LOAD
    Left(DEVICE_NAME, Index(DEVICE_NAME, '.') -1) as DEVICE_NAME,
    INSTALLED,
    AGENT_VER,
    ROLE,
    MGMT_GRP;
SQL SELECT DisplayName as DEVICE_NAME, AgentInstalled as INSTALLED, AgentVersion as AGENT_VER, 'StoreFront Server Computer Role' as ROLE, 'SCOMNAM12_CTX' as MGMT_GRP
FROM dbo.MTV_ComTrade$Citrix$StoreFront$Server$ComputerRole
ORDER BY DisplayName;

That will get you the server name and will discard the suffix.

Cheers.

[MarcoWedel · Accepted solution]

Hi,

use a preceding load like this:

LOAD SubField(DEVICE_NAME,'.',1) as DEVICE_NAME, 
          INSTALLED, 
          AGENT_VER,
          ROLE, 
            MGMT_GRP;
SQL Select
...
FROM
...

hope this helps

regards

Marco

[nigelapt]

Hi Alejandro, thank you for taking time to reply and providing a solution to my problem.

I am really grateful, as  I spent hours yesterday trying to get this to work.

Thanks again.

Nigel

[nigelapt]

Hi Marco,

Thank you for helping me out with this problem.

I received two perfect solutions within a matter of hours.

Really impressed with the product and the community.

Thanks

Nigel

[MarcoWedel]

Hi Nigel,

I guess that's what this community is all about.

Should be difficult to find a better combination

regards

Marco