→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here
12-02-2017 12:13 AM
Solved! Go to Solution.
11-22-2023 09:23 PM
NVM, Got it sorted thanks to some AI. Here is the script as I am using it now:
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
Convert(datetime,SubString(SubQuery1.Value, 1, 😎 + ' ' +
SubString(SubQuery1.Value, 9, 2) + ':' + SubString(SubQuery1.Value, 11, 2) +
':' + SubString(SubQuery1.Value, 13, 2)) As [Last Rebuild/Deployment],
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
TsysLastscan.Lasttime As LastRegistryScan,
Case
When SubQuery1.Valuename Is Not Null And SubQuery1.Valuename <> ''
Then 'Yes'
Else 'No'
End As ValuenameFound,
SubQuery1.Lastchanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged
From tblRegistry
Where tblRegistry.Regkey Like '%SOFTWARE\Microsoft\Deployment 4' And
tblRegistry.Valuename = 'Deployment Timestamp') SubQuery1 On
SubQuery1.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1 And TsysWaittime.CFGname = 'registry'
Order By tblAssets.Domain,
tblAssets.AssetName
The end result looks like this:
11-22-2023 04:28 PM
I realize this is definitely an old thread, but Im curious if there is a way to further clean the report. The end result of grabbing the date isnt as...beautiful as I am hoping. Heres an example:
Is there an easy way in my report to make that "Value" a bit more legible/searchable?
11-22-2023 09:23 PM
NVM, Got it sorted thanks to some AI. Here is the script as I am using it now:
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
Convert(datetime,SubString(SubQuery1.Value, 1, 😎 + ' ' +
SubString(SubQuery1.Value, 9, 2) + ':' + SubString(SubQuery1.Value, 11, 2) +
':' + SubString(SubQuery1.Value, 13, 2)) As [Last Rebuild/Deployment],
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
TsysLastscan.Lasttime As LastRegistryScan,
Case
When SubQuery1.Valuename Is Not Null And SubQuery1.Valuename <> ''
Then 'Yes'
Else 'No'
End As ValuenameFound,
SubQuery1.Lastchanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged
From tblRegistry
Where tblRegistry.Regkey Like '%SOFTWARE\Microsoft\Deployment 4' And
tblRegistry.Valuename = 'Deployment Timestamp') SubQuery1 On
SubQuery1.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1 And TsysWaittime.CFGname = 'registry'
Order By tblAssets.Domain,
tblAssets.AssetName
The end result looks like this:
12-04-2017 02:18 PM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now