Stopping Duplication Of VM Names and merging them.

I have a SWQL script below that looks at containers and VMs and then groups them.The script is below

SELECT n.DisplayName AS [Node Name]
,CASE
WHEN m.containerID = '218' Then 'DBA'
WHEN m.containerID = '217' Then 'BICC'
WHEN m.containerID = '219' Then 'Finance'
WHEN m.containerID = '220' Then 'Finance Payroll'
WHEN m.containerID = '222' Then 'Networks Team'
WHEN m.containerID = '221' Then 'Infrastructure'
WHEN m.containerID = '223' Then 'Office Prod'
WHEN m.containerID = '224' Then 'Security'
WHEN m.containerID = '225' Then 'Service Desk'
END as [VM Service Owner]
,Case
WHEN m.containerID = '228' Then 'UKBVB'
WHEN m.containerID = '229' Then 'USARI'
WHEN m.containerID = '230' Then 'UAEBS'
End as [Org Owner]
FROM Orion.VIM.VirtualMachines AS n
LEFT JOIN Orion.ContainerMembers AS m ON m.name=n.DisplayName AND MemberEntityType='Orion.VIM.VirtualMachines'

The output is showing the details of what each VM is in what group by th heading however the VMs are duplicating.

So an example of what i want it to be is

vm	Service Owner	Org Owner
uk-lon-456	Service Desk	ukbvb
us-dal-123		usari
us-tex-345	BICC

However the output is showing something like this

VM	Service Owner	Org Owner
uk-lon-456	Service Desk
uk-lon-456		ukbvb
us-dal-123		usari

How do i merge VM names so they do not double/triple up the names?

Find more posts tagged with

Reports

swql report

SWQL

Accepted answers

mesverrum

This might get you closer i think

SELECT distinct n.DisplayName AS [Node Name]
, so.[VM Service Owner]
, oo.[Org Owner]
FROM Orion.VIM.VirtualMachines AS n
LEFT JOIN (
SELECT Distinct name
,CASE
WHEN m.containerID = '218' Then 'DBA'
WHEN m.containerID = '217' Then 'BICC'
WHEN m.containerID = '219' Then 'Finance'
WHEN m.containerID = '220' Then 'Finance Payroll'
WHEN m.containerID = '222' Then 'Networks Team'
WHEN m.containerID = '221' Then 'Infrastructure'
WHEN m.containerID = '223' Then 'Office Prod'
WHEN m.containerID = '224' Then 'Security'
WHEN m.containerID = '225' Then 'Service Desk'
END as [VM Service Owner]
from Orion.ContainerMembers AS m
where MemberEntityType='Orion.VIM.VirtualMachines'
and m.containerid in (217, 218, 219, 220, 221, 222, 223, 224, 225)
) so on so.name = n.displayname
left join (
select distinct name
,Case
WHEN m.containerID = '228' Then 'UKBVB'
WHEN m.containerID = '229' Then 'USARI'
WHEN m.containerID = '230' Then 'UAEBS'
End as [Org Owner]

from Orion.ContainerMembers AS m
where MemberEntityType='Orion.VIM.VirtualMachines'
and m.containerid in (228, 229, 230)
) oo on oo.name = n.displayname
where (
isnull(oo.name,'') not like ''
or isnull(so.name,'') not like ''
)

All comments

mesverrum

So your case conditions are keyed into two different situations. the uk-lon vm exists as separate lines in the containermembers table in the db because it is in two separate groups, 225 and 228. A single entry seems like it probably wouldnt be in both groups, so in SQL terms this is not a duplicate since the same containerid db row won't be in both containers at the same time. You probably need to do something more like the below (although im winging this because i dont want to get on vpn to test it for real). I'm imagining something along the lines of

