cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Watoo
Engaged Sweeper
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?
1 ACCEPTED SOLUTION
MikeMc
Champion Sweeper II
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

View solution in original post

9 REPLIES 9
SystemsIT
Engaged Sweeper III
Correct that we are unfortunately.
MikeMc
Champion Sweeper II
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
Engaged Sweeper III
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
Engaged Sweeper
This is working now - again thanks for your help. Very impressed with the service and the product.
Watoo
Engaged Sweeper
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
Champion Sweeper II
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
Engaged Sweeper
That is really fantastic - excellent service. Thank you very much.
MikeMc
Champion Sweeper II
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
Engaged Sweeper III

This report is kicking up a lot of duplicates. 

Michaelzip0_0-1707418952001.png