Report on the history of changes on the device in the last week.

[rybyanets]

Hello!

I would like to deduce to receive alerts by mail every day if new records appear on the devices in the OS_Windows group in the History tab. Events that interest me:
1. Installation/removal of software
2. Changing the size of RAM
3. Changing the number of nuclei
4. Installation/removal Hotfix
5. Changing the composition of local groups

I began to write a request to view the event "Installation/removal of software" and it works correctly. Here he is

Select Top 1000000 tsysOS.Image As Icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
Case tblSoftwareHist.Action
When '1' Then '+'
When '2' Then '-'
End As [+/-],
tblSoftwareUni.softwareName As Software,
tblSoftwareHist.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
tblSoftwareHist.Installdate,
tblSoftwareHist.Lastchanged,
tsysOS.OSname As OS
From tblAssets
Inner Join tblSoftwareHist On tblAssets.AssetID = tblSoftwareHist.AssetID
Inner Join tblSoftwareUni On tblSoftwareHist.softid = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where tblSoftwareHist.Lastchanged > GetDate() - 7 And
tblAssetGroups.AssetGroup = 'OS_Windows'
Order By tblAssets.AssetName Collate Latin1_General_CI_AS

But if you add a viewing of the event "Changing the size of RAM" are shown only the VM on which both events occurred. This request. And I need to see all the events on the points described above on each VM.

Select Top 1000000 tsysOS.Image As Icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
Case tblSoftwareHist.Action
When '1' Then '+'
When '2' Then '-'
End As [+/-],
tblSoftwareUni.softwareName As Software,
tblSoftwareHist.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
tblSoftwareHist.Installdate,
tblSoftwareHist.Lastchanged,
tsysOS.OSname As OS,
tblPhysicalMemoryArrayHist.MaxCapacity
From tblAssets
Inner Join tblSoftwareHist On tblAssets.AssetID = tblSoftwareHist.AssetID
Inner Join tblSoftwareUni On tblSoftwareHist.softid = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tblPhysicalMemoryArrayHist On tblAssets.AssetID =
tblPhysicalMemoryArrayHist.AssetID
Where tblSoftwareHist.Lastchanged > GetDate() - 7 And
tblAssetGroups.AssetGroup = 'OS_Windows'
Order By tblAssets.AssetName Collate Latin1_General_CI_AS

Maybe there is another table in which all events are stored? And not as I look at separate tables (tblSoftwareHist, tblPhysicalMemoryArrayHist ...)

[Mister_Nobody]

I posted some reports:

https://community.lansweeper.com/t5/reports-analytics/report-removed-vms-as-assets/td-p/75968

https://community.lansweeper.com/t5/reports-analytics/a-colorful-report-for-a-not-so-fun-situation-m... - trackers for disk, cpu, RAM

[rybyanets]

Thanks for your work to create such informative reports!

I am a newcomer to Lansweeper and I don't know much. As I understand it, it is impossible to create a single report on the changes described by me above?

I just want to make a notification to the mail if new information appears on the History tab in the last 7 days. And if there are many changes, then I will receive a mountain of letters.

I would like to receive one letter with a lot of information.

[Mister_Nobody]

https://community.lansweeper.com/t5/reporting/send-report-and-event-log-alerts/ta-p/64333

[rybyanets]

I correctly understood you that all events from the tab in the screenshot are taken from the system journal?

[Mister_Nobody]

It's summary view of history info per asset.

[Mister_Nobody · Accepted solution]

1. You must set history checkbox Manage scanned item intervals - Lansweeper Community

2. You can see history per category into LS interface https://Ls_server/Report/report.aspx?det=changes

https://community.lansweeper.com/t5/managing-assets/track-software-memory-and-other-changes-on-windo...

3. You can create custom report with *hist tables (no documentation per table)

https://community.lansweeper.com/t5/reporting/access-lansweeper-database-documentation/ta-p/64481

[rybyanets]

Thank you, I will use your experience and on the basis of your reports will create the necessary ones.