Local printers list

[juanmasdeu]

Hello,

I'm pretty new to creating reports and I wanted to create a report that lists all the LOCAL printers installed in the computers.

I managed to create a report that does that but there are many duplicates being shown.
I want the duplicates to disappear.
Tried using the Distinct option but as i also want some comments to be shown, it does not let me do so. I had to remove the distinct, is there any other way to do it?? why are the duplicates being shown?.

the sql I have is this one:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblPrintershist.Caption,
tblAssetCustom.Location As Location1,
tblAssetCustom.Comments,
tblAssets.IPAddress,
tblPrintershist.Portname
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblPrintershist On tblAssets.AssetID = tblPrintershist.AssetID
Where (tblPrintershist.Portname = 'USB001' And tblAssetCustom.State = 1) Or
(tblPrintershist.Portname = 'LPT1:') Or
(tblPrintershist.Portname = 'USB002') Or
(tblPrintershist.Portname = 'USB003') Or
(tblPrintershist.Portname = 'LPT2:')
Order By tblAssets.AssetName,
tblPrintershist.Caption


Your help is much appreciated.

thank you

[juanmasdeu]

Thanks a lot!!

I think that did the trick!

I will keep on testing this report...

Any idea if it's possible to auto update a field say a comment or other field if the printer is updated? my idea is to have the printer cartridge needed for that printer stated in the comments field. What I'm asking is, if we update the printer, is there a way that lansweeper updates de field so that it matches the cartridge that the updated (new) printer needs if we specify that somewhere?

Not sure if this makes any sense for you guys!

thanks a lot again

[RCorbeil · Accepted solution]

It looks like your problem is that you're linking against the printer history (tblPrintersHist), not the current printer list (tblPrinters). Change that and you should eliminate your multiplicate results.

There's also a small flaw in your WHERE clause:

WHERE
  (tblPrintershist.Portname = 'USB001' And tblAssetCustom.State = 1)
  Or (tblPrintershist.Portname = 'LPT1:')
  Or (tblPrintershist.Portname = 'USB002')
  Or (tblPrintershist.Portname = 'USB003')
  Or (tblPrintershist.Portname = 'LPT2:')

You're testing for

(Asset is active and printer port is USB001:)
OR (don't care about asset state and printer port is LPT1:)
OR (don't care about asset state and printer port is LPT2:)
etc.

where you want to be testing for

(Asset is active)
AND ( printer port is USB001:
      OR printer port is LPT1:
      OR printer port is LPT2:
      etc.
    )

Suggestion:

tblAssetCustom.State = 1
AND ( tblPrinters.Portname LIKE 'USB%'
      OR tblPrinters.Portname LIKE 'LPT%'
      OR tblPrinters.Portname LIKE 'COM%'
    )

As to the most recent logged-on user, add tblAssets.Username to your report. If you have users on multiple domains, add tblAssets.Userdomain, too. And if you want to know when the asset was last seen, of course, tblAssets.Lastseen.

[juanmasdeu]

I also want to add the lastuser field to the report.

So final report should show lastuser, local printer caption, Location1 and comments. I added these two last fields because I want to know what printer cartridge that printer uses (I add that manually in the comments and, where that printer is located, I add that manually in the Location field).

Any help is appreciated.

thank you