SQL Query op de CMDB probleem

[markverhoef]

Wij hebben hier op de zaak een CMDB waar het niet echt makkelijk is om een goed overzichtje te krijgen van alle notebooks met de mac-adressen (WiFi / LAN) zichtbaar te krijgen dus hebben ze mij gevraagd om het via een SQL Query te doen.

Dit heb ik al volgt gedaan:

SELECT cmdb_ci_ext_id External_CI, Description, Status, content Mac_adres, LEN(content) Lengte_mac, it_location_desc Locatie
FROM it_cmdb_ci
INNER JOIN it_cmdb_ci_attr ON it_cmdb_ci.cmdb_ci_id = it_cmdb_ci_attr.cmdb_ci_id
INNER JOIN account_location ON it_cmdb_ci.acct_location_id = account_location.acct_location_id
WHERE cmdb_ci_ext_id LIKE 'Laptop%'
AND content LIKE '%:%'
ORDER BY cmdb_ci_ext_id

Helaas is er geen apart veld voor mac-adres en is dit gewoon in content geplaatst, nu heb ik dit weten te filteren omdat we alle macadressen met : notatie doen. Ik krijg nu het volgende resultaat:


Nu heb ik even nagekeken en het resultaat is goed, de eerste regel is de LAN MAC en de tweede is de WiFi MAC alleen nu wil de opdrachtgever dit in losse kolommen ipv rijen zodat je maar 1 rij per laptop krijgt met beide macadressen. Is dat nog mogelijk zonder de database aan te passen ?

Ik hoop dat het duidelijk is mocht dit niet zo zijn vraag vooral wat je wil weten!

Alvast bedankt

[lier]

De database hoef je in ieder geval niet aan te passen

Je zou iets moeten kunnen doen met een group by en tijdelijke tabellen (waarbij één gevuld worden op basis van LAN MAC en de ander op basis van WiFi MAC). Vergeet de (left) join niet en je bent er!

[Russel88]

Tabel waar content in voorkomt 2 keer joinen.
In je voorbeel kan ik niet zien in welke tabel welke info staat.
Maar waarschijnlijk wordt het zoiets:

SQL:

Select A.LaptopNaam, IsNull(B.Value, '') as LAN,  IsNull(B.Value, '') as WIFI
From A
Left Outer Join B on a.ID = b.ParentID And b.Description = 'LAN'
Left Outer Join B on a.ID = b.ParentID And b.Description = 'WIFI'

Let op dat dit een dure SQL kan zijn, doordat je een tabel 2 keer joined.

[markverhoef]

Bedankt, ik kom er alleen nog niet helemaal uit.

@russel88
De tabel waat de mac-adressen in staan heet "content" deze zit in it_cmdb_ci, het probleem is dat er ook andere data in de content is gedropt daarom filter ik op het dubbelepunt om alleen de mac-adressen over te houden

code:

AND content LIKE '%:%'

Ook de laptopnaam staat in: it_cmdb_ci

[Russel88]

Je moet kijken hoe je het onderscheid kan maken tussen WIFI en LAN, vandaar dat ik in regel 3 en regel 4 dat op Description doe. Dat is in jou geval natuurlijk iets anders/complexer.

[Larko]

markverhoef schreef op donderdag 17 januari 2013 @ 15:07:
Bedankt, ik kom er alleen nog niet helemaal uit.

@russel88
De tabel waat de mac-adressen in staan heet "content" deze zit in it_cmdb_ci, het probleem is dat er ook andere data in de content is gedropt daarom filter ik op het dubbelepunt om alleen de mac-adressen over te houden

code:

AND content LIKE '%:%'

Ook de laptopnaam staat in: it_cmdb_ci

Kun je wellicht aangeven welke velden uit welke tabel komen?
Of een overzicht van welke data in welke tabel staat, dus gewoon een regel per tabel te tonen? Dat zou 't makkelijker maken voor ons om een query voor je te schrijven.
Alvast bedankt.

[telefoontoestel]

Je zou er een dubbele regex op los kunnen laten dmv de ~ operator.

Wel een vreemd datamodel voor een dergelijke cmdb om de mac adressen geen apart veldje te geven. Bijna alle apparatuur heeft wel een dergelijke identificatie.

[ Voor 55% gewijzigd door telefoontoestel op 17-01-2013 18:48 ]

[markvt]

Watvoor database systeem?

Staan ze er altijd 2x in, dan kan je met een join de data samenvoegen.

