VBS: polling a print server for low toner

$ausage$

Hey!

so we have alot of printer in remote sites. (this will be Ireland and GB) so to keep a stock of toners is not possible and we have people who run out now and again so i made a toner rodering system to automate it but they still didnt use it. so next logical step remove the User Error. I made a vbs script that would run once a day at 4pm and send a email to our ordering company with the printer details and the depot address. Great! only i have hit some snaggs. It works as it but you get the printer name (which the DB uses as a link to get the depot address) and the Address. you dont know what make or model or toner is needed. Also i was thinking to stop repeat emails for the same toner to add a counter so once 1 email is sent it waits x days till send another. But if i could get the serial of the toner I would know when it was changed which would be more accurate.

Anyway here is my code and any ideas or info would be good. excuse the mess its in as i ahve been playing with it alot.

Option Explicit

'On Error Resume Next

Set objshell = Wscript.CreateObject ("Wscript.Shell")

Dim strBody
Dim colErrorPrinters
Dim WshShell
Dim objPrinter
Dim strProdList
Dim OsType
Dim ADSysinfo
Dim objWSHShell
Set WshShell = CreateObject("WScript.Shell")
Dim retCode
Dim strComputer, objWMI, colItems, objItem, objName, InstalledOS, objEmail, objNetwork, objShell
Dim connectionString
Dim sql
Dim cn
Dim cmd
Dim rs
Dim Name
Dim testinfo


Set objNetwork = WScript.CreateObject("WScript.Network")
Set objWSHShell = Wscript.CreateObject("Wscript.Shell")
Set ADSysInfo = CreateObject("ADSystemInfo")

set cn = createobject("ADODB.Connection")
set cmd = createobject("ADODB.Command")


strComputer = "print server"
Set objWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colErrorPrinters = objWMI.ExecQuery ("Select * From Win32_Printer WHERE DetectedErrorState = 5")
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Intranet.mdb;"
cn.open connectionString
' Associate connection object with command object
cmd.ActiveConnection = cn
' Set the SQL statement of the command object
cmd.CommandText = sql


If colErrorPrinters.Count > 0 Then
	strBody = "The following printers are indicating low toner:" & vbCrLf




For Each objPrinter in colErrorPrinters
		strBody = strBody & objPrinter.Name & vbCrLf
'testinfo = "sc-la-p"
testinfo = Left(objPrinter.name, Len(objPrinter.name) - 3)

testinfo = testinfo & "%"

'wscript.echo testinfo


sql = "select * from Depots Where Printer1 LIKE '" & testinfo & "' "
	
	' Open connection

cmd.ActiveConnection = cn
' Set the SQL statement of the command object
cmd.CommandText = sql
set rs = cmd.execute

' Enumerate each row in the result set
while rs.EOF <> true and rs.BOF <> True
	' Using ordinal
	'wscript.echo "Result " & rs("Name") &vbCrLf & rs("Address1") &vbCrLf & rs("Address2") &vbCrLf & rs("Town") &vbCrLf & rs("Postcode")
		strBody = strBody & vbCrLf & rs("Name") &vbCrLf & rs("Address1") &vbCrLf & rs("Address2") &vbCrLf & rs("Town") &vbCrLf & rs("Postcode") & vbCrLf & " " & vbCrLf & " "

	rs.movenext
wend
Next
' Close Connection
cn.Close	
	

	

	strBody = strBody & vbCrLf & "Lots of love Jonny"


Set objEmail = CreateObject("CDO.Message")
		objEmail.Configuration.Fields.Item _
   			("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
		objEmail.Configuration.Fields.Item _
   			("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
       			"email ip" 
		objEmail.Configuration.Fields.Item _
   				("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
		objEmail.Configuration.Fields.Update
		objEmail.From = "helpdesk"
		objEmail.To = "helpdesk"
		objEmail.Subject = "Printers Toner Needed" 
		objEmail.Textbody = strBody
		objEmail.Send

						'wscript.echo "Email Sent"
						'wscript.echo (strBody)
	Else
					'wscript.echo "Email Not Sent"
		End If


'-----------------------------

FSL

If when querying the printer you cannot get the toner state or details returned then you would need to store the toner details in your database along with the address details. You would also need to update these when the toner is replaced.

Merrion

The Win32_Printer class has many attributes e.g. Location you can use...