SQL Bauces Come on In

NYSpeed Automotive NYSpeed Off Topic
1

I’m trying to get this report correct to run in SCCM. I have very very little SQL experience other than doing some VB.net (similar syntax) in college. Anyway I modified an existing report that was built-in to SCCM to incorporate another column (IP Addresses). When I run the report it gives me a Conversion failed when converting the varchar value ‘XX.XXX.X.XXX’ to data type int., where the Xs are substituted by an actual IP address. Here is the code, what’s in bold and red is what I added.

select @ComputerName = '%' +lower(@ComputerName) + '%' 

 select  distinct v_R_System_Valid.ResourceID, 
 v_R_System_Valid.Netbios_Name0 AS [Computer Name], 
 v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup], 
 v_Site.SiteName as [SMS Site Name], 
 v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 AS [Top Console User], 
 v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], 
 v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level], 
 v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [Serial Number], 
 v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 AS [Asset Tag], 
 v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer], 
 v_GS_COMPUTER_SYSTEM.Model0 AS [Model], 
 v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)], 
 <b>v_Network_DATA_Serialized.IPAddress0 AS [IP Address],</b>
 v_GS_PROCESSOR.MaxClockSpeed0 AS [Processor (GHz)], 
 (Select sum(Size0) from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID ) 
 where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = @CollectionID) As [Disk Space (MB)], 
 (Select sum(FreeSpace0) from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID ) 
 where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = @CollectionID) As [Free Disk Space (MB)] 
 from v_R_System_Valid 
 inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID) 
 left join v_GS_SYSTEM_ENCLOSURE_UNIQUE on (v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System_Valid.ResourceID) 
 inner join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID) 
 inner join v_GS_X86_PC_MEMORY on (v_GS_X86_PC_MEMORY.ResourceID = v_R_System_Valid.ResourceID) 
 inner join v_GS_PROCESSOR on (v_GS_PROCESSOR.ResourceID = v_R_System_Valid.ResourceID) 
 inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID) 
 left join v_Site on (v_FullCollectionMembership.SiteCode = v_Site.SiteCode) 
 inner join v_GS_LOGICAL_DISK on (v_GS_LOGICAL_DISK.ResourceID = v_R_System_Valid.ResourceID) and v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0,1,2) 
 left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID) 
<b> Join v_Network_DATA_Serialized on (v_Network_DATA_Serialized.IPAddress0=v_Network_DATA_Serialized.ResourceID)
 and v_Network_DATA_Serialized.IPAddress0 is not NULL</b>
 Where v_FullCollectionMembership.CollectionID = @CollectionID 
 and (lower(v_R_System_Valid.Netbios_Name0) like @ComputerName or @ComputerName='') 
 and (v_R_System_Valid.Resource_Domain_OR_Workgr0 = @Domain or @Domain='') 
 and (v_Site.SiteName = @SMSSiteName or @SMSSiteName='') 
 and (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = @TopUser or @TopUser = '') 
 and (v_GS_OPERATING_SYSTEM.Caption0 = @OperatingSystem or @OperatingSystem='') 
 and (v_GS_COMPUTER_SYSTEM.Manufacturer0 = @Manufacturer or @Manufacturer = '') 
 and (v_GS_COMPUTER_SYSTEM.Model0=@Model or @Model = '') 
 Order by v_R_System_Valid.Netbios_Name0

If I remove the red items I added it works like a charm, but I need the column to be created in order to display the IP Address.

2

an IP Address is not an integer value, resource ID probably is…hence they cannot be compared in the Join statement.

3

I figured it was something like that…Any suggestions on fixing it?

4

drop table

http://imgs.xkcd.com/comics/exploits_of_a_mom.png

---------- Post added at 02:48 PM ---------- Previous post was at 02:42 PM ----------

Did you try google?

looks like people doing similar stuff

5

Yeah, I got the column to add once but it was from a different table (I think) in which case it showed no data. I took a different report below, and tried to stitch it into the other report.

