‎07-21-2016 05:18 PM
Solved! Go to Solution.
‎07-25-2016 10:16 PM
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
‎08-12-2016 11:16 PM
‎08-12-2016 10:01 PM
‎08-11-2016 10:40 PM
‎07-27-2016 02:58 PM
‎07-27-2016 12:49 PM
‎07-27-2016 02:42 PM
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.
‎07-27-2016 12:46 PM
‎07-25-2016 10:16 PM
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
‎02-08-2024 08:02 PM
This report is kicking up a lot of duplicates.
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now