cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
ProfessorNerdly
Engaged Sweeper II
I am testing LANSweeper right now to see if it fits our needs, but am having a bit of trouble with the SQL reports.

I am trying to create a report that would let me know anytime a new asset is found during a scan.

I tried to set the criteria of the tblAssets.Firstscan to equal CURRENT_DATE but it keeps saying that doesn't work for this Boolean operation. I am only moderately familiar with SQL and any help on this task would be greatly appreciated.

I have looked through the reports and don't see one that fits this by default and have looked through here to and did not seem to find it. I apologize if there is something already made for this.
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
If you would like to list assets which have been detected during the last 24 hours, you could use the filter
> GetDate() - 1
in field tblAssets.Firstseen. Please find a full example report query below:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblAssets.Firstseen > GetDate() - 1 And tblAssetCustom.State = 1
Order By tblAssets.Firstseen Desc

View solution in original post

4 REPLIES 4
ProfessorNerdly
Engaged Sweeper II
Ah OK, thank you. I wasn't sure if the reports always reflected the current database state, or if I needed to manually "run" the reports like some other inventory systems.

The system I currently use requires me to run a new report after doing a scan to ensure that I have current information. I like that these will automatically refresh to reflect the database without my intervention!

Thanks again, very helpful!
Daniel_B
Lansweeper Alumni
You need to keep in mind that scanning and reporting are two different things. Every time you run a report, you will just pull data from your Lansweeper database. The dashboard will be refreshed every minute by default, so the data on your Dashboard are always synchronized with your database.
How often new scans take place totally depends on your setup under Configuration\Scanning methods. I.e. with IP range scanning you can define scans based on a weekly schedule or in a repeating interval every X hours or minutes.
ProfessorNerdly
Engaged Sweeper II
That works PERFECT.

Is there anyway to get that to run automatically every day? I put it in the Data Reports widget on my Dashboard, but I don't know if that auto updates or what.
Daniel_B
Lansweeper Alumni
If you would like to list assets which have been detected during the last 24 hours, you could use the filter
> GetDate() - 1
in field tblAssets.Firstseen. Please find a full example report query below:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblAssets.Firstseen > GetDate() - 1 And tblAssetCustom.State = 1
Order By tblAssets.Firstseen Desc