Grouping Expression

[Aremac]

I'm having trouble understanding the apparant limitations of grouping expressions. For example, I use grouping expression to parse a VM name to Identify its role. I wanted to expand this to have it group by role and site. The below is not my exact expression but is an example of what im trying to do and what isn't working..

Example VM name 1 OHsrvfs1
Example VM name 2 OHsrvfs2
Example VM name 3 OHsrvdc1
Example VM name 4 MIsrvfs1
Example VM name 5 MIsrvdc1


Example expression
Case
When substring(name,5,2) = "fs" THEN "File Server"
When substring(name,5,2) = "dc" THEN "Domain Controller"
Else "Other"
End

This works well to group all my file servers and DC but it does not group by site. So I tried to expand on this in what I thought would be an easy manner by doing the following:

Case
When substring(name,5,2) = "fs" THEN "File Server" + left(name,2)
When substring(name,5,2) = "dc" THEN "Domain Controller + left(name,2)"
Else "Other"
End

I expected the expression result from this to be:

Fileserver - OH
Fileserver - MI
Domain Controller - OH
Domain Controller - MI

However it reported an error. I removed the '+' and enclosed everthing in quotes Ex. "Domain Controller left(name,2)" the expression result I get for this was:
OH or MI
I dont know why it didn't join the two strings or how to join them, or why the "Domain Controller" portion was just ignored. I got a little closer by doing the below:

Case
When substring(name,5,2) = "fs" THEN "File Server" - "
When substring(name,5,2) = "dc" THEN "Domain Controller - "
Else "Other"
End
left(name,2) <-- This apparently appends to the end of the expression results which is what I was trying to do above on each 'when' line.

This gave me 'mostly what I want'

Fileserver - OH
Fileserver - MI
Domain Controller - OH
Domain Controller - MI

But causes problems with the Else "Other" statement which is intened to be a catchall for servers that haven't been named to convention. I will get results like

Otherla, OtherEx (or whatever the first two char of each non-conforming server name is as opposed to just "Other" containing all non-conforming servernames.)

Suggestions?

[Aremac]

I figured it out, Kinda. to get what I wanted I did the following:

Case
When left(name,2) = "Oh" THEN "Ohio - "
When left(name,2) = "MI" THEN "Michigan - "
End

Case
When substring(name,5,2) = "fs" THEN "File Servers"
When substring(name,5,2) = "dc" THEN "Domain Controllers"
Else "Other"
End

This gives me:

Ohio - File Servers
Ohio - Domain Controllers
Michigan - File Servers
Michigan - Domain Controllers
Other

[Vitaliy S.]

Sam, thank you very much for sharing your grouping expression, great job!

[Hulabaloo]

I've not been successful with this way of building the grouping expression. The result if I make my ELSE "Other" is that everything gets grouped into "Other".

Here is my statement. I'm very new to this platform and how to build the expressions so please excuse my noob question.

Example Object - CANNWPAPPMTR01A

Case
When substring(name,5,2) = "WP" THEN "Production - "
End

Case
When substring(name,10,3) = "MTR" THEN "Microstrategy"
When sugstring(name,10,3) = "BPM" THEN "MetaStorm"
Else "Other"
End

[Hulabaloo]

Figured it out. In the method SubString, you have to place your startindex and then your length are not your same number.

For example, my server name has about 15 characters. I wanted to pull the 3 character prefix we use to name the application. I.E. MTR = MicroStrategy. The StartIndex should have been set at 9 and not 10 because the next letter is the start of the length.

CANNWPAPPMTR01A where the 3rd "P" was the StartIndex and Length would cover "MTR".

Hope that makes sense. It took me awhile to figure this out. I'm not a programmer by trade so anything like this was a huge deal for me.

Substring("value", startIndex, length)

[Vitaliy S.]

Hi Matthew, thank you for sharing your grouping expression, glad that you've figured everything out!