Page 1 of 1

Printer Report

PostPosted: Mon Oct 31, 2011 11:04 am
by Martin.Taylor NHS
I am trying to return all printers that are returned within Alloy Navigator 6, this is to identify how many we currently have and their details.

We used to do this using the "Inv_Printers" table in AN5, but this is no longer available.

Any help would be much appreciated.

Re: Printer Report

PostPosted: Mon Oct 31, 2011 4:37 pm
by eliaslynch
I believe that information is now located in discovery. Navigator now only stores that information in a blob. I don't know if it's the same table name though.

Re: Printer Report

PostPosted: Wed Nov 02, 2011 6:06 am
by Martin.Taylor NHS
That's where they are!
I've used the Alloy Navigator & Discovery database to create the following:

USE Alloy6_Discovery
SELECT
MAX(Inv_Printers.[Name]) AS [Printer],
Inv_Printers.Port,
MAX(L.Location),
CASE WHEN CHARINDEX('\',OU.Placement) < 1 THEN
OU.Placement
ELSE SUBSTRING(OU.Placement,0,CHARINDEX('\',OU.Placement)) END Trust,
S.Status
FROM Inv_Printers RIGHT OUTER JOIN
Alloy6.dbo.Status AS S RIGHT OUTER JOIN
Computers INNER JOIN
Inv_NetNodes ON Computers.Node_ID = Inv_NetNodes.ID INNER JOIN
Alloy6.dbo.Computers AS C ON Computers.Name = C.Name COLLATE Latin1_General_CI_AS LEFT OUTER JOIN
Alloy6.dbo.Locations AS L ON C.Location_ID = L.ID LEFT OUTER JOIN
Alloy6.dbo.Organizational_Units AS OU ON C.Organization_ID = OU.ID ON S.ID = C.Status_ID ON Inv_Printers.Node_ID = Inv_NetNodes.ID
GROUP BY
CASE WHEN CHARINDEX('\',OU.Placement) < 1 THEN OU.Placement
ELSE SUBSTRING(OU.Placement,0,CHARINDEX('\',OU.Placement)) END
,[Port], S.Status