→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
juanmasdeu
Engaged Sweeper
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
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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.

View solution in original post

12 REPLIES 12
juanmasdeu
Engaged Sweeper
Thanks a lot my friend this was what I was looking for!
you thaman!
RCorbeil
Honored Sweeper II
Treat the CASE...END block as just another field in the SELECT list.
Select Top 1000000
tblAssets.Username,
tblPrinters.Caption As Caption1,
tblAssetCustom.Location As Location1,
tblAssetCustom.Comments,
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssetCustom.Custom1 As [Print Manager],
CASE
WHEN Exists (SELECT Top 1
tblSoftware.AssetID
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
WHERE
tblSoftware.AssetID = tblAssets.AssetID
AND tblSoftwareUni.SoftwareName = 'Print Manager Plus')
THEN 'Yes'
ELSE 'No'
END AS PMPinstalled
From
tblAssets
...
juanmasdeu
Engaged Sweeper
sorry for this noob question, but where exactly under the code I have should i paste that code you just sent?

pasted it before select and throws an error....

than you in advance
RCorbeil
Honored Sweeper II
Add to your list of SELECTed fields:
  CASE
WHEN Exists (SELECT Top 1
tblSoftware.AssetID
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
WHERE
tblSoftware.AssetID = tblAssets.AssetID
AND tblSoftwareUni.SoftwareName = 'Print Manager Plus')
THEN 'Yes'
ELSE 'No'
END AS PMPinstalled


The "Top 1" is there to minimize the size of the returned list; you only care if there's a minimum of one instance. Feel free to eliminate. The condition will remain true whether the SELECT returns 1 or 1,000,000 results.

What you're saying there is "IF there's at least one instance of 'Print Manager Plus' associated with this asset THEN return 'Yes' ELSE return 'No'".
juanmasdeu
Engaged Sweeper
ok so, i decided to let the network printers aside of the list.

Now, i have another inquiry:

I would like a new column to show up with YES or NO information displayed taking into account if a software name "Print Manager Plus" is installed in the computers on the list that I already have (not all computers on the domain, just the ones that appear under this report that i have).

Is this possible? like using an IF statement or something like that?
So, IF "print manager plus" is installed, display YES on Column1
else display NO

does this make sense??

the code i have so far is this one:

Select Top 1000000 tblAssets.Username,
tblPrinters.Caption As Caption1,
tblAssetCustom.Location As Location1,
tblAssetCustom.Comments,
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssetCustom.Custom1 As [Print Manager]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblPrinters On tblAssets.AssetID = tblPrinters.AssetID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where (tblPrinters.Caption != 'Broderbund PDF Converter' And
tblPrinters.Caption != 'DYMO LabelWriter 400 Turbo' And tblPrinters.Caption !=
'DYMO LabelWriter 330-USB' And tblPrinters.Caption != 'FANTASMAGORICA' And
tblPrinters.Caption != 'DYMO LabelWriter 330 Turbo-USB' And
tblPrinters.Caption != 'Printer Driver Type 101' And tblPrinters.Caption !=
'DYMO LabelWriter 450 Turbo' And tblPrinters.Caption !=
'DYMO LabelWriter 450') And tblAssetGroups.AssetGroup = 'Staff & Faculty' And
(tblPrinters.Portname Like 'USB%' Or tblPrinters.Portname Like 'LPT%' Or
tblPrinters.Portname Like 'COM%') And tblAssetCustom.State = 1
Order By tblAssets.Username

what should I change or add for doing this?
thanks a lot
Hemoco
Lansweeper Alumni
If you want to list both network printers that don't meet your port criteria and local printers that do, you will need to add criteria to TWO criteria columns. E.g.:
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Username,
tblAssets.Userdomain,
tblAssetCustom.Location,
tblAssetCustom.Comments,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblPrinters.Caption,
tblPrinters.Portname
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblPrinters On tblAssets.AssetID = tblPrinters.AssetID
Where ((tblPrinters.Portname Like 'USB%' Or tblPrinters.Portname Like 'LPT%' Or
tblPrinters.Portname Like 'COM%') And tblAssetCustom.State = 1) Or
(tblPrinters.Portname Like '%.%.%.%' And tblAssetCustom.State = 1)
Order By tblAssets.Username
juanmasdeu
Engaged Sweeper
YES, i tried doing that, adding the network=1 but i think what that does it looks for any network printer but those will also have to be on the porthosts specified (it doesn't override the ports i specified for filtering the XPS document writer, fax, etc) if i delete that argument, the network and local printer appear but also y have all that junk like xps document writer, fax, send to one note, etc. and that is no good.

Any ideas??

thank you
RCorbeil
Honored Sweeper II
Looking at tblPrinters I would theorize that listing printers where Network=1 would return the list that you're looking for, but doing that on my own installation returns negligible results. I suspect that the information you're looking for isn't recorded by LANSweeper. LANSweeper support should be able to confirm that.
juanmasdeu
Engaged Sweeper
Guys,

one more question, I would also like to add all the network printers to the list.
what should I add??
If i take the WHERE statement that only shows me the printers in the ports, a lot of junk apears like the XPS writer or send to one note, etc.
I don't want those to appear, just the local and network printers.

this is the code i have so far

Select Top 1000000 tblAssets.Username,
tblPrinters.Caption As Caption1,
tblAssetCustom.Location As Location1,
tblAssetCustom.Comments,
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblPrinters.Portname
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblPrinters On tblAssets.AssetID = tblPrinters.AssetID
Where (tblPrinters.Portname Like 'USB%' Or tblPrinters.Portname Like 'LPT%' Or
tblPrinters.Portname Like 'COM%') And tblAssetCustom.State = 1
Order By tblAssets.Username


thanks a lot