SELECT DISTINCT SYS.Netbios_Name0, Netcard.Description0, Netcard.Manufacturer0, Netcard.AdapterType0, Netcard.MACAddress0, 	NETW.IPAddress0, NETW.IPSubnet0
FROM v_R_System SYS
JOIN v_GS_NETWORK_ADAPTER Netcard on SYS.ResourceID=Netcard.ResourceID
JOIN v_Network_DATA_Serialized NETW on Netcard.ResourceID=NETW.ResourceID and Netcard.MACAddress0=NETW.MACAddress0
WHERE SYS.Netbios_Name0 LIKE @variable and NETW.IPAddress0 is not NULL
ORDER BY SYS.Netbios_Name0
6

OK, look

FROM v_R_System SYS
JOIN v_GS_NETWORK_ADAPTER Netcard on SYS.ResourceID=Netcard.ResourceID
JOIN v_Network_DATA_Serialized NETW on Netcard.ResourceID=NETW.ResourceID and Netcard.MACAddress0=NETW.MACAddress0

You need to be comparing LIKE values. The join is going to search 2 tables, and spit out data in the rows where the data matches your statement.

An IP Address will never be a ResourceID, they will never be equal. 1 - An IP is a varchar and resourceID is an integer.

The red code above compares a resourceID with a ResourceID

What you really want to do is say, print out IP Address when resourceID=resourceID

So, you’re already selecting the right thing, now you just need to match the right thing.

7

It’s going to come down to what .IPAddress0 is defined as compared to .ResourceID. Use cast to make them the same type and it should work. This is assuming that .IPAddress0 in the first table is the same data as .ResourceID in the second table. If they’re different data they will never be equal and this query is just written completely wrong.

http://msdn.microsoft.com/en-us/library/ms187928.aspx

8

UMMM OK WAIT

Your original join statement is looking at columnss in the same table

Joins are usually used so that you can compare data in different tables. Hence, you are joining the tables. that’s about all I can help with.

9

Bottom line, when you have a query that involved (I see 9 joins just skimming it) a DBA should really be the one tweaking it, especially if the datasets are very large. Add in one improperly indexed join on a huge dataset and you can bring the entire DB to a screeching halt when you execute the query.

10

Funny I was just talking to one of JayS’s old coworkers about how awesome he is on lunch today lol

11

lol wat? PM me a name so I know who to paypal some money to.

12

Thats where I was confused. You should be joining two tables by a common value so it makes one big table. If there is type casting issues, you aren’t using the right fields. Lol.

13

Got it to work finally, here’s the code:

select @ComputerName = '%' +lower(@ComputerName) + '%' 

 select  distinct v_R_System_Valid.ResourceID, 
 v_R_System_Valid.Netbios_Name0 AS [Computer Name], 
 v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup], 
 v_Site.SiteName as [SMS Site Name], 
 v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 AS [Top Console User], 
 v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], 
 v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level], 
 v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [Serial Number], 
 v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 AS [Asset Tag], 
 v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer], 
 v_GS_COMPUTER_SYSTEM.Model0 AS [Model], 
 v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)], 
 v_Network_DATA_Serialized.IPAddress0 AS [IP Address],
 v_GS_PROCESSOR.MaxClockSpeed0 AS [Processor (GHz)], 
 (Select sum(Size0) from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID ) 
 where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = @CollectionID) As [Disk Space (MB)], 
 (Select sum(FreeSpace0) from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID ) 
 where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = @CollectionID) As [Free Disk Space (MB)] 
 from v_R_System_Valid 
 inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID) 
 left join v_GS_SYSTEM_ENCLOSURE_UNIQUE on (v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System_Valid.ResourceID) 
 inner join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID) 
 inner join v_GS_X86_PC_MEMORY on (v_GS_X86_PC_MEMORY.ResourceID = v_R_System_Valid.ResourceID) 
 Left Join v_Network_DATA_Serialized on (v_Network_DATA_Serialized.ResourceID = v_R_System_Valid.ResourceID)
 inner join v_GS_PROCESSOR on (v_GS_PROCESSOR.ResourceID = v_R_System_Valid.ResourceID) 
 inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID) 
 left join v_Site on (v_FullCollectionMembership.SiteCode = v_Site.SiteCode) 
 inner join v_GS_LOGICAL_DISK on (v_GS_LOGICAL_DISK.ResourceID = v_R_System_Valid.ResourceID) and v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0,1,2) 
 left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID) 
 Where v_FullCollectionMembership.CollectionID = @CollectionID 
 and (lower(v_R_System_Valid.Netbios_Name0) like @ComputerName or @ComputerName='') 
 and (v_R_System_Valid.Resource_Domain_OR_Workgr0 = @Domain or @Domain='') 
 and (v_Site.SiteName = @SMSSiteName or @SMSSiteName='') 
 and (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = @TopUser or @TopUser = '') 
 and (v_GS_OPERATING_SYSTEM.Caption0 = @OperatingSystem or @OperatingSystem='') 
 and (v_GS_COMPUTER_SYSTEM.Manufacturer0 = @Manufacturer or @Manufacturer = '') 
 and (v_GS_COMPUTER_SYSTEM.Model0=@Model or @Model = '') 
 Order by v_R_System_Valid.Netbios_Name0
