Notification

Icon
Error

Patch Tuesday report, last 3 months

Posted: Thursday, February 28, 2019 5:50:07 PM(UTC)
Esben.D

Esben.D

Member Administration Original PosterPosts: 1,834
8
Like
Hi guys,


I've created a new report since multiple people have asked for a report similar to the Patch Tuesday reports but with more history.

The report has a column per month to indicate if the patch for that month was installed or not. If patches for the latest month have been installed, the asset should be highlighted green.

If you have suggestions or comments, feel free to let me know. This is only an initial version that I've tested on my local test machines.
The report looks in both the currently scanned Windows updates and the history table since I noticed that some Windows updates can be removed when you install a newer version

Code:

Select Distinct Top 1000000 Coalesce(tsysOS.Image,
  tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblState.Statename As State,
  Case
    When tblAssets.AssetID = Two.AssetID Then 'Installed'
    Else 'Not Installed'
  End As [Patch July],
  Case
    When tblAssets.AssetID = One.AssetID Then 'Installed'
    Else 'Not Installed'
  End As [Patch August],
  Case
    When tblAssets.AssetID = Three.AssetID Then 'Installed'
    Else 'Not Installed'
  End As [Patch September],
  Case
    When tblComputersystem.Domainrole > 1 Then 'Server'
    Else 'Workstation'
  End As [Workstation/Server],
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  Case
    When tsysOS.OScode Like '10.0.10240%' Then '1507'
    When tsysOS.OScode Like '10.0.10586%' Then '1511'
    When tsysOS.OScode Like '10.0.14393%' Then '1607'
    When tsysOS.OScode Like '10.0.15063%' Then '1703'
    When tsysOS.OScode Like '10.0.16299%' Then '1709'
    When tsysOS.OScode Like '10.0.17134%' Then '1803'
    When tsysOS.OScode Like '10.0.17763%' Then '1809'
    When tsysOS.OScode Like '10.0.18362%' Then '1903'
  End As Version,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Case
    When tblErrors.ErrorText Is Not Null Or
      tblErrors.ErrorText != '' Then
      'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
    Else ''
  End As ScanningErrors,
  Convert(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
  GetDate())) + ' days ago' As WindowsUpdateInfoLastScanned,
  Case
    When Convert(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
      GetDate())) > 3 Then
      'Windows update information may not be up to date. We recommend rescanning this machine.'
    Else ''
  End As Comment,
  Case
    When tblAssets.AssetID = Three.AssetID Then '#d4f4be'
    Else '#ffadad'
  End As backgroundcolor
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
    tblAssets.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID
      From tblQuickFixEngineering
        Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID
          = tblQuickFixEngineering.QFEID
        Inner Join tblQuickFixEngineeringHist On
          tblQuickFixEngineeringHist.QFEID = tblQuickFixEngineeringUni.QFEID
      Where tblQuickFixEngineeringUni.HotFixID In ('KB4512491', 'KB4512476',
        'KB4512486', 'KB4512506', 'KB4512482', 'KB4512518', 'KB4512489',
        'KB4512488', 'KB4512497', 'KB4093109', 'KB4512517', 'KB4512507',
        'KB4512516', 'KB4512501', 'KB4511553', 'KB4512508')) As One On
    tblAssets.AssetID = One.AssetID
  Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID
      From tblQuickFixEngineering
        Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID
          = tblQuickFixEngineering.QFEID
        Inner Join tblQuickFixEngineeringHist On
          tblQuickFixEngineeringHist.QFEID = tblQuickFixEngineeringUni.QFEID
      Where tblQuickFixEngineeringUni.HotFixID In ('KB4507452', 'KB4507461',
        'KB4507449', 'KB4507456', 'KB4507462', 'KB4507464', 'KB4507448',
        'KB4507457', 'KB4507458', 'KB4093109', 'KB4507460', 'KB4507450',
        'KB4507455', 'KB4507435', 'KB4507469', 'KB4507453')) As Two On
    tblAssets.AssetID = Two.AssetID
  Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID
      From tblQuickFixEngineering
        Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID
          = tblQuickFixEngineering.QFEID
        Inner Join tblQuickFixEngineeringHist On
          tblQuickFixEngineeringHist.QFEID = tblQuickFixEngineeringUni.QFEID
      Where tblQuickFixEngineeringUni.HotFixID In ('KB4516051', 'KB4516026',
        'KB4516033', 'KB4516065', 'KB4516062', 'KB4516055', 'KB4516064',
        'KB4516067', 'KB4516070', 'KB4516044', 'KB4516068', 'KB4516066',
        'KB4516058', 'KB4512578', 'KB4515384')) As Three On tblAssets.AssetID =
    Three.AssetID
  Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
    And tblAssets.IPNumeric <= tsysIPLocations.EndIP
  Left Join (Select Distinct Top 1000000 TsysLastscan.AssetID As ID,
        TsysLastscan.Lasttime As QuickFixLastScanned
      From TsysWaittime
        Inner Join TsysLastscan On TsysWaittime.CFGCode = TsysLastscan.CFGcode
      Where TsysWaittime.CFGname = 'QUICKFIX') As QuickFixLastScanned On
    tblAssets.AssetID = QuickFixLastScanned.ID
  Left Join (Select Distinct Top 1000000 tblErrors.AssetID As ID,
        Max(tblErrors.Teller) As ErrorID
      From tblErrors
      Group By tblErrors.AssetID) As ScanningError On tblAssets.AssetID =
    ScanningError.ID
  Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
  Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
    tblErrors.ErrorType
