list assets manually edited in last 30 days

[Jeff_Henze]

I'm trying to find a field that would indicate the last time the asset record was manually edited. For instance, if you enter some data into Custom4 and save the record, is there a field that updates with the current date/timesamp at that time? Ideally, for me, that field would *not* be updated by scans. I was hoping tblAssetCustom.Lastchanged would do the trick, but it doesn't.

It doesn't look like such a field exists, but thought I'd ask before giving up.

Thanks,
-Jeff

[MikeMc]

We had a similar need where we wanted to link the assets to what was reported from the tblConfigLog report. We ended up using a substring on a join statement. It works most of time but there are occasions where it won't work as intended with name changes and assets with the same name.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblADusers.Username,
  tblADusers.Userdomain,
  tblADusers.Displayname,
  Case tblConfigLog.ActionID When 1 Then 'change' When 2 Then '+'
    When 3 Then '-' End As Action,
  tblConfigLog.Description,
  tblConfigLog.Date
From tblConfigLog
  Left Join tblAssets
    On tblAssets.AssetName = (SubString(tblConfigLog.Description,
    CharIndex('''', tblConfigLog.Description, 1) + 1,
    (Len(tblConfigLog.Description) - CharIndex('''',
    Reverse(tblConfigLog.Description), 1)) - (CharIndex('''',
    tblConfigLog.Description, 1))))
  Left Join tblADusers On tblADusers.Userdomain + '\' + tblADusers.Username =
    tblConfigLog.displayname
Where tblConfigLog.Description Like '%Asset%' And tblConfigLog.Date >
  GetDate() - 30
Order By tblConfigLog.Date Desc

[Jeff_Henze]

That's a cool report - I don't use the helpdesk feature so I had to cut that part out, but otherwise works fine.

However... 🙂
I'm looking for a way to get a report of the asset record itself. The tblConfigLog approach to it doesn't allow me to tie back to the asset and report on it (for example, if I also wanted to list "Custom4" on that report from the found assets). That's why I was looking within tblAssetCustom for the date. The tblConfigLog only reports the asset name embedded within the description field.

[MrCount]

Hi,

I think this is stored in 'tblConfigLog.Date'

Select Top 1000000 tblConfigLog.Date,
tblConfigLog.displayname As ChangeMadeBy,
htblusers.name,
Case tblConfigLog.ActionID When 1 Then 'change' When 2 Then '+'
When 3 Then '-' End As Action,
tblConfigLog.Description,
tblConfigLog.OldValue,
tblConfigLog.NewValue,
htblusers.username,
htblusers.userdomain,
htblusers.email,
htblusers.telephone
From tblConfigLog
Left Join htblusers On htblusers.userid = tblConfigLog.UserID
Where tblConfigLog.Date > GetDate() - 30
Order By tblConfigLog.Date Desc