# SQL Bauces Come on In **URL:** \ **Category:** NYSpeed Off Topic\ **Created:** [December 6, 2011, 11:26am UTC](https://forums.speedlife.net/t/sql-bauces-come-on-in/218927 "2011-12-06T11:26:49Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![ProgRocker](https://yyz2.discourse-cdn.com/flex034/user_avatar/forums.speedlife.net/progrocker/32/5434_2.png) [@ProgRocker](https://forums.speedlife.net/u/ProgRocker)\ **Post date:** [December 6, 2011, 11:26am UTC](https://forums.speedlife.net/t/sql-bauces-come-on-in/218927/1 "2011-12-06T11:26:49Z")
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](http://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. ```auto 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) 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) 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 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. ---
**Author:** ![boardjnky4](https://avatars.discourse-cdn.com/v4/letter/b/96bed5/32.png) [@boardjnky4](https://forums.speedlife.net/u/boardjnky4)\ **Post date:** [December 6, 2011, 11:35am UTC](https://forums.speedlife.net/t/sql-bauces-come-on-in/218927/2 "2011-12-06T11:35:00Z")
an IP Address is not an integer value, resource ID probably is…hence they cannot be compared in the Join statement. ---
**Author:** ![ProgRocker](https://yyz2.discourse-cdn.com/flex034/user_avatar/forums.speedlife.net/progrocker/32/5434_2.png) [@ProgRocker](https://forums.speedlife.net/u/ProgRocker)\ **Post date:** [December 6, 2011, 11:38am UTC](https://forums.speedlife.net/t/sql-bauces-come-on-in/218927/3 "2011-12-06T11:38:43Z")
I figured it was something like that…Any suggestions on fixing it? ---
**Author:** ![LZ1](https://avatars.discourse-cdn.com/v4/letter/l/7ab992/32.png) [@LZ1](https://forums.speedlife.net/u/LZ1)\ **Post date:** [December 6, 2011, 11:48am UTC](https://forums.speedlife.net/t/sql-bauces-come-on-in/218927/4 "2011-12-06T11:48:56Z")
drop table ![http://imgs.xkcd.com/comics/exploits_of_a_mom.png](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? > **[Need help adding IP address to SCCM report](https://www.experts-exchange.com/questions/27060255/Need-help-adding-IP-address-to-SCCM-report.html)** > > Can anyone please help direct me or let me know the code I need to add the IP address of each server onto this report. This would make this report perfect for me. Thank you SELECT distinct CS.name0... looks like people doing similar stuff ---
**Author:** ![ProgRocker](https://yyz2.discourse-cdn.com/flex034/user_avatar/forums.speedlife.net/progrocker/32/5434_2.png) [@ProgRocker](https://forums.speedlife.net/u/ProgRocker)\ **Post date:** [December 6, 2011, 11:55am UTC](https://forums.speedlife.net/t/sql-bauces-come-on-in/218927/5 "2011-12-06T11:55:27Z")
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. ```auto 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 ``` ---
**Author:** ![boardjnky4](https://avatars.discourse-cdn.com/v4/letter/b/96bed5/32.png) [@boardjnky4](https://forums.speedlife.net/u/boardjnky4)\ **Post date:** [December 6, 2011, 12:01pm UTC](https://forums.speedlife.net/t/sql-bauces-come-on-in/218927/6 "2011-12-06T12:01:39Z")
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. ---
**Author:** ![JayS](https://yyz2.discourse-cdn.com/flex034/user_avatar/forums.speedlife.net/jays/32/15375_2.png) [@JayS](https://forums.speedlife.net/u/JayS)\ **Post date:** [December 6, 2011, 12:02pm UTC](https://forums.speedlife.net/t/sql-bauces-come-on-in/218927/7 "2011-12-06T12:02:11Z")
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](http://msdn.microsoft.com/en-us/library/ms187928.aspx) ---
**Author:** ![boardjnky4](https://avatars.discourse-cdn.com/v4/letter/b/96bed5/32.png) [@boardjnky4](https://forums.speedlife.net/u/boardjnky4)\ **Post date:** [December 6, 2011, 12:03pm UTC](https://forums.speedlife.net/t/sql-bauces-come-on-in/218927/8 "2011-12-06T12:03:23Z")
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. ---
**Author:** ![JayS](https://yyz2.discourse-cdn.com/flex034/user_avatar/forums.speedlife.net/jays/32/15375_2.png) [@JayS](https://forums.speedlife.net/u/JayS)\ **Post date:** [December 6, 2011, 12:06pm UTC](https://forums.speedlife.net/t/sql-bauces-come-on-in/218927/9 "2011-12-06T12:06:37Z")
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. ---
**Author:** ![LZ1](https://avatars.discourse-cdn.com/v4/letter/l/7ab992/32.png) [@LZ1](https://forums.speedlife.net/u/LZ1)\ **Post date:** [December 6, 2011, 12:26pm UTC](https://forums.speedlife.net/t/sql-bauces-come-on-in/218927/10 "2011-12-06T12:26:49Z")
Funny I was just talking to one of JayS’s old coworkers about how awesome he is on lunch today lol ---
**Author:** ![JayS](https://yyz2.discourse-cdn.com/flex034/user_avatar/forums.speedlife.net/jays/32/15375_2.png) [@JayS](https://forums.speedlife.net/u/JayS)\ **Post date:** [December 6, 2011, 12:46pm UTC](https://forums.speedlife.net/t/sql-bauces-come-on-in/218927/11 "2011-12-06T12:46:49Z")
> [@LZ](#): > > Funny I was just talking to one of JayS’s old coworkers about how awesome he is on lunch today lol lol wat? PM me a name so I know who to paypal some money to. ---
**Author:** ![boxxa](https://yyz2.discourse-cdn.com/flex034/user_avatar/forums.speedlife.net/boxxa/32/5045_2.png) [@boxxa](https://forums.speedlife.net/u/boxxa)\ **Post date:** [December 6, 2011, 12:53pm UTC](https://forums.speedlife.net/t/sql-bauces-come-on-in/218927/12 "2011-12-06T12:53:07Z")
> [@boardjnky4](#): > > 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. 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. ---
**Author:** ![ProgRocker](https://yyz2.discourse-cdn.com/flex034/user_avatar/forums.speedlife.net/progrocker/32/5434_2.png) [@ProgRocker](https://forums.speedlife.net/u/ProgRocker)\ **Post date:** [December 7, 2011, 5:05am UTC](https://forums.speedlife.net/t/sql-bauces-come-on-in/218927/13 "2011-12-07T05:05:16Z")
Got it to work finally, here’s the code: ```auto 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 ``` ---
**Author:** ![Motocrossx23](https://yyz2.discourse-cdn.com/flex034/user_avatar/forums.speedlife.net/motocrossx23/32/5120_2.png) [@Motocrossx23](https://forums.speedlife.net/u/Motocrossx23)\ **Post date:** [December 7, 2011, 5:24am UTC](https://forums.speedlife.net/t/sql-bauces-come-on-in/218927/14 "2011-12-07T05:24:54Z")
Whenever I read this thread title I think it says, “BBQ Sauces” ---
**Author:** ![JayS](https://yyz2.discourse-cdn.com/flex034/user_avatar/forums.speedlife.net/jays/32/15375_2.png) [@JayS](https://forums.speedlife.net/u/JayS)\ **Post date:** [December 7, 2011, 5:37am UTC](https://forums.speedlife.net/t/sql-bauces-come-on-in/218927/15 "2011-12-07T05:37:24Z")
:tup: So you were trying to join on the wrong columns before. ---
**Author:** ![boardjnky4](https://avatars.discourse-cdn.com/v4/letter/b/96bed5/32.png) [@boardjnky4](https://forums.speedlife.net/u/boardjnky4)\ **Post date:** [December 7, 2011, 5:46am UTC](https://forums.speedlife.net/t/sql-bauces-come-on-in/218927/16 "2011-12-07T05:46:43Z")
wrong column on the wrong table ---
**Author:** ![ProgRocker](https://yyz2.discourse-cdn.com/flex034/user_avatar/forums.speedlife.net/progrocker/32/5434_2.png) [@ProgRocker](https://forums.speedlife.net/u/ProgRocker)\ **Post date:** [December 7, 2011, 5:51am UTC](https://forums.speedlife.net/t/sql-bauces-come-on-in/218927/17 "2011-12-07T05:51:40Z")
> [@JayS](#): > > :tup: > > So you were trying to join on the wrong columns before. 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.% ---
**Author:** ![JayS](https://yyz2.discourse-cdn.com/flex034/user_avatar/forums.speedlife.net/jays/32/15375_2.png) [@JayS](https://forums.speedlife.net/u/JayS)\ **Post date:** [December 7, 2011, 5:58am UTC](https://forums.speedlife.net/t/sql-bauces-come-on-in/218927/18 "2011-12-07T05:58:48Z")
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 ---
**Author:** ![ProgRocker](https://yyz2.discourse-cdn.com/flex034/user_avatar/forums.speedlife.net/progrocker/32/5434_2.png) [@ProgRocker](https://forums.speedlife.net/u/ProgRocker)\ **Post date:** [December 7, 2011, 6:04am UTC](https://forums.speedlife.net/t/sql-bauces-come-on-in/218927/19 "2011-12-07T06:04:16Z")
> [@JayS](#): > > Try this: That worked! Except you had a **(** Before the **'192** ---
**Author:** ![boxxa](https://yyz2.discourse-cdn.com/flex034/user_avatar/forums.speedlife.net/boxxa/32/5045_2.png) [@boxxa](https://forums.speedlife.net/u/boxxa)\ **Post date:** [December 7, 2011, 6:11am UTC](https://forums.speedlife.net/t/sql-bauces-come-on-in/218927/20 "2011-12-07T06:11:26Z")
![http://www.scruta.org/scruta.org/wp-content/uploads/2011/03/anserous1.jpg](http://www.scruta.org/scruta.org/wp-content/uploads/2011/03/anserous1.jpg) [Next page](https://forums.speedlife.net/t/sql-bauces-come-on-in/218927.md?page=2)