Where tsysOS.OSname <> 'Win 2000 S' And tsysOS.OSname Not Like '%XP%' And
  tsysOS.OSname Not Like '%2003%' And (Not tsysOS.OSname Like 'Win 7%'
    Or Not tblAssets.SP = 0) And tblAssetCustom.State = 1 And
  tsysAssetTypes.AssetTypename Like 'Windows%'
Order By tblAssets.Domain,
  tblAssets.AssetName
Saulo Benigno
#1Saulo Benigno Member Posts: 7  
posted: 3/1/2019 5:53:35 PM(UTC)
There's a CASE error on Line 19, it's missing the Case command

You need to change this:

#
Else 'Out of date'
End As [Patch February],
When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation'
End As [Workstation/Server],
#

To this

#
Else 'Out of date'
End As [Patch February],
Case
When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation'
End As [Workstation/Server],
#

:)


Esben.D
#2Esben.D Member Administration Original PosterPosts: 1,834  
posted: 3/13/2019 1:32:27 PM(UTC)
Originally Posted by: Saulo Benigno Go to Quoted Post
There's a CASE error on Line 19, it's missing the Case command

You need to change this:

#
Else 'Out of date'
End As [Patch February],
When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation'
End As [Workstation/Server],
#

To this

#
Else 'Out of date'
End As [Patch February],
Case
When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation'
End As [Workstation/Server],
#

:)




Thanks!

Added the March updates to the report.
jmje
#3jmje Member Posts: 21  
posted: 4/10/2019 2:42:52 PM(UTC)
Looking forward to the April update to this report.
Is there a way to make it run faster? It takes a couple minutes to run in our environment, where every other report is done in seconds.

Thanks!
Tenacious
#4Tenacious Member Posts: 2  
posted: 4/16/2019 12:09:05 AM(UTC)
I'm curious about three things in this report.

1) AFAIK, updates are cumulative (other than delta updates) so, why would something be 'not up to date' if it is missing the January update but has the March Update. This would be pretty likely scenario for a computer deployed in late February. It seems to me as though January and February would still be up-to-date even if only march was installed. Maybe I misunderstand the intent.

2) I haven't dived in too deeply to your SQL but it seems like there are 5 sections with "Where tblQuickFixEngineeringUni.HotFixID" statements and a block of KB ids.
The first and second blocks are mostly February KBs.
The third and fourth blocks are mostly January KBs.
The fifth block is March KBs.
So, question 2 is, why do the January and February blocks repeat but not the March block?

3) Each of the five blocks also contains KB4093109, which appears to be the April update for Windows 10 1511. This seems odd to me. Is it intentional?

