Notification

Icon
Error

Issues with Desktop Report

Posted: Thursday, March 4, 2021 8:07:10 PM(UTC)
Jake

Jake

Member Original PosterPosts: 4
0
Like
I'm trying to create a report that just shows Total numbers for all our Desktops for a specific Manufacturer or Model. If I can figure one out the other should be a piece of cake.

Code:
Select Top 1000000 tblAssetCustom.Manufacturer,
  Count(tblAssets.AssetID) As Total
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tsysAssetTypes.AssetTypename Like '%Windows%' And tblAssetCustom.Model <>
  '%Latitude%'
Group By tblAssetCustom.Manufacturer
Order By Total Desc


I have this but it is not filtering out the Latitude laptops. They will still show in our Total count. This also happens with the Lenovos as well if I try to wildcard with %ThinkPad%. I'm sure it's just a simple order of operations or value. SQL is not my forte and I'm trying to learn on the fly.

Any suggestions / help is appreciated.
RC62N
#1RC62N Member Posts: 522  
posted: 3/4/2021 9:11:29 PM(UTC)
If you want to use wildcards, you need to test for "like"-ness, not equality.

Change <> '%Latitude%' to NOT LIKE '%Latitude%' (i.e. the text string doesn't contain "Latitude").

Model = 'Latitude' (Model is "Latitude")
Model <> 'Latitude' (Model is not "Latitude")

Model LIKE 'Latitude%' (Model starts with "Latitude")
Model LIKE '%Latitude' (Model ends with "Latitude")
Model LIKE '%Latitude%' (Model contains "Latitude")
Model LIKE 'Lat%tude' (Model starts with "Lat" and ends with "tude")

Model NOT LIKE 'Latitude%' (Model doesn't start with "Latitude")
Model NOT LIKE '%Latitude' (Model doesn't end with "Latitude")
Model NOT LIKE '%Latitude%' (Model doesn't contain "Latitude")
Model NOT LIKE 'Lat%tude' (Model doesn't start with "Lat" and end with "tude")
Jake
#2Jake Member Original PosterPosts: 4  
posted: 3/4/2021 9:18:29 PM(UTC)
Originally Posted by: RC62N Go to Quoted Post
If you want to use wildcards, you need to test for "like"-ness, not equality.

Change <> '%Latitude%' to NOT LIKE '%Latitude%' (i.e. the text string doesn't contain "Latitude").

Model = 'Latitude' (Model is "Latitude")
Model <> 'Latitude' (Model is not "Latitude")

Model LIKE 'Latitude%' (Model starts with "Latitude")
Model LIKE '%Latitude' (Model ends with "Latitude")
Model LIKE '%Latitude%' (Model contains "Latitude")
Model LIKE 'Lat%tude' (Model starts with "Lat" and ends with "tude")

Model NOT LIKE 'Latitude%' (Model doesn't start with "Latitude")
Model NOT LIKE '%Latitude' (Model doesn't end with "Latitude")
Model NOT LIKE '%Latitude%' (Model doesn't contain "Latitude")
Model NOT LIKE 'Lat%tude' (Model doesn't start with "Lat" and end with "tude")


Thank you very much! I knew it was something I was just not thinking through. I was using LIKE statement in another query for LIKE %ipad% so I don't know why it didn't register for me.

Works like a charm now!

Active Discussions

Report Center Microsoft Outlook email bug EX255650
by  Esben.D   Go to last post Go to first unread
Last post: Yesterday at 2:30:16 PM(UTC)
Lansweeper Patch Tuesday May 2021
by  Esben.D  
Go to last post Go to first unread
Last post: 5/11/2021 8:13:06 PM(UTC)
Lansweeper Report showing only Wi-Fi Devices and MAC addresses
by  Andy.S   Go to last post Go to first unread
Last post: 5/11/2021 2:23:24 PM(UTC)
Lansweeper Modifying Purchase Date / Yearly Refresh Report
by  Cripple.Zero   Go to last post Go to first unread
Last post: 5/7/2021 7:06:47 PM(UTC)
Lansweeper Tweak LanSweepers Bios Version Audit to show Last Seen
by  Tyler M.  
Go to last post Go to first unread
Last post: 5/6/2021 8:22:40 PM(UTC)
Lansweeper Patch Volume Across Fleet
by  darren.kimber  
Go to last post Go to first unread
Last post: 5/5/2021 11:29:33 PM(UTC)