Je zou ook alleen een van de 2 op kunnen halen, dit in een tijdelijke tabel opslaan en daarna het tweede mac door middel van een subquery eraan toevoegen.

[RobIII]

Russel88 schreef op donderdag 17 januari 2013 @ 14:25:

SQL:

Select A.LaptopNaam, IsNull(B.Value, '') as LAN,  IsNull(B.Value, '') as WIFI
From A
Left Outer Join B on a.ID = b.ParentID And b.Description = 'LAN'
Left Outer Join B on a.ID = b.ParentID And b.Description = 'WIFI'

Bedoel je op regel 1 en 4 niet C (en c. ) ?

Ik zie overigens niet waar die IsNull(x, '') voor dient; wat is er mis met gewoon null returnen? Null staat voor 'onbekend' / 'ongedefinieerd'. Een lege string geeft, voor mij, aan dat iemand heeft ingevuld dat er géén waarde is (en dus bekend).

SQL:

Select A.LaptopNaam, IsNull(B.Value, '') as LAN,  IsNull(c.Value, '') as WIFI
From A
Left Outer Join B on a.ID = b.ParentID And b.Description = 'LAN'
Left Outer Join C on a.ID = c.ParentID And c.Description = 'WIFI'

Russel88 schreef op donderdag 17 januari 2013 @ 14:25:
Let op dat dit een dure SQL kan zijn, doordat je een tabel 2 keer joined.

Nou, poehee, twee hele joins! Dat zegt helemaal geen kont en op basis daarvan kun je dan ook niets zinnigs zeggen. Als je (o.a.) zorgt dat de juiste indices gebruikt (kunnen) worden kost het nagenoeg geen drol doorgaans.

markvt schreef op donderdag 17 januari 2013 @ 20:44:
Je zou ook alleen een van de 2 op kunnen halen, dit in een tijdelijke tabel opslaan en daarna het tweede mac door middel van een subquery eraan toevoegen.

Why? Dat kost je alleen maar overbodige I/O en een subquery is duurder/even duur als een join.

[ Voor 46% gewijzigd door RobIII op 17-01-2013 23:48 ]

[Russel88]

RobIII schreef op donderdag 17 januari 2013 @ 23:43:
[...]

Bedoel je op regel 1 en 4 niet C (en c. ) ?

Ja, een typefout inderdaad.

Ik zie overigens niet waar die IsNull(x, '') voor dient; wat is er mis met gewoon null returnen? Null staat voor 'onbekend' / 'ongedefinieerd'. Een lege string geeft, voor mij, aan dat iemand heeft ingevuld dat er géén waarde is (en dus bekend).

In TS's geval wordt resultaat van de query in een Excel gezet.Een lege string is netter dan NULL in een eindrapport. Aangezien het om een MAC adres gaat, weet je met een lege string al dat de informatie er niet is.

Nou, poehee, twee hele joins! Dat zegt helemaal geen kont en op basis daarvan kun je dan ook niets zinnigs zeggen. Als je (o.a.) zorgt dat de juiste indices gebruikt (kunnen) worden kost het nagenoeg geen drol doorgaans.

Als ik de situatie van TS goe dbeoordeel, kost het iets meer moeite om te bepalen of de info een WIFI of LAN Mac adres is.
Een extra waarschuwing dat je dezelfde data 2 keer filtert leek me wel handig.

[jbdeiman]

Je hebt de data nu in Excel staan, maar hoe zeker is het dat de 2e MAC altijd die van WIFI is en de 1e dus die van de LAN? Heb je bij de export naar Excel al een filter gebruikt op type MAC adres?

Als je deze gegevens op deze volgorde in de database hebt staan, dan is het op zijn zachtst gezegd vreemde (onbetrouwbare) data. Wat nou als 1 van die laptops eerst op WIFI is aangesloten en later pas via LAN (ervan uitgaande dat dit systeem automatisch gevuld wordt)? Wat nou als (bij handmatig vullen) dit andersom is ingevoerd?
Dan kan je niet stellen dat het 2e adres het MAC adres van de WIFI verbinding is. In elk geval klinkt dit als een vreemde zet om van iets onbetrouwbaars iets betrouwbaars te maken.

[ThinkPad]

Kan je niet beter filteren op de lengte van het MAC adres ipv op de dubbele punt? Volgens mij is een MAC adres altijd 17 karakters lang (12 tekens, en 5x een dubbele punt).