Thanks!
joe_user
#5joe_user Member Posts: 11  
posted: 4/17/2019 2:01:52 PM(UTC)
To your point, half of our computers that show "up to date" for April are reported "out of date" for March. I feel that the "date" wording is problematic as it seems that the criteria in the report only verify that a single month's updates are installed. A more apt description would seem to be "March 2019 OS updates installed" or "Some or all March 2019 OS updates NOT installed."

[EDIT to add "OS" because application updates (Sharepoint, Exchange, SQL) are NOT in the criteria, e.g. KB4487563 https://support.microsof...icrosoft-exchange-server]
Esben.D
#6Esben.D Member Administration Original PosterPosts: 1,834  
posted: 4/17/2019 3:34:11 PM(UTC)
Originally Posted by: jmje Go to Quoted Post
Looking forward to the April update to this report.
Is there a way to make it run faster? It takes a couple minutes to run in our environment, where every other report is done in seconds.

Thanks!

The reason why it takes so long is because is because it just has to do a lot since it's 3 already complex reports rolled into 1. The quickest way would be to run it directly in SSMS.


Originally Posted by: Tenacious Go to Quoted Post
I'm curious about three things in this report.

1) AFAIK, updates are cumulative (other than delta updates) so, why would something be 'not up to date' if it is missing the January update but has the March Update. This would be pretty likely scenario for a computer deployed in late February. It seems to me as though January and February would still be up-to-date even if only march was installed. Maybe I misunderstand the intent.

You're probably right and it was just me misunderstanding things. I'll have to remake it then.

Originally Posted by: Tenacious Go to Quoted Post

2) I haven't dived in too deeply to your SQL but it seems like there are 5 sections with "Where tblQuickFixEngineeringUni.HotFixID" statements and a block of KB ids.
The first and second blocks are mostly February KBs.
The third and fourth blocks are mostly January KBs.
The fifth block is March KBs.
So, question 2 is, why do the January and February blocks repeat but not the March block?

Think this is related to the delta update mindset, where you have 3 subqueries, month 1, month 1+2 and month 1+2+3.
Originally Posted by: Tenacious Go to Quoted Post

3) Each of the five blocks also contains KB4093109, which appears to be the April update for Windows 10 1511. This seems odd to me. Is it intentional?

Thanks!

Microsoft has not released new security updates for this specific build for some time. Which is why it has remained the same.

Originally Posted by: joe_user Go to Quoted Post
To your point, half of our computers that show "up to date" for April are reported "out of date" for March. I feel that the "date" wording is problematic as it seems that the criteria in the report only verify that a single month's updates are installed. A more apt description would seem to be "March 2019 updates installed" or "Some or all March 2019 updates NOT installed."


In addition to the comments above about the updates being cumulative I'll add this. The new report will basically just show whether that month's updates have been installed or not.

I'll post again when I've update the report with all these changes.

Esben.D
#7Esben.D Member Administration Original PosterPosts: 1,834  
posted: 4/17/2019 3:56:57 PM(UTC)
Updated the report.

Feel free to continue with the feedback. This was more of a small quick fix.
Tenacious
#8Tenacious Member Posts: 2  
posted: 4/17/2019 10:46:26 PM(UTC)
Originally Posted by: Tenacious Go to Quoted Post
I'm curious about three things in this report...

Originally Posted by: Esben.D Go to Quoted Post
You're probably right and it was just me misunderstanding things. I'll have to remake it then...


Originally Posted by: Tenacious Go to Quoted Post
2) I haven't dived in too deeply to your SQL...

Originally Posted by: Esben.D Go to Quoted Post
Think this is related to the delta update mindset...


Originally Posted by: Tenacious Go to Quoted Post

3) Each of the five blocks also contains KB4093109...

Originally Posted by: Esben.D Go to Quoted Post
Microsoft has not released new security updates for this specific build...


Thanks for the update. This report could shape up to be pretty valuable to me. Thanks!
Esben.D
#9Esben.D Member Administration Original PosterPosts: 1,834  
posted: 5/21/2019 8:25:30 AM(UTC)
Updated the report for May
Viper
#10Viper Member Posts: 8  
posted: 7/10/2019 4:06:22 AM(UTC)
Am I missing something or did you stop updating the report?
I do find this valuable.

