cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
MarkJonN
Engaged Sweeper II
Hello, May i know how to remove the Microsoft Windows 10 and leave only the Professional, Enterprise and Pro in tblOperatingsystem.Caption OS Version. I Tried this report below but Microsoft Windows 10 still showing in my query report.

Select Top 1000000 tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysOS.OSname As [OS Name],
tblOperatingsystem.Caption As [OS Version],
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblSystemEnclosure On
tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes =
TsysChassisTypes.Chassistype
Inner Join tblPortableBattery On
tblAssets.AssetID = tblPortableBattery.AssetID
Inner Join lansweeperdb.dbo.tblOperatingsystem On tblAssets.AssetID =
tblOperatingsystem.AssetID
Where tblAssets.Lastseen <> '' And tblAssetCustom.State = 1 And
tblAssets.Assettype = -1
Order By tblAssets.AssetName



See the 2nd picture i attached, i want my report same like that.

Any help will do, Thank you!!! Team LS!
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
Keeping it clean and legible,
  CASE
WHEN tsysOS.OSname = 'Win 7' THEN Replace(tblOperatingsystem.Caption, 'Microsoft Windows 7 ', '')
WHEN tsysOS.OSname = 'Win 8' THEN Replace(tblOperatingsystem.Caption, 'Microsoft Windows 8 ', '')
WHEN tsysOS.OSname = 'Win 8.1' THEN Replace(tblOperatingsystem.Caption, 'Microsoft Windows 8.1 ', '')
WHEN tsysOS.OSname = 'Win 10' THEN Replace(tblOperatingsystem.Caption, 'Microsoft Windows 10 ', '')
ELSE tblOperatingsystem.Caption
END AS [OS Version],


If you don't care so much about legibility, you could nest Replace():
Replace(Replace(Replace(Replace(Replace(tblOperatingsystem.Caption, 'Microsoft Windows ', ''), '10 ', ''), '7 ', ''), '8 ', ''), '8.1 ', '') As [OS Version],

As one who has to come back to code and modify things later, I am not suggesting doing this, but because you don't seem to have used Replace() before, I'm throwing it out there just to show that it can be done.

Unpacking it for illustration:
/* 5 */  Replace(
/* 4 */ Replace(
/* 3 */ Replace(
/* 2 */ Replace(
/* 1 */ Replace(tblOperatingsystem.Caption, 'Microsoft Windows ', '')
/* 2 */ , '10 ', '')
/* 3 */ , '7 ', '')
/* 4 */ , '8 ', '')
/* 5 */ , '8.1 ', '') As [OS Version],

First, because it's common to all of them, strip out "Microsoft Windows ".
Second, if there's a "10 ", remove that.
Third, if there's a "7 ", remove that.
Fourth, if there's an "8 ", remove that.
Fifth, if there's an "8.1 ", remove that.

Again, do not do this. It does work. There's a time and a place for it. This isn't it.

View solution in original post

6 REPLIES 6
MarkJonN
Engaged Sweeper II
It's all working, You are good man! Thanks a lot!! Stay Safe!
RCorbeil
Honored Sweeper II
Keeping it clean and legible,
  CASE
WHEN tsysOS.OSname = 'Win 7' THEN Replace(tblOperatingsystem.Caption, 'Microsoft Windows 7 ', '')
WHEN tsysOS.OSname = 'Win 8' THEN Replace(tblOperatingsystem.Caption, 'Microsoft Windows 8 ', '')
WHEN tsysOS.OSname = 'Win 8.1' THEN Replace(tblOperatingsystem.Caption, 'Microsoft Windows 8.1 ', '')
WHEN tsysOS.OSname = 'Win 10' THEN Replace(tblOperatingsystem.Caption, 'Microsoft Windows 10 ', '')
ELSE tblOperatingsystem.Caption
END AS [OS Version],


If you don't care so much about legibility, you could nest Replace():
Replace(Replace(Replace(Replace(Replace(tblOperatingsystem.Caption, 'Microsoft Windows ', ''), '10 ', ''), '7 ', ''), '8 ', ''), '8.1 ', '') As [OS Version],

As one who has to come back to code and modify things later, I am not suggesting doing this, but because you don't seem to have used Replace() before, I'm throwing it out there just to show that it can be done.

Unpacking it for illustration:
/* 5 */  Replace(
/* 4 */ Replace(
/* 3 */ Replace(
/* 2 */ Replace(
/* 1 */ Replace(tblOperatingsystem.Caption, 'Microsoft Windows ', '')
/* 2 */ , '10 ', '')
/* 3 */ , '7 ', '')
/* 4 */ , '8 ', '')
/* 5 */ , '8.1 ', '') As [OS Version],

First, because it's common to all of them, strip out "Microsoft Windows ".
Second, if there's a "10 ", remove that.
Third, if there's a "7 ", remove that.
Fourth, if there's an "8 ", remove that.
Fifth, if there's an "8.1 ", remove that.

Again, do not do this. It does work. There's a time and a place for it. This isn't it.
MarkJonN
Engaged Sweeper II
@RC62N It's working for Windows 10.

Upon checking on my report we have Windows 7? Windows 8 & Windows 8.1? how can i change that as well?

Thank you!!
RCorbeil
Honored Sweeper II
Select Top 1000000
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysOS.OSname As [OS Name],
Replace(tblOperatingsystem.Caption, 'Microsoft Windows 10 ', '') As [OS Version],
tblAssets.Lastseen
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblSystemEnclosure On tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes = TsysChassisTypes.Chassistype
Inner Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Inner Join lansweeperdb.dbo.tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Where
tblAssets.Lastseen <> ''
And tblAssetCustom.State = 1
And tblAssets.Assettype = -1
Order By
tblAssets.AssetName
MarkJonN
Engaged Sweeper II
Thanks for that. However, Do you have a sample of full report? i'm having an error when inserting the replace code on the query.
RCorbeil
Honored Sweeper II
Refer to the SQL Replace() function. (W3Schools, Microsoft)

In your case, change your OS Version field:
Replace(tblOperatingsystem.Caption, 'Microsoft Windows 10 ', '') As [OS Version],

Here you're saying "search the Caption field for the text 'Microsoft Windows 10 ' and, if found, replace it with no text". Note the trailing space after "Windows 10".