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

Lansweeper Automatic Follow-Up for Tickets
by  Francis Lee Mondia - Endace   Go to last post Go to first unread
Last post: Yesterday at 11:06:51 PM(UTC)
Lansweeper Can't see devices on Lansweeper
by  vqT4cDoP9iXyMZwoDUWU  
Go to last post Go to first unread
Last post: Yesterday at 8:33:21 PM(UTC)
Lansweeper LAPS managed password
by  SystemAdmin   Go to last post Go to first unread
Last post: Yesterday at 6:08:42 PM(UTC)
Lansweeper Install Adobe Reader Error
by  Brandon  
Go to last post Go to first unread
Last post: Yesterday at 2:46:54 PM(UTC)
Lansweeper Stop deployment
by  Tyler M.   Go to last post Go to first unread
Last post: Yesterday at 1:59:22 PM(UTC)
Lansweeper "Out of memory" in errorlog.txt
by  dua  
Go to last post Go to first unread
Last post: Yesterday at 1:00:42 PM(UTC)
Lansweeper Installation issues
by  Troy   Go to last post Go to first unread
Last post: Yesterday at 9:24:54 AM(UTC)
Lansweeper End of Life Asset Report
by  DJ Bradley  
Go to last post Go to first unread
Last post: 5/11/2021 4:09:33 PM(UTC)