Thanks
Esben.D
#11Esben.D Member Administration Original PosterPosts: 1,834  
posted: 7/10/2019 3:28:16 PM(UTC)
I updated it so it now includes May, June and July

Seems like I forgot for June as I was so busy Angel
Viper
#12Viper Member Posts: 8  
posted: 7/11/2019 2:32:20 AM(UTC)
Originally Posted by: Esben.D Go to Quoted Post
I updated it so it now includes May, June and July

Seems like I forgot for June as I was so busy Angel


No worries. I was fairly busy last month too.
Thanks for the update..!
Esben.D
#13Esben.D Member Administration Original PosterPosts: 1,834  
posted: 8/14/2019 8:40:45 AM(UTC)
Updated for August
dshu
#14dshu Member Posts: 26  
posted: 8/24/2019 7:04:41 PM(UTC)
Hey,

Line 58 appears to be incorrect. When the August patch is installed, clients remain highlighted in red and not green. I believe it should read :

'When tblAssets.AssetID = One.AssetID Then '#d4f4be'

instead of :

'When tblAssets.AssetID = Three.AssetID Then '#d4f4be'
Esben.D
#15Esben.D Member Administration Original PosterPosts: 1,834  
posted: 8/30/2019 1:41:55 PM(UTC)
Originally Posted by: dshu Go to Quoted Post
Hey,

Line 58 appears to be incorrect. When the August patch is installed, clients remain highlighted in red and not green. I believe it should read :

'When tblAssets.AssetID = One.AssetID Then '#d4f4be'

instead of :

'When tblAssets.AssetID = Three.AssetID Then '#d4f4be'


You are correct! Seems I missed that. Fixed it.
Victor Heijmerikx
#16Victor Heijmerikx Member Posts: 3  
posted: 9/3/2019 11:48:20 AM(UTC)
Either I am doing something wrong or not, but trying this report brings my Lansweeper server down.
I am running SQL server Express 2015 on Windows 2012r2 and so far no problems with custom reports.
When I try to save this report, it keeps churning and finally chokes my machine by filling the tempdb completely up.
(My database is 4,5 GB, free space on disk is 36 GB before I run the report, but it is all gone after an hour or so).
Probably I need to set a limit somewhere for tempdb, but the point is, it has never done this before. Think
alanalan
#17alanalan Member Posts: 2  
posted: 9/3/2019 6:02:56 PM(UTC)
Originally Posted by: Victor Heijmerikx Go to Quoted Post
Either I am doing something wrong or not, but trying this report brings my Lansweeper server down.
I am running SQL server Express 2015 on Windows 2012r2 and so far no problems with custom reports.
When I try to save this report, it keeps churning and finally chokes my machine by filling the tempdb completely up.
(My database is 4,5 GB, free space on disk is 36 GB before I run the report, but it is all gone after an hour or so).
Probably I need to set a limit somewhere for tempdb, but the point is, it has never done this before. Think


I have the same issue. I run quite a few custom reports, but this one kills my lansweeper server every time. Win 2012 R2 / SQL Express 2012
Hendrik.VE
#18Hendrik.VE Member Posts: 24  
posted: 9/4/2019 8:42:24 AM(UTC)
Same here. In SSMS it can take more then an hour to run this report, while I only have about 800 Windows assets.
Victor Heijmerikx
#19Victor Heijmerikx Member Posts: 3  
posted: 9/4/2019 9:03:01 AM(UTC)
EDIT: I had the incomplete file, now it is complete.

Workaround:
As I remained very curious about my patch status, I took the July 2019 report from the Patch Tuesday Reports section which does work and amended it to reflect the august KB numbers.
For Windows 10/2016 it checks if the patch tuesday KB number OR the refresh KB number from a week to two weeks later is installed.
Oh and I removed Windows build 1511 which we haven't used in two years.

Code:

