cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
mtcuser
Engaged Sweeper II
Hello. I look at the "Software: changes in the last 7 days" report to see what has been installed on user computers. I have just recently updated from 4.x to 5.x a couple of weeks ago. As far as I can tell, ever since I have updated from my previous version of 4.x to 5.x, currently 5.1.60, this report only shows software installed since Sunday. The report gets longer through out the week though. On Friday, I could see all the software that was installed after last Sunday. Today, I only see since Sunday and none of what I saw on Friday. It is like it resets the report every Sunday rather than actually showing me the last 7 days.

So, my question is, how can I change the query to actually show me what software has been installed in the last 7 days rather than just this week from Sunday?

Here is the current query from Lansweeper report editor.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.Image As icon,
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
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
Where tblSoftwareHist.Lastchanged > GetDate() - 1 And tblAssetCustom.State = 1
Order By tblSoftwareHist.Lastchanged Desc

Thank you for your help.
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
The query you posted lists software changes that occurred in the last 24 hours (1 day), not the last 7 days. Note the number marked below, which should be 7 instead of 1. Someone must have changed the query. (An update will not do this.)
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.Image As icon,
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
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
Where tblSoftwareHist.Lastchanged > GetDate() - 1 And tblAssetCustom.State = 1
Order By tblSoftwareHist.Lastchanged Desc

View solution in original post

2 REPLIES 2
mtcuser
Engaged Sweeper II
That worked perfectly! Thank you for your help!
Hemoco
Lansweeper Alumni
The query you posted lists software changes that occurred in the last 24 hours (1 day), not the last 7 days. Note the number marked below, which should be 7 instead of 1. Someone must have changed the query. (An update will not do this.)
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.Image As icon,
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
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
Where tblSoftwareHist.Lastchanged > GetDate() - 1 And tblAssetCustom.State = 1
Order By tblSoftwareHist.Lastchanged Desc