Duplicate entries on custom report

[cpmining]

Hi LS team

I am wondering if you could modify my script as to prevent duplicate entries of the asset?

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tsysOS.OSname,
tsysOS.Image As icon,
tblAssetCustom.Model,
tsysIPLocations.IPLocation,
tblAssets.Username,
tblAssets.IPAddress,
tblAssetCustom.Serialnumber As [Asset Serial],
tblMonitor.MonitorModel As [Model Monitor 1],
tblMonitor.SerialNumber As [Serial Monitor 1],
tblMonitor1.MonitorModel As [Model Monitor 2],
tblMonitor1.SerialNumber As [Seria Monitor 2],
tblMonitor2.MonitorModel As [Model Monitor 3],
tblMonitor2.SerialNumber As [Serial Monitor 3]
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Inner Join tblMonitor On tblAssets.AssetID = tblMonitor.AssetID
Inner Join tblMonitor tblMonitor1 On tblAssets.AssetID = tblMonitor1.AssetID
Inner Join tblMonitor tblMonitor2 On tblAssets.AssetID = tblMonitor2.AssetID
Where tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1
Order By tblAssets.AssetName

[AZHockeyNut]

I ran it by a DBA and basically the way you want the report to look, all on one line, is the issue. The data can be gotten, for example on 3 different lines, but not off the top of his head how to do it all on one line like you want.
sorry

[cpmining]

I have found this report, all I need is to include a 3rd monitor in the results. I've spent 1hr on this already and can't get my head around the coding.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename,
tsysOS.OSname,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblMonitor.MonitorModel As [Monitor 1 Model],
tblMonitor.SerialNumber As [Monitor 1 Serial],
tblMonitor1.MonitorModel As [Monitor 2 Model],
tblMonitor1.SerialNumber As [Monitor 2 Serial]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join (Select Top 1000000 tblMonitor.AssetID,
Min(tblMonitor.MonitorID) As Monitor1ID
From tblMonitor
Group By tblMonitor.AssetID) As Monitor1 On tblAssets.AssetID =
Monitor1.AssetID
Inner Join tblMonitor On Monitor1.Monitor1ID = tblMonitor.MonitorID
Inner Join (Select Top 1000000 tblMonitor.AssetID,
Max(tblMonitor.MonitorID) As Monitor2ID
From tblMonitor
Group By tblMonitor.AssetID) As Monitor2 On tblAssets.AssetID =
Monitor2.AssetID
Inner Join tblMonitor tblMonitor1 On Monitor2.Monitor2ID =
tblMonitor1.MonitorID
Inner Join (Select tblMonitor.AssetID,
Count(tblMonitor.MonitorID) As [Monitor Count]
From tblMonitor
Group By tblMonitor.AssetID
Having Count(tblMonitor.MonitorID) = 2) As MonitorCount On tblAssets.AssetID =
MonitorCount.AssetID
Where tblAssetCustom.State = 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName

[cpmining]

Unfortunately that is double Dutch to me. Not versed with SQL queries.

[AZHockeyNut]

My DBA helped me with this report (https://www.lansweeper.com/forum/yaf_postst15805_Report-user-logon-by-unique-Assets.aspx#post53304) if you look at the code there is a part where it does something like this...

select t.logontime , t.assetid
from (select logontime, assetid, row_number() over(partition by assetid order by logontime desc) as rn
from tblCPlogoninfo) as T
where rn = 1


so if you find a column will work to filter for you similar to the longontime one above and you look at how we put it in that report, you might be able to take it the rest of the way?

pretty sure you get the duplicate entries because you join the same table multiple times. what you possibly need to do is get the last changed date for each monitor and asset combination then when the dates are the same but serials differ, that becomes monitor 2 or monitor 3 otherwise they are blank or "no additional monitor" perhaps a select CASE statement?

[cpmining]

I notice it more with machines that do NOT have multiple monitors connected to it, but all 3 fields represent the same device (i.e. details of that 1 monitor).

I can confirm all details are the same. Screenshot attached.

AZHockeyNut I added the filed "last seen" as per your recommendation and that made no change.

My aim with this report is to find out what what assets on our network (based on location|user|serial|model etc) have monitors attached, how many of them and what the details are.

[AZHockeyNut]

are yours REALLY duplicate? I ran it on mine and I have what look like duplicates at first however, they are not unique within each set of the same IS. So if I have 6 entries for assetid 96, somewhere else on each of those lines there is a difference, like a different monitor serial or something. my hunch would be you need to grab a date/time from like last scanned or somehow tie in another element that tells you this is the most up to date config which I am guessing is what you are looking for?