Select Distinct Top 1000000 Coalesce(tsysOS.Image,
tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblState.Statename As State,
Case tblAssets.AssetID
When SubQuery1.AssetID Then 'Up to date'
Else 'Out of date'
End As [Patch status],
Case
When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation'
End As [Workstation/Server],
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
Case
When tsysOS.OScode Like '10.0.14393%' Then '1607'
When tsysOS.OScode Like '10.0.15063%' Then '1703'
When tsysOS.OScode Like '10.0.16299%' Then '1709'
When tsysOS.OScode Like '10.0.17134%' Then '1803'
When tsysOS.OScode Like '10.0.17763%' Then '1809'
When tsysOS.OScode Like '10.0.18362%' Then '1903'
End As Version,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '' Then
'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
Else ''
End As ScanningErrors,
Case
When tblAssets.AssetID = SubQuery1.AssetID Then ''
Else Case
When tsysOS.OSname = 'Win 2008' Then 'KB4512476 or KB4512491'
When tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 7 RC' Or
tsysOS.OSname = 'Win 2008 R2' Then 'KB4512506 or KB4512486'
When tsysOS.OSname = 'Win 2012' Or
tsysOS.OSname = 'Win 8' Then 'KB4512518 or KB4512482'
When tsysOS.OSname = 'Win 8.1' Or
tsysOS.OSname = 'Win 2012 R2' Then 'KB4512488 or KB4512489'
When tsysOS.OScode Like '10.0.14393' Or
tsysOS.OSname = 'Win 2016' Then 'KB4512517 or KB4512495'
When tsysOS.OScode Like '10.0.15063' Then 'KB4512507 or KB4512474'
When tsysOS.OScode Like '10.0.16299' Then 'KB4512516 or KB4512494'
When tsysOS.OScode Like '10.0.17134' Then 'KB4512501 or KB4512509'
When tsysOS.OScode Like '10.0.17763' Or
tsysOS.OSname = 'Win 2019' Then 'KB4511553 or KB4512534'
When tsysOS.OScode Like '10.0.18362' Then 'KB4512508 or KB4512941'
End
End As [Install one of these updates],
Convert(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
GetDate())) + ' days ago' As WindowsUpdateInfoLastScanned,
Case
When Convert(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
GetDate())) > 3 Then
'Windows update information may not be up to date. We recommend rescanning this machine.'
Else ''
End As Comment,
Case tblAssets.AssetID
When SubQuery1.AssetID Then '#d4f4be'
Else '#ffadad'
End As backgroundcolor
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID
= tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4512476', 'KB4512491', 'KB4512506',
     'KB4512486', 'KB4512518', 'KB4512482', 'KB4512488', 'KB4512489', 'KB4512517',
'KB4512495', 'KB4512507', 'KB4512474', 'KB4512516', 'KB4512494', 'KB4512501',
'KB4512509', 'KB4511553', 'KB4512534', 'KB4512508', 'KB4512941')) As
SubQuery1 On tblAssets.AssetID = SubQuery1.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join (Select Distinct Top 1000000 tblAssets.AssetID As ID,
TsysLastscan.Lasttime As QuickFixLastScanned
From TsysWaittime
Inner Join TsysLastscan On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Inner Join tblAssets On tblAssets.AssetID = TsysLastscan.AssetID
Where TsysWaittime.CFGname = 'QUICKFIX') As QuickFixLastScanned On
tblAssets.AssetID = QuickFixLastScanned.ID
Left Join (Select Distinct Top 1000000 tblAssets.AssetID As ID,
Max(tblErrors.Teller) As ErrorID
From tblErrors
Inner Join tblAssets On tblAssets.AssetID = tblErrors.AssetID
Group By tblAssets.AssetID) As ScanningError On tblAssets.AssetID =
ScanningError.ID
Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
tblErrors.ErrorType
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssets.AssetID Not In (Select Top 1000000 tblAssets.AssetID
From tblAssets Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tsysOS.OSname Like 'Win 7%' And tblAssets.SP = 0) And
tsysOS.OSname != 'Win 2000 S' And tsysOS.OSname Not Like '%XP%' And
tsysOS.OSname Not Like '%2003%' And tsysAssetTypes.AssetTypename Like
'Windows%' And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName
JacobH
#20JacobH Member Posts: 170  
posted: 9/4/2019 4:10:00 PM(UTC)
I have a sql guru looking at this to see if he can make it more efficient - hopefully will reply soon with an update
Hendrik.VE
#21Hendrik.VE Member Posts: 24  
posted: 9/5/2019 12:01:08 PM(UTC)
I just found out that when you replace the "Select (distinct) Top 1000000" with a more realistic value (eg. 5000), the report runs properly Dancing
To be sure you don't miss any information I think it's best to do a 'select count(*)' on all subqueries so you know which limit to configure.

