Complex Report on all Computers

[Watoo]

Below are the fields I wish to report on. I have tried pulling these in from several different reports but my SQL knowledge is non-existent and all my reports have failed so far. Some of it is quite simple - other stuff I'm not sure if it's possible to report on. I know this is a lot but any help what so ever is appreciated.

Machine Name: need no explanation
Primary User: will settle for last logged in user - though most logged in user would be preferred
Data Issued: I think "First Seen" would suffice
Serial Number: need no explanation
Make: need no explanation
Model: need no explanation
Purchase Date: need no explanation
Warranty Expiry: need no explanation
Operating System: need no explanation
Non-Encrypted drives?: list of non-encrypted drives (is this possible?)
Anti Virus up to date?: need no explanation
Date of last Windows Updates Installed: need no explanation
Number of Outstanding windows updates: not sure if this possible
Last non-domain joined Account Log In: Basically we wish to see if a local admin account has ever logged into the machine and if so what the account name was - if we could report on when this was as well then that would be great.
App Locker Enabled: is there a way to report on App Locker?

[SystemsIT]

Correct that we are unfortunately.

[MikeMc]

By the error message, I am assuming you are running SQL Compact Edition for your Lansweeper? If so, there is not much I can do as I do not run SQL CE in our environment.

[SystemsIT]

Great report.

Testing it i am getting this error:

"Error while saving: "There was an error parsing the query. [Token line number = 1. Token line offset = 76,Token in error = Select]"

Any thoughts?

[Watoo]

This is working now - again thanks for your help. Very impressed with the service and the product.

[Watoo]

Ah - just ran the report and got the error:

error while getting report
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

[MikeMc]

Watoo wrote:
Ah - just ran the report and got the error:

error while getting report
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

I'm not sure why you are getting that error. Perhaps a time zone setting difference? Anyway, I updated my original post and removed the datetime conversions and tweaked the logic a little bit to catch some missing assets.

[Watoo]

That is really fantastic - excellent service. Thank you very much.

[MikeMc · Accepted solution]

I threw this report together for you, but there are a couple of things worth bringing up:

• Primary user: This will list the most frequent user for the computer.
  
• Non-encrypted drives: I pulled the drive letters listed from the Computer: Encryptable Volumes report with a protection status of OFF.
  
• Windows update last install: I pulled the latest date from the Quickfixengineering section.
  
• Outstanding updates: This information isn't pulled in by Lansweeper as far as I know. I did include the total number of updates reportly installed from Quickfixengineering. You can compare the counts to your other systems.
  
• Last non-domain joined admin account login: This has been included.
  
• Applocker status: I don't believe there is a good built-in method to check the Applocker status. I did include a check on the Application Identity service.
  

Select Distinct Top 1000000 a.AssetID,
  a.AssetName,
  Reverse(Stuff(Reverse((Select (Case When Exists(Select 1 From tblADusers adu
      Where adu.Userdomain = x.Domain And adu.Username = x.Username) Then
      (Select adu.Name + ', ' From tblADusers adu
      Where adu.Userdomain = x.Domain And adu.Username = x.Username)
      Else x.Domain + '\' + x.Username + ', ' End) From (Select cpli.Domain,
      cpli.Username,
      count(*) As name_count,
      Rank() Over (Order By count(*) Desc) As rank
    From tblCPlogoninfo cpli
    Where cpli.AssetID = a.AssetID
    Group By cpli.Domain,
      cpli.Username) x
  Where x.rank = 1 For Xml Path(''))), 1, 2, '')) As PrimaryUser,
  a.Firstseen,
  ac.Serialnumber,
  ac.Manufacturer,
  ac.Model,
  ac.PurchaseDate,
  ac.Warrantydate,
  os.Caption As OS,
  Reverse(Stuff(Reverse((Select ev.DriveLetter + ', '
  From tblEncryptableVolume ev
  Where ev.AssetId = a.AssetID And Len(ev.DriveLetter) = 2 And
    ev.ProtectionStatus = 0 Order By ev.DriveLetter For Xml Path(''))), 1, 2,
  '')) As NonEncryptedDrives,
  (Case When av.productUpToDate = 1 Then 'Up To Date'
    When av.productUpToDate = 0 Then 'Out of Date'
    When Not Exists(Select s.AssetID
    From tblSoftware s Inner Join tblSoftwareUni su On su.SoftID = s.softID
      Inner Join tsysantivirus tsav On su.softwareName Like tsav.Software
    Where s.AssetID = a.AssetID) Then 'No AV Found' Else 'Unknown'
  End) As AVStatus,
  (Select Max(qfe.InstalledOn) From tblQuickFixEngineering qfe
  Where qfe.AssetID = a.AssetID) As LastUpdateInstalled,
  (Select Distinct COUNT(*) From tblQuickFixEngineering qfe
  Where qfe.AssetID = a.AssetID) As UpdateInstallCount,
  (Select Top 1 cpli.Username From tblCPlogoninfo cpli
  Where Exists(Select uig.AssetID,
      uig.Username,
      uig.Admingroup From tblUsersInGroup uig
    Where uig.AssetID = cpli.AssetID And uig.Username = cpli.Username And
      uig.Admingroup = 1) And Exists(Select u.AssetID,
      u.Name From tblUsers u
    Where u.AssetID = cpli.AssetID And u.Name = cpli.Username) And
    a.AssetID = cpli.AssetID Order By
    cpli.logontime Desc) As LastLocalAdminUser,
  (Select (Case When s.StateID = 4 Then 'Running' Else 'Not Running' End)
  From tblServices s Inner Join tblServicesUni su On su.ServiceuniqueID =
      s.ServiceuniqueID
  Where s.AssetID = a.AssetID And su.Caption = 'Application Identity')
  As ApplockerStatus,
  tsysOS.Image As icon
From tblAssets a
  Inner Join tblAssetCustom ac On ac.AssetID = a.AssetID
  Left Outer Join tblOperatingsystem os On os.AssetID = a.AssetID
  Left Outer Join tsysOS On a.OScode = tsysOS.OScode
  Left Outer Join tblAntivirus av On av.AssetID = a.AssetID
Where ac.State = 1 And a.Assettype = -1
Order By a.AssetName

[Michaelzip0]

This report is kicking up a lot of duplicates.