14

Whenever I read this thread title I think it says, “BBQ Sauces”

15

:tup:

So you were trying to join on the wrong columns before.

16

wrong column on the wrong table

17

I guess ??? Once I looked over how the rest of the syntax I put the pieces together. This is basically a foreign language I’m just trying to match things up. The problem now is that it generates 3 “IPs” for each computer, one is blank, one is IPv4 and one is IPv6. Now I just have to figure out how to show only IPv4 ones. I’m guessing a where needs to be inserted somewhere in there to say Where …IPAddress0 like 192.%

18

Try this:

select @ComputerName = ‘%’ +lower(@ComputerName) + ‘%’

select distinct v_R_System_Valid.ResourceID,
v_R_System_Valid.Netbios_Name0 AS [Computer Name],
v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup],
v_Site.SiteName as [SMS Site Name],
v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 AS [Top Console User],
v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],
v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level],
v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [Serial Number],
v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 AS [Asset Tag],
v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer],
v_GS_COMPUTER_SYSTEM.Model0 AS [Model],
v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)],
v_Network_DATA_Serialized.IPAddress0 AS [IP Address],
v_GS_PROCESSOR.MaxClockSpeed0 AS [Processor (GHz)],
(Select sum(Size0) from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )
where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = @CollectionID) As [Disk Space (MB)],
(Select sum(FreeSpace0) from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )
where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = @CollectionID) As [Free Disk Space (MB)]
from v_R_System_Valid
inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)
left join v_GS_SYSTEM_ENCLOSURE_UNIQUE on (v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System_Valid.ResourceID)
inner join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)
inner join v_GS_X86_PC_MEMORY on (v_GS_X86_PC_MEMORY.ResourceID = v_R_System_Valid.ResourceID)
Left Join v_Network_DATA_Serialized on (v_Network_DATA_Serialized.ResourceID = v_R_System_Valid.ResourceID)
inner join v_GS_PROCESSOR on (v_GS_PROCESSOR.ResourceID = v_R_System_Valid.ResourceID)
inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID)
left join v_Site on (v_FullCollectionMembership.SiteCode = v_Site.SiteCode)
inner join v_GS_LOGICAL_DISK on (v_GS_LOGICAL_DISK.ResourceID = v_R_System_Valid.ResourceID) and v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0,1,2)
left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID)
Where v_FullCollectionMembership.CollectionID = @CollectionID
and (lower(v_R_System_Valid.Netbios_Name0) like @ComputerName or @ComputerName=’’)
and (v_R_System_Valid.Resource_Domain_OR_Workgr0 = @Domain or @Domain=’’)
and (v_Site.SiteName = @SMSSiteName or @SMSSiteName=’’)
and (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = @TopUser or @TopUser = ‘’)
and (v_GS_OPERATING_SYSTEM.Caption0 = @OperatingSystem or @OperatingSystem=’’)
and (v_GS_COMPUTER_SYSTEM.Manufacturer0 = @Manufacturer or @Manufacturer = ‘’)
and (v_GS_COMPUTER_SYSTEM.Model0=@Model or @Model = ‘’)
and (v_Network_DATA_Serialized.IPAddress0 like(‘192.%’)
Order by v_R_System_Valid.Netbios_Name0

19

That worked! Except you had a ( Before the '192

20

http://www.scruta.org/scruta.org/wp-content/uploads/2011/03/anserous1.jpg