Furthermore, I believe you can replace Select Top 1000000 tblQuickFixEngineering.AssetID with Select distinct Top 5000 tblQuickFixEngineering.AssetID. Is that right Esben?
Esben.D
#22Esben.D Member Administration Original PosterPosts: 1,834  
posted: 9/5/2019 12:36:35 PM(UTC)
Originally Posted by: Hendrik.VE Go to Quoted Post
I just found out that when you replace the "Select (distinct) Top 1000000" with a more realistic value (eg. 5000), the report runs properly Dancing
To be sure you don't miss any information I think it's best to do a 'select count(*)' on all subqueries so you know which limit to configure.

Furthermore, I believe you can replace Select Top 1000000 tblQuickFixEngineering.AssetID with Select distinct Top 5000 tblQuickFixEngineering.AssetID. Is that right Esben?


It should still work, yes. I'm not sure how limiting the results would make it run faster (if you have below 5k assets anyway) since the results will always be limited to the number of assets you have. Having the limit set higher should not affect anything (unless I'm misunderstanding it).

Then again, if it works, it works.
I wonder what happens if you just remove the "Top 1000000", since that is not a requirement for an SQL query.
alanalan
#23alanalan Member Posts: 2  
posted: 9/9/2019 4:40:55 PM(UTC)
Originally Posted by: Hendrik.VE Go to Quoted Post
I just found out that when you replace the "Select (distinct) Top 1000000" with a more realistic value (eg. 5000), the report runs properly Dancing
To be sure you don't miss any information I think it's best to do a 'select count(*)' on all subqueries so you know which limit to configure.

Furthermore, I believe you can replace Select Top 1000000 tblQuickFixEngineering.AssetID with Select distinct Top 5000 tblQuickFixEngineering.AssetID. Is that right Esben?


Interesting. I haven't tried running it with this change, but those lines are in lansweeper reports by default when you first create them. I think every other report I run starts with "Select (distinct) Top 1000000" and they all run fine.
Esben.D
#24Esben.D Member Administration Original PosterPosts: 1,834  
posted: 9/16/2019 1:38:33 PM(UTC)
Updated for September
Esben.D
#25Esben.D Member Administration Original PosterPosts: 1,834  
posted: 9/19/2019 10:55:07 AM(UTC)
Thanks to JacobH for sending me some performance suggestions, I've updated the original report. Hopefully it helps.

Active Discussions

Lansweeper Remove all users from old domain
by  cmuter   Go to last post Go to first unread
Last post: 9/20/2019 8:03:58 PM(UTC)
Lansweeper Static IP Address
by  cycleheat  
Go to last post Go to first unread
Last post: 9/20/2019 4:07:16 PM(UTC)
Lansweeper Bitlocker Encryption Recovery Key no information found
by  Stephane   Go to last post Go to first unread
Last post: 9/20/2019 2:26:19 PM(UTC)
Lansweeper InTune Scanning Issues
by  Esben.D  
Go to last post Go to first unread
Last post: 9/20/2019 12:34:59 PM(UTC)
Lansweeper Office 365 scanning issues
by  Esben.D   Go to last post Go to first unread
Last post: 9/20/2019 12:23:30 PM(UTC)
Lansweeper Hard Drive Tracking - Start to Finish
by  Lone Jedi  
Go to last post Go to first unread
Last post: 9/19/2019 8:11:56 PM(UTC)
Lansweeper Lansweeper Reporting Old Assets as New
by  Jpatterson   Go to last post Go to first unread
Last post: 9/19/2019 12:52:14 PM(UTC)
Lansweeper Cannot edit a ticket anymore
by  Esben.D  
Go to last post Go to first unread
Last post: 9/19/2019 12:32:42 PM(UTC)