April 21, 2013 at 4:19 pm
So i need to query Active directory and get all the computer based on certian OS type, no problem doing this easy, the hard part is I need to extract the serial number out of the info string.
This this is what I have started but dont know how to get the rest, I used a charindex to get tot he Sn part but it starts at the semicolon and not the equals part.
Create table #TempAD (cn varchar(max), info varchar(max), os varchar(max))
Insert #AD (cn, info, os)
Select cn, info, operatingSystem from OPENQUERY(ADSI, 'Select cn, info, operatingSystem
from ''LDAP://myldap''') where INFO is not null
Select cn, info, CharIndex(';SN=', info), os from #AD
Here is what the typical data looks like for info, what i need is the SN for each so in these cases
554FSL5
3TFKDQ4
664JDL2
Sys=Dell Inc.|OptiPlex 760;SN=554FSL5;OS=Ver:6.1.7601,SP:1,Type:1;Form=Desktop;
Sys=Dell Inc.|Latitude E6410;SN=3TFKDQ4;OS=Ver:6.1.7601,SP:1,Type:1;Form=Laptop;
Sys=Dell Inc.|OptiPlex 760;SN=664JDL2;OS=Ver:6.1.7601,SP:1,Type:1;Form=Desktop;
Thank you for the help
April 21, 2013 at 6:10 pm
This might be what you need.
SELECT CHARINDEX('SN',os,1) AS 'Starting position'
,CHARINDEX(';',os,CHARINDEX('SN',os,1)) AS 'Ending position'
,SUBSTRING(os,CHARINDEX('SN',os,1)+ 3 ,7) FROM #TempAD
Results:
Starting positionEnding position (No column name)
28 38 554FSL5
30 40 3TFKDQ4
28 38 664JDL2
April 22, 2013 at 2:55 am
Just a minor point, but for a general solution my inclination would be to replace "SN" with "SN=" in the charindex. It's not beyond the bounds of possibility that a system type could have an "sn" in it (although I can't actually think of any off-hand).
April 22, 2013 at 6:11 am
Hi,
Thanks for the reply it works good except its being limited on the SN amount of characters, is there a way to go to the next semicolon and get the full Sn as I have some computers such as Mac's that have longer Sn's. All have in common that the end of the SN is a semicolon.
Sys=Apple Inc.|iMac11,1;SN=QP0261H75RU;OS=Ver:6.1.7601,SP:1,Type:1;Form=Desktop;
Sys=Apple Inc.|iMac10,1;SN=W89437LZ5PE;OS=Ver:6.1.7601,SP:1,Type:1;Form=Desktop;
Thanks you for the help.
April 22, 2013 at 6:24 am
I'm sure there's a neater way, but this should work:
SELECT CHARINDEX('SN',os,1) AS 'Starting position'
,CHARINDEX(';',os,CHARINDEX('SN',os,1)) AS 'Ending position'
,SUBSTRING(os,CHARINDEX('SN',os,1)+ 3 ,CHARINDEX(';',os,CHARINDEX('SN',os,1))-CHARINDEX('SN',os,1)-3) FROM #TempAD
April 22, 2013 at 6:54 am
Hi,
Thanks for sticking with me and helping, i am getting the following error.
Msg 537, Level 16, State 5, Line 8
Invalid length parameter passed to the LEFT or SUBSTRING function.
April 22, 2013 at 7:26 am
Not sure what's going on there - it works a treat for me.
Here's the full script I'm using to test:
create table #tempad (os varchar(255))
insert #tempad values('Sys=Dell Inc.|OptiPlex 760;SN=554FSL5;OS=Ver:6.1.7601,SP:1,Type:1;Form=Desktop;')
insert #tempad values('Sys=Dell Inc.|Latitude E6410;SN=3TFKDQ4;OS=Ver:6.1.7601,SP:1,Type:1;Form=Laptop;')
insert #tempad values('Sys=Dell Inc.|OptiPlex 760;SN=664JDL2;OS=Ver:6.1.7601,SP:1,Type:1;Form=Desktop;')
insert #tempad values('Sys=Apple Inc.|iMac11,1;SN=QP0261H75RU;OS=Ver:6.1.7601,SP:1,Type:1;Form=Desktop;')
insert #tempad values('Sys=Apple Inc.|iMac10,1;SN=W89437LZ5PE;OS=Ver:6.1.7601,SP:1,Type:1;Form=Desktop;')
SELECT CHARINDEX('SN',os,1) AS 'Starting position'
,CHARINDEX(';',os,CHARINDEX('SN',os,1)) AS 'Ending position'
,SUBSTRING(os,CHARINDEX('SN',os,1)+ 3 ,CHARINDEX(';',os,CHARINDEX('SN',os,1))-CHARINDEX('SN',os,1)-3) FROM #TempAD
drop table #tempad
Results:
Starting positionEnding position(No column name)
2838554FSL5
30403TFKDQ4
2838664JDL2
2539QP0261H75RU
2539W89437LZ5PE
If you're running on a bigger dataset, I guess there could be lines in there with unexpected formatting that are throwing it.
April 22, 2013 at 8:47 am
gentlemen,
all of you rock, thank you very much fro the help.
April 22, 2013 at 8:48 am
Actually i should say Andrew you rock, thank you
April 22, 2013 at 8:49 am
No - Ron did the main work; I just tidied up a bit - the credit is due to him.
April 25, 2013 at 12:03 am
Late to the party it seems but I think what you need is a good delimited string splitter like DelimitedSplit8K[/url] in the linked article.
WITH ComputerRecords (cn) AS (
SELECT 'Sys=Apple Inc.|iMac11,1;SN=QP0261H75RU;OS=Ver:6.1.7601,SP:1,Type:1;Form=Desktop;'
UNION ALL SELECT 'Sys=Apple Inc.|iMac10,1;SN=W89437LZ5PE;OS=Ver:6.1.7601,SP:1,Type:1;Form=Desktop;'
)
SELECT SN=REPLACE(Item, 'SN=', '')
FROM ComputerRecords
CROSS APPLY DelimitedSplit8K(cn, ';')
WHERE CHARINDEX('SN=', Item) > 0
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply