‎05-09-2018 04:30 PM
Solved! Go to Solution.
‎05-11-2018 11:22 AM
AZHockeyNut wrote:
thanks Sean, any chance you can help figure out how to run a report on assets that shows 1) does it have windows.old and 2 does it have the feature installed to run cleanmgr?
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblFeatureUni.featureCaption,
tblAssets.Lastseen,
tblAssets.Lasttried,
TsysLastscan.Lasttime As LastFileScan,
Case
When TsysLastscan.Lasttime < GetDate() -
1 Then
'Last file scan more than 24 hours ago. Scanned data may not be accurate.'
End As Comment,
tblFileVersions.FilePathfull As PathSearched,
Case tblFileVersions.Found When 1 Then 'Yes' Else 'No' End As FileFound,
tblFileVersions.FileVersion,
tblFileVersions.CreationDate,
tblFileVersions.LastAccessed,
tblFileVersions.LastModified,
tblFileVersions.Lastchanged As LastChanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tblFileVersions On tblFileVersions.AssetID = tblAssets.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Inner Join tblFeature On tblAssets.AssetID = tblFeature.AssetId
Inner Join tblFeatureUni On tblFeatureUni.featUniID = tblFeature.featUniId
Where tblFeatureUni.featureCaption Like '%User Interfaces and Infrastructure%'
And tblFileVersions.FilePathfull = 'C:\Windows.old\filehere.txt' And
tblState.Statename = 'Active' And TsysWaittime.CFGname = 'files'
Order By tblAssets.Domain,
tblAssets.AssetName,
PathSearched
‎05-09-2018 07:47 PM
‎05-09-2018 05:36 PM
‎05-11-2018 11:22 AM
AZHockeyNut wrote:
thanks Sean, any chance you can help figure out how to run a report on assets that shows 1) does it have windows.old and 2 does it have the feature installed to run cleanmgr?
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblFeatureUni.featureCaption,
tblAssets.Lastseen,
tblAssets.Lasttried,
TsysLastscan.Lasttime As LastFileScan,
Case
When TsysLastscan.Lasttime < GetDate() -
1 Then
'Last file scan more than 24 hours ago. Scanned data may not be accurate.'
End As Comment,
tblFileVersions.FilePathfull As PathSearched,
Case tblFileVersions.Found When 1 Then 'Yes' Else 'No' End As FileFound,
tblFileVersions.FileVersion,
tblFileVersions.CreationDate,
tblFileVersions.LastAccessed,
tblFileVersions.LastModified,
tblFileVersions.Lastchanged As LastChanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tblFileVersions On tblFileVersions.AssetID = tblAssets.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Inner Join tblFeature On tblAssets.AssetID = tblFeature.AssetId
Inner Join tblFeatureUni On tblFeatureUni.featUniID = tblFeature.featUniId
Where tblFeatureUni.featureCaption Like '%User Interfaces and Infrastructure%'
And tblFileVersions.FilePathfull = 'C:\Windows.old\filehere.txt' And
tblState.Statename = 'Active' And TsysWaittime.CFGname = 'files'
Order By tblAssets.Domain,
tblAssets.AssetName,
PathSearched
‎05-09-2018 05:33 PM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now