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 my friend this was what I was looking for!
you thaman!

[RCorbeil]

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]

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]

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]

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]

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]

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]

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]

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