select n.displayname
, so.[VM Service Owner]
, oo.[Org Owner]
from Orion.VIM.VirtualMachines as n
left join (
select distinct displayname
,CASE
WHEN m.containerID = '218' Then 'DBA'
WHEN m.containerID = '217' Then 'BICC'
WHEN m.containerID = '219' Then 'Finance'
WHEN m.containerID = '220' Then 'Finance Payroll'
WHEN m.containerID = '222' Then 'Networks Team'
WHEN m.containerID = '221' Then 'Infrastructure'
WHEN m.containerID = '223' Then 'Office Prod'
WHEN m.containerID = '224' Then 'Security'
WHEN m.containerID = '225' Then 'Service Desk'
END as [VM Service Owner]

from Orion.ContainerMembers AS m
where MemberEntityType='Orion.VIM.VirtualMachines'
) so on so.name = n.displayname

left join (
select distinct displayname
, Case
WHEN m.containerID = '228' Then 'UKBVB'
WHEN m.containerID = '229' Then 'USARI'
WHEN m.containerID = '230' Then 'UAEBS'
End as [Org Owner]en 'Service Desk'
END as [VM Service Owner]

from Orion.ContainerMembers AS m
where MemberEntityType='Orion.VIM.VirtualMachines'
) oo on oo.name = n.displayname

where oo.name is not null or so.name is not null

adam.beedell

Vague answers:

-This feels like an odd way to approach what looks like a custom properties lookin' problem, there's probably an easier way
-Either Joins or GROUP BY

nwbarwell

So the groups are being pulled from VMWare via custom properties

however the custom properties are set via true/false or 1/0 and not by text (Something yet to resolved by Solarwinds)

So as a work around I am moving them as a dynamic query into groups - i am looking into reporting these categories and confirming which VMs are tagged and not!

nwbarwell

Very helpful - I will have a play with this and get back to you! I feel this has sent me on the correct path - thank you!!

adam.beedell

When you say Tagged, are you referring to vmware tags?

nwbarwell

So I have done some playing an i am getting closer thanks to this

If i remove the following it runs

End as [Org Owner]en 'Service Desk'
END as [VM Service Owner]

it does link the two together but it is now adding the same node names multiple times and blank as well.

so the full code is this

SELECT n.DisplayName AS [Node Name]
, so.[VM Service Owner]
, oo.[Org Owner]
FROM Orion.VIM.VirtualMachines AS n
LEFT JOIN (
SELECT Distinct name
,CASE
WHEN m.containerID = '218' Then 'DBA'
WHEN m.containerID = '217' Then 'BICC'
WHEN m.containerID = '219' Then 'Finance'
WHEN m.containerID = '220' Then 'Finance Payroll'
WHEN m.containerID = '222' Then 'Networks Team'
WHEN m.containerID = '221' Then 'Infrastructure'
WHEN m.containerID = '223' Then 'Office Prod'
WHEN m.containerID = '224' Then 'Security'
WHEN m.containerID = '225' Then 'Service Desk'
END as [VM Service Owner]
from Orion.ContainerMembers AS m
where MemberEntityType='Orion.VIM.VirtualMachines'
) so on so.name = n.displayname
left join (
select distinct name
,Case
WHEN m.containerID = '228' Then 'UKBVB'
WHEN m.containerID = '229' Then 'USARI'
WHEN m.containerID = '230' Then 'UAEBS'
End as [Org Owner]

from Orion.ContainerMembers AS m
where MemberEntityType='Orion.VIM.VirtualMachines'
) oo on oo.name = n.displayname
where oo.name is not null or so.name is not null

and the output looks like this at the moment

VM Name	Service Owner	Org Owner
uk-lon-456	Service Desk
uk-lon-456
uk-lon-456	Service Desk	UKBVB
uk-lon-456		UKBVB

We are close - if you got any reason why - let me know! all help is brilliant

nwbarwell

Yes! The guide states they haven't updated the tagging crossover to make it include of all the categories

So if you have a location category on VMWare it will export to SolarWinds are not a text custom properties but a True false for each individual option!

So until they resolve that i am looking at a work around

mesverrum

This might get you closer i think

nwbarwell

That has worked!!!! - Thank you so much - perfect