[MSSQL] SQL en (misschien) arrays

[YellowOnline]

SQL is niet mijn specialiteit, maar geregeld moet ik scripts schrijven die SQL query's doen. Nu loop ik tegen volgende probleem aan. Gegeven de volgende SQL query (op een MS SCCM 2007 DB overigens):

SQL:

SELECT DISTINCT 
    v_R_System.Netbios_Name0,
    v_R_System.managedBy0,
    v_R_System.User_Name0,
    v_R_System.AD_Site_Name0,
    v_GS_PC_BIOS.SerialNumber0,
    v_GS_COMPUTER_SYSTEM.Model0,
    v_RA_System_IPAddresses.IP_Addresses0,
    v_RA_System_SystemOUName.System_OU_Name0

FROM 
    v_R_System 
    INNER JOIN 
    v_RA_System_SystemOUName ON v_RA_System_SystemOUName.ResourceId = v_R_System.ResourceId 
    INNER JOIN 
    v_GS_PC_BIOS ON v_GS_PC_BIOS.ResourceID = v_R_System.ResourceId 
    INNER JOIN 
    v_GS_COMPUTER_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceId 
    INNER JOIN 
    v_RA_System_IPAddresses ON v_RA_System_IPAddresses.ResourceID = v_R_System.ResourceID 

WHERE 
    -- Dit is een placeholder voor een variabele in het script waarin dit gebruikt zal worden
    v_GS_PC_BIOS.SerialNumber0 LIKE '%'
    AND
    -- Dit is om de IPv6 adressen er uit te filteren
    v_RA_System_IPAddresses.IP_Addresses0 NOT LIKE '%:%'

ORDER BY 
    v_R_System.Netbios_Name0

Mijn probleem zit 'm in v_RA_System_SystemOUName.System_OU_Name0: die geeft niet de OU van het object weer maar de hele tree. Voor een gegeven machine X krijg ik het volgende terug (fictieve gegevens):

code:

CONTOSO.COM/_ACME
CONTOSO.COM/_ACME/SALES
CONTOSO.COM/_ACME/SALES/COMPUTERS
CONTOSO.COM/_ACME/SALES/COMPUTERS/DESKTOPS
CONTOSO.COM/_ACME/SALES/COMPUTERS/DESKTOPS/STANDARD

Ik wil enkel de laatste (of eigenlijk de 2e) terugkrijgen omdat ik het departement uit die string moet halen (in casu: SALES). Dat is geen probleem als ik maar één van de 5 resultaten terug zou krijgen (behalve de 1e).

Als dit een array zou zijn in .NET, dan zou ik gewoon v_RA_System_SystemOUName.System_OU_Name0[1] doen om de juiste informatie er uit te vissen. Hoe dit in SQL kan weet ik evenwel niet - ook Google is weinig behulpzaam omdat ik het probleem moeilijk in een juiste trefwoord kan samenvatten (bij gebrek aan kennis van SQL), een probleem dat zich ook stelde bij het kiezen van een titel van dit topic

Dit is ongetwijfeld een eitje voor de SAs hier. Ik dank jullie alvats om mij op het juiste spoor te zetten

Edit: In principe kan ik wel een oplossing vinden in mijn code, maar dat zou niet zo netjes zijn én qua performance ook een slechte keuze: ik trek met deze query immers 5x dezelfde data binnen. Te vermenigvuldigen met pakweg 25 000 en dat in een ForEach loop met nog eens zoveel data van een andere bron.

[ Voor 6% gewijzigd door YellowOnline op 10-04-2013 13:21 ]

[Gomez12]

Bekijk de v_RA_System_SystemOUName tabel eens, ik vermoed dat er simpelweg een veldje daarin staat met bijv niveau van de tree oid. Zodat je een where v_RA_System_SystemOUName.treelevel=2 oid kan toevoegen.

In theorie kan je ook in je rapporting engine vast wel aangeven dat je het 2e resultaat wilt hebben maar persoonlijk zou ik gewoon een where toevoegen.

[YellowOnline]

Ik zie zoiets toch niet staan:

[kutagh]

Hoe efficiënt dit is, weet ik niet (geen SA), maar dit zou kunnen:

SQL:

SELECT * FROM (SELECT * FROM Table1 ORDER BY LENGTH(System_OU_Name0) DESC) as temp GROUP BY ResourceID;

Zie ook http://sqlfiddle.com/#!2/ffdad/6/0

[YellowOnline]

kutagh schreef op woensdag 10 april 2013 @ 17:08:
Hoe efficiënt dit is, weet ik niet (geen SA), maar dit zou kunnen:

SQL:

SELECT * FROM (SELECT * FROM Table1 ORDER BY LENGTH(System_OU_Name0) DESC) as temp GROUP BY ResourceID;

Zie ook http://sqlfiddle.com/#!2/ffdad/6/0

Ben nu thuis dus heb de DB niet bij, maar ik heb iets in die trant geprobeerd. Een SELECT statement eromheen en een nieuwe kolom aangemaakt met LEN van de lengte van de verschillende OUs.

Het idee was dan om een MAX te vragen in de nieuwe WHERE clause van die nieuwe kolom, maar dan kreeg ik verschillende foutmeldingen naargelang wat ik probeerde (hetzij dat de kolom niet bestaat, hetzij een error ivm. een sub-clause). Ik kan morgen de nieuwe code posten met foutmelding.

't Verwondert me dat er zo weinig reactie op deze thread is - ik dacht dat dit een voor de hand liggend ding zou zijn voor mensen die veel met SQL werken. Blijkbaar is dat niet het geval

[JaQ]

YellowOnline schreef op woensdag 10 april 2013 @ 21:10:
't Verwondert me dat er zo weinig reactie op deze thread is - ik dacht dat dit een voor de hand liggend ding zou zijn voor mensen die veel met SQL werken. Blijkbaar is dat niet het geval

In Oracle zou ik dit doen met

SQL:

SUBSTR(column, INSTR(column, '/', -1) 

Maar SQL-Server heeft geen instr. Wat je wel kan doen in sqlserver (heb ik net geleerd) is iets vunzigs als

SQL:

REVERSE( SUBSTR( REVERSE(column), 1, CHARINDEX( REVERSE(column),'/') ) )

Althans, dat denk ik (heb niet eens een Windows machine om het op te testen ). Heb je hier iets aan?

[Verwijderd]

Dit mag dan voor MySQL zijn maar ik denk dat het redelijk makkelijk te herschrijven is voor MSSQL:

http://www.hashbangcode.c...ers-string-mysql-511.html

Het idee op zich zou moeten werken.

[YellowOnline]

JaQ schreef op woensdag 10 april 2013 @ 21:32:
[...]

In Oracle zou ik dit doen met

SQL:

SUBSTR(column, INSTR(column, '/', -1) 

Maar SQL-Server heeft geen instr. Wat je wel kan doen in sqlserver (heb ik net geleerd) is iets vunzigs als

SQL:

REVERSE( SUBSTR( REVERSE(column), 1, CHARINDEX( REVERSE(column),'/') ) )

Althans, dat denk ik (heb niet eens een Windows machine om het op te testen ). Heb je hier iets aan?

Dat ziet er inderdaad vunzig uit

Verwijderd schreef op woensdag 10 april 2013 @ 22:16:
Dit mag dan voor MySQL zijn maar ik denk dat het redelijk makkelijk te herschrijven is voor MSSQL:

http://www.hashbangcode.c...ers-string-mysql-511.html

Het idee op zich zou moeten werken.

Dat is de piste waar ik gisteren op zat. Onderstaande code werkt (maar doet nog niet wat moet)):

SQL:

SELECT 
    * 

FROM    
    (
    SELECT DISTINCT 
    v_R_System.Netbios_Name0,
    v_R_System.managedBy0,
    v_R_System.User_Name0,
    v_R_System.AD_Site_Name0,
    v_GS_PC_BIOS.SerialNumber0,
    v_GS_COMPUTER_SYSTEM.Model0,
    v_RA_System_IPAddresses.IP_Addresses0,
    v_RA_System_SystemOUName.System_OU_Name0,
    LEN(v_RA_System_SystemOUName.System_OU_Name0)As OULength

    FROM 
        v_R_System 
        INNER JOIN 
        v_RA_System_SystemOUName ON v_RA_System_SystemOUName.ResourceId = v_R_System.ResourceId 
        INNER JOIN 
        v_GS_PC_BIOS ON v_GS_PC_BIOS.ResourceID = v_R_System.ResourceId 
        INNER JOIN 
        v_GS_COMPUTER_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceId 
        INNER JOIN 
        v_RA_System_IPAddresses ON v_RA_System_IPAddresses.ResourceID = v_R_System.ResourceID 

    WHERE 
        -- Placeholder for a script variable
        v_GS_PC_BIOS.SerialNumber0 LIKE '%'
        AND
        -- Filtering IPv6 addresses
        v_RA_System_IPAddresses.IP_Addresses0 NOT LIKE '%:%'
    ) t_Dataset

WHERE
    t_Dataset.OULength LIKE '50'

Alleen, de laatste WHERE clause is een dummy nu. In die clause wil ik specifiëren dat ik enkel de langste string wil. Nu ben ik met MAX() aan het experimenteren om dat voor elkaar te krijgen.

[Gomez12]

YellowOnline schreef op donderdag 11 april 2013 @ 10:21:
[...]
Alleen, de laatste WHERE clause is een dummy nu. In die clause wil ik specifiëren dat ik enkel de langste string wil. Nu ben ik met MAX() aan het experimenteren om dat voor elkaar te krijgen.

Let er wel op date SQL MAX de maximum waarde uit de set teruggeeft, dus je zal die over de stringlengtes van de kolom moeten doen.

[YellowOnline]

Gomez12 schreef op donderdag 11 april 2013 @ 11:05:
[...]

Let er wel op date SQL MAX de maximum waarde uit de set teruggeeft, dus je zal die over de stringlengtes van de kolom moeten doen.

Tot die vaststelling was ik intussen helaas ook gekomen

Ik heb nu toch een oplossing gevonden, al is ze weinig elegant. De volgende ANDs in de WHERE clause doen hun werk:

SQL:

AND LEN(v_RA_System_SystemOUName.System_OU_Name0) > 27
AND LEN(v_RA_System_SystemOUName.System_OU_Name0) < 33

Ik kan dit doen aangezien de OU die ik zoek in werkelijkheid 3 of 4 letters is en niet langer. Echt netjes vind ik het niet maar 't is het beste dat ik kan bedenken. In mijn productiecode zal het overigens niet zo hard-coded zijn als het hier is, aangezien ik dat ga vervangen met variabelen uit mijn script, dwz.de eerste AND wordt > $((FQDN/OU).Length) en de tweede AND $(((FQDN/OU).Length) + 5). Enkel die 5 zla dus hard-coded zijn.

[jlrensen]

In MSSQL 2007 kun je ook de RANK() of ROW_NUMBER functie gebruiken. Ik heb hier geen SQL bij de hand, maar het zou iets van

SQL:

and ROW_NUMBER() OVER (ORDER BY len(v_RA_System_SystemOUName.System_OU_Name0) ASC) = 2

In je where-statement kunnen zijn

meer info: MSDN: RANK (Transact-SQL)

[ Voor 16% gewijzigd door jlrensen op 12-04-2013 00:40 ]

[YellowOnline]

Dank voor alle reacties. De uiteindelijke code is dit geworden:

SQL:

SELECT DISTINCT 
    v_R_System.Netbios_Name0,
    v_R_System.managedBy0,
    v_R_System.User_Name0,
    v_R_System.AD_Site_Name0,
    v_GS_PC_BIOS.SerialNumber0,
    v_GS_COMPUTER_SYSTEM.Model0,
    v_RA_System_SystemOUName.System_OU_Name0

FROM 
    v_GS_PC_BIOS
    FULL OUTER JOIN 
    v_RA_System_SystemOUName ON v_RA_System_SystemOUName.ResourceId = v_GS_PC_BIOS.ResourceId 
    FULL OUTER JOIN
    v_R_System ON v_R_System.ResourceID = v_GS_PC_BIOS.ResourceId 
    FULL OUTER JOIN 
    v_GS_COMPUTER_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_PC_BIOS.ResourceId 
    
WHERE 
    v_GS_PC_BIOS.SerialNumber0 LIKE @SystemBarcode
    AND (
        LEN(v_RA_System_SystemOUName.System_OU_Name0) > @LowerLimit
        AND 
        LEN(v_RA_System_SystemOUName.System_OU_Name0) < @UpperLimit
        OR 
        v_RA_System_SystemOUName.System_OU_Name0 IS Null
        )

ORDER BY 
    v_GS_PC_BIOS.SerialNumber0

De voornaamste wijzigingen nog zijn omdat ik wat data ontbrak ^^ Even context (op het einde van de thread): ik match bestelbonnen (met serienummer) met de SCCM database. Niet alle machines hebben hun hardware inventory al gedaan of zijn al in gebruik genomen. Om die reden moest ik nog FULL OUTER JOINs gebruiken om ook de machines terug te krijgen waar er geen relaties voor bestonden in de andere tables. Daarnaast is er nog een OR toegevoegd voor wanneer de OU Null is - dit is het geval wanneer de hardware inventory nog niet voltooid is. Daar heb ik trouwens nog een grap mee voorgehad, maar dat heb ik in YellowOnline in "[alg] Slechtste programmeervoorbeelden deel 4" gepost

---

Overigens: geen idee hoe SQL normaal identet wordt, maar dit leek mij het handigst.

[ Voor 31% gewijzigd door YellowOnline op 12-04-2013 09:45 ]

[De_Bastaard]

Wat krijg je nu voor restultaat dan als ik vragen mag? Alles na Sales\ of inclusief Sales zonder de contoso.com ?

[YellowOnline]

De_Bastaard schreef op vrijdag 12 april 2013 @ 09:36:
Wat krijg je nu voor restultaat dan als ik vragen mag? Alles na Sales\ of inclusief Sales zonder de contoso.com ?

(met fictieve gegevens)

code:

CONTOSO.COM/_ACME/SALES

Daar maak ik SALES van in mijn script met

PowerShell:

PS X:\>$SCCMData.System_OU_Name0
CONTOSO.COM/_ACME/SALES
PS X:\>(($SCCMData.System_OU_Name0).Split("/"))[-1]
SALES

[ Voor 9% gewijzigd door YellowOnline op 12-04-2013 09:41 ]