Description
NOTE: SUPPORTED FOR SPICEWORKS 6.2 AND ABOVE
This script will look through all tickets and create appointments in an iCalendar format (ICS) based upon the due dates for tickets and IT services. The ICS file can than be referenced in Outlook or other email clients, creating a ‘Spiceworks’ calendar.
If the ticket is assigned to someone, they will show up as being attendees, or ‘scheduled’. If the ticket is unassigned, you will see a big ol’ UNASSIGNED before the summary in the event.
If a ticket does NOT have a due date, it will create a TODO item. Note that Outlook cannot see these items by default, but other programs can (ReminderFox, for example).
Ticket items will show up in the calendar like this:
Event summary: Ticket: #### - (Priority) Ticket summary
Event description:
http://your-spiceworks-server/tickets/list/single_ticket/####
Description of initial ticket problem
Created by: last_name, first_name
Added by Spiceworks DB2ICAL, 0.7 - Rob Dunn, 10/16/13
Section 1. Requirements
-
Install the 32-bit (even if you run Windows 64-bit) SQLite3 ODBC driver from:SQLite ODBC Driver
-
On your Spiceworks server:
-
Configure a SQLite3 ODBC DSN, point it to your spiceworks_prod.db file.
On a 64-bit system, you will need to do this by executing odbcad32.exe from the C:\Windows\sysWOW64 folder.
Note that you will schedule the script to execute FROM your Spiceworks server later.
Section 2. User account for scheduled task
Make sure you have a user account to use for a scheduled task that has:
* Read permissions to a ***copy*** of the Spiceworks DB file (c:\program files\spiceworks\db\spiceworks_prod.db or \\server\c$\program files\spiceworks\db\spiceworks_prod.db)
* Modify permissions to a folder which will be available via a web server for your email client to access).
Section 3. Modify the script for your environment
-
Copy the code to a text editor and save with a .vbs extension.
-
Edit the script variables in the code:
sRootAddress: Make it “” (two double-quotes) if you want it to pull from your SW email URL setting. In Spiceworks, this is located under ‘Settings’> ‘Email’> ‘Additional Email Settings’. Otherwise, set it to ‘http://yourserver:9675’ or ‘http://yourserver’ - no trailing slash.
sPathToDB: A full path to the COPY of the spiceworks_prod.db file - this can be either a direct path using a drive letter, or an UNC. See my caveat notes below about querying a /copy/ of the production database.
sEscalateToGroup: An email address of a distribution list you want to be the ‘owner’ of the calendar items. Not important, but it helps.
sPublishToPath: A network or folder location which is a published web folder where you can point your email client to. This is where the script will write the ICS file to.
iMakeTicketsDueInHowManyDays: An integer of how many days after the creation date you wish to make a “non due date” ticket report as a TODO due date.
iGoBackHowManyDays: An integer of how many days back you wish to query the database for tickets.
iPort: Specify a port for https here - if you leave it blank, it will use your default http port found in httpd.conf.
sPrefix: set to http or https depending on what port you use in iPort.
NOTE: This version of the script (0.5) still seems to bring in really old tickets if they are not closed.
Section 4. Testing
You can run it manually if you like once you get your variables in place - you should be able to point your email client to the web location where the ICS file is stored.
For best results, run this script from your Spiceworks server…although, it is entirely possible to run it from another location.
This is in ‘alpha’ stage, so let me know if there are any issues!
Section 5. Caveats
You cannot write anything to the events, as the script will overwrite the calendar with a new version whenever run.
5.x and later revisions of Spiceworks locks the entire database at times, making it difficult for the script to run and synchronize events. The best work around for this is to have an automated routine that copies the production database to another location, then make the synchronization script pull events from that instead. This works really well.
Section 6. Other notes
This works fantastically with PHPiCalendar found here: PHP iCalendar download | SourceForge.net - this is an internal web-based calendar that parses .ics files (you can link it to Google calendar too, awesome!). Use it if you want your managers to view it and easily subscribe in Outlook 2007+.
Also, I’ve tested it with ReminderFox (a Firefox Add-on) that brings up reminders against an ICS/Icalendar formatted file. Looks pretty good.
Source Code
Dim sScriptver
sScriptver = "0.7 - Rob Dunn, 10/16/13"
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
Dim objAppointments, objAppt, sPathToSpiceworks
Dim blAllDay, blreminder, Status, olBusy, ol, objSession, objFolder
Dim sServerAddress, sTicketAddress, sRootAddress, sUIDType, objfso, sTicketCategoryFieldName
Dim iMakeTicketsDueInHowManyDays
''Set oStmt = CreateObject("LiteX.LiteStatement")
''Set oDb = CreateObject("LiteX.LiteConnection")
'NOTE: You MUST have the Litex Automation wrapper, and register the
' sqlite3u.dll file to your system prior to running.
'Download it here: http://www.assembla.com/wiki/show/litex
'This is the fully resolved name via share or otherwise to the
' Spiceworks_prod.db file.
'
'HINT: Run this script as a user that has full read permissions
' to the DB
'Tell the script to make tickets with no due-date due in x days
iMakeTicketsDueInHowManyDays = 7
'Not yet implemented...
'bCreateCalendarsBasedOnTicketCategory = true
'How many days back to go for tickets
iGoBackHowManyDays = 30
'Which column to get the categories (will set the calendar item category)
sTicketCategoryFieldName = "category"
Function SetupSummary(sAssignedTo,sTicketID,sPriority,sSummary)
If trim(sAssignedTo) = "" or isnull(sAssignedTo) then
sAssignedTo_VAR = ""
Else
sAssignedTo_VAR = "[" & sAssignedTo & "]"
End If
sTicketID_VAR = "Ticket ID: " & sTicketID
sSummary_VAR = sSummary
sPriority_VAR = ucase(sPriority)
'Edit this line if you want to change up how the summary looks.
sSummaryOrder = sTicketID_VAR & " " & sAssignedTo_VAR & " - " & sPriority_VAR & " " & sSummary_VAR
SetupSummary = sSummaryOrder
End Function
'This value overrides the database email url setting (in pro email settings)
' Set it to blank if you want it to pull from your pro settings.
sRootAddress = ""
'Full path to Spiceworks installation, no trailing backslash "\"
sPathToSpiceworks = "\\spiceworks_server\c$\program files (x86)\Spiceworks"
'This should be your distribution list for IT or Helpdesk, etc.
' Not too useful at this point.
sEscalateToGroup = "yourgroup@yourdomain.com"
'Location where to save the ICS file when we are done with it.
' Whatever account you use to run this script needs to have
' modify permissions to the final folder location.
'
'Put the full path (no trailing backslash) here. Also, don't
' put a filename here.
sPublishToPath = "\\server\c$\Inetpub\wwwroot\phpicalendar-2.4\calendars"
'Location to save temporary working ICS. Note that the name of the
' file will be the same name when it is moved from the temporary
' directory to the published location.
'
'This file will be moved out of the working location to the final
' published folder location.
sICSName = ".\Spiceworks.ics"
'
'Force a port integer, if empty, the script will check the httpd.conf
' file and use the default non-ssl port.
iPort = "443"
'
'Set prefix for URL to spiceworks help desk, note if you use a secure
' port above, you will want to use https here.
sPrefix = "https"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set logfile = objFSO.OpenTextFile (sICSName, ForWriting, True)
Function CheckConfigFile(oFile)
Do While oFile.AtEndOfStream <> True
If oFile.AtEndOfStream <> True Then
sLine = oFile.Readline
If instr(lcase(sLine),"listen") then
sPort = trim(replace(lcase(sLine),"listen",""))
CheckConfigFile = sPort
exit function
End If
End If
Loop
oFile.close
End Function
logfile.writeline("BEGIN:VCALENDAR" & vbnewline _
& "PRODID:-//hacksw/handcal//NONSGML v1.0//EN")
logfile.writeline("X-WR-CALNAME:Spiceworks Tickets and Services")
logfile.writeline("X-WR-CALDESC:Spiceworks ticket and services due-dates")
If sRootAddress = "" then
'Get configuration details
sql = "SELECT configuration_0.id, configuration_0.name, configuration_0.value FROM main.configuration configuration_0 WHERE (configuration_0.name Like 'hostname_for_emails')"
Call OpenConnection(sql,"config")
Set HTTPConfig = objFSO.OpenTextFile(sPathToSpiceworks & "\httpd\conf\httpd.conf", ForReading)
If iPort = "" then
sRootAddress = sRootAddress & ":" & CheckConfigFile(HTTPConfig)
Else
sRootAddress = sRootAddress & ":" & iPort
End If
End If
If sTicketCategoryFieldName <> "" then
sCategoryField = ", [tickets]." & sTicketCategoryFieldName
Else
sCategoryField = ""
End If
If igoBackHowManyDays > 0 then
dDateAfter = ConvertDate(DateAdd("d", -iGoBackHowManyDays, Now))
'msgbox dDateAfter
Else
dDateAfter = cDate("2006-01-20")
End If
'wscript.quit
'Get ticket details
SQL = "SELECT [tickets].[id], [users].[email], [tickets].[summary], [tickets].[priority], [tickets].[due_at], [tickets].[description], [tickets].[status], [users].[first_name], [tickets].[created_at], [users].[last_name], [creators].[last_name] || ', ' || [creators].[first_name] AS [created_by]" & sCategoryField & " FROM [tickets] LEFT OUTER JOIN [users] ON [tickets].[assigned_to] = [users].[id] INNER JOIN [users] [creators] ON [tickets].[created_by] = [creators].[id] WHERE [tickets].created_at > " & dDateAfter & " and status = 'open'"
Call OpenConnection(sql,"ticket")
'Get service details
sql = "SELECT A1.id, A1.service_end, V1.name, V1.description, V1.website FROM main.agreements A1, main.vendors V1 WHERE A1.vendor_id = V1.id AND A1.service_end <> ''"
Call OpenConnection(sql,"services")
logfile.writeline("END:VCALENDAR")
logfile.close
on error resume next
objFSO.DeleteFile sPublishToPath & "\" & replace(sICSName,".\","")
on error resume next
objFSO.MoveFile sICSName, sPublishToPath & "\" & replace(sICSName,".\","")
Function AddEscapeCharacters(sString)
sString = replace(sString,vbCr,"")
on error resume next
sString = replace(sString,"\","\\")
sString = replace(sString,chr(10),"\n")
sString = replace(sString,":","\:")
sString = replace(sString,chr(34),"\" & chr(34))
sString = replace(sString,chr(39),"\" & chr(39))
sString = replace(sString,";","\;")
AddEscapeCharacters = sString
End Function 'AddEscapeCharacters
Function OpenConnection(sql,sQueryType)
on error goto 0
'on error goto 0
'oDb.Open(sPathToSpiceworks & "\db\spiceworks_prod.db")
''oStmt.ActiveConnection = oDb
''oStmt.Prepare(sql)
''oStmt.Step()
if err.number <> 0 then
''oStmt.Close()
''oDb.close()
exit function
End if
objConnection.Open "DSN=Spiceworks;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open sql , objConnection, _
adOpenStatic, adLockOptimistic
objRecordSet.MoveFirst
on error resume next
If sQueryType = "ticket" then
While not objRecordset.EOF
sStatus = objRecordset.Fields.Item("status")
If sStatus <> "closed" then
sCategory = ""
sID = objRecordset.Fields.Item("id")
sUIDType = "ticket_" & sID
sAssigned_to = objRecordset.Fields.Item("email")
sAssigned_to_First = objRecordset.Fields.Item("first_name")
sAssigned_to_Last = objRecordset.Fields.Item("last_name")
sSummary = AddEscapeCharacters(objRecordset.Fields.Item("Summary"))
sPriority = objRecordset.Fields.Item("Priority")
sDue_at = objRecordset.Fields.Item("Due_at")
sDescription = objRecordset.Fields.Item("Description")
sCreated_By = objRecordset.Fields.Item("Created_By")
sCreated_at = objRecordset.Fields.Item("Created_at")
on error resume next
sCategory = objRecordset.Fields.Item("category")
If sAssigned_to <> "" then
sDescription = "Assigned to: " & sAssigned_to_First & " " & sAssigned_to_Last & "\n\n" & AddEscapeCharacters(sDescription) & "\n\n" & "Created by: " & sCreated_By
Else
sDescription = AddEscapeCharacters(sDescription) & "\n\n" & "Created by: " & sCreated_By
End if
Select Case sPriority
Case "1"
sPriority = "(Low)"
Case "2"
sPriority = "(Medium)"
Case "3"
sPriority = "(High)"
End Select
'msgbox "Ticket ID: " & sID & vbcrlf & "Assigned to: " & sAssigned_To _
' & vbcrlf & "Summary: " & sSummary _
' & vbcrlf & "Priority: " & sPriority & vbcrlf & "Due date: " & sDue_at _
' & vbcrlf & sDescription
sSubject = SetupSummary(sAssigned_to_First & " " & sAssigned_to_Last,sID,sPriority,sSummary)
'sSubject = "Ticket: " & sID & " " & sAlert & " - " & sPriority & " " & sSummary
sTicketAddress = sRootAddress & "/tickets/list/single_ticket/"
If sDue_at <> "" then
Call CreateMeetingRequest(sID,sSummary,sPriority,sDue_at,sDescription,sAssigned_to_First & " " & sAssigned_to_Last,sSubject,sUIDType,sCategory,sAssigned_to)
ElseIf isnull(sDue_at) Then
Call CreateToDo(sId,sSummary,sPriority,sDescription,sAssigned_to_first & " " & sAssigned_to_Last,sSubject,sUIDType,sCategory,sAssigned_to,sCreated_at)
End If
End if
objRecordset.MoveNext
Wend
ElseIf sQueryType = "config" then
While not objRecordset.EOF
sRootAddress = sPrefix & "://" & objRecordset.Fields.Item("value")
objRecordset.MoveNext
Wend
ElseIf sQueryType = "services" then
While not objRecordset.EOF
sStatus = ""
sID = objRecordset.Fields.Item("id")
sUIDType = "service_" & sID
sTicketAddress = sRootAddress & "/agreements#"
sAssigned_to = sEscalateToGroup
sSummary = AddEscapeCharacters(objRecordset.Fields.Item("name"))
sPriority = ""
sDue_at = objRecordset.Fields.Item("service_end")
sDescription = objRecordset.Fields.Item("website") & "\n\n" & AddEscapeCharacters(objRecordset.Fields.Item("Description"))
sSubject = "Service: " & sSummary & " service end date"
Call CreateMeetingRequest(sID,sSummary,sPriority,sDue_at,sDescription,sAssigned_To,sSubject,sUIDType,"","")
objRecordset.MoveNext
Wend
End If
obRecordSet.Close
objConnection.Close
End Function
Function ConvertDate(sDate)
on error goto 0
sDate = cDate(sDate)
sYear = year(sDate)
sMonth = month(sDate)
if len(sMonth) = 1 then sMonth = "0" & sMonth
sDay = day(sDate)
if len(sDay) = 1 then sDay = "0" & sDay
ConvertDate = sYear & sMonth & sDay
End Function
Sub CreateToDo(sId,sSummary,sPriority,sDescription,sAssigned_to,sSubject,sUIDType,sCategory,sAssigned_to_email,sCreated_at)
Dim sOrganizer
sDateMod = ConvertDate(sCreated_at)
sNextDay = ConvertDate(DateAdd("d", iMakeTicketsDueInHowManyDays, sCreated_at))
If sAssigned_to_email <> "" then
sAttendee = "ATTENDEE;PARTSTAT=ACCEPTED:MAILTO:" & sAssigned_to_email & vbnewline
sAlert = ""
sOrganizer = "ORGANIZER:MAILTO:" & sAssigned_to_email & vbnewline
Else
sOrganizer = ""
sAttendee = ""
sAlert = "**UNASSIGNED** "
End if
'& "DUE:" & sNextDay & "T000000" & vbnewline _
logfile.writeline("BEGIN:VTODO" & vbnewline _
& "DTSTAMP:" & sDateMod & "T000000Z" & vbnewline _
& "DTSTART;VALUE=DATE:" & sDateMod & vbnewline _
& "DTEND;VALUE=DATE:" & sNextDay & vbnewline _
& "SEQUENCE:2" & vbnewline _
& "UID:" & sUIDType & vbnewline _
& sOrganizer _
& sAttendee _
& "STATUS:NEEDS-ACTION" & vbnewline _
& "SUMMARY:" & sAlert & sSubject & vbnewline _
& "CATEGORIES:" & sCategory & vbnewline _
& "DESCRIPTION:" & sTicketAddress & sID & "\n\n" & sDescription & "\n\nAdded by Spiceworks DB2ICAL, " & sScriptver & " \n") & vbnewline _
& "URL:" & sTicketAddress & sID & vbnewline _
& "BEGIN:VALARM" & vbnewline _
& "TRIGGER:-PT12H" & vbnewline _
& "ACTION:DISPLAY" & vbnewline _
& "TRIGGER:-P8H" & vbnewline _
& "END:VALARM" & vbnewline _
& "X-MICROSOFT-CDO-ALLDAYEVENT:FALSE" & vbnewline _
& "END:VTODO"
End Sub
Sub CreateMeetingRequest(sID,sSummary,sPriority,sDue_at,sDescription,sAssigned_To,sSubject,sUIDType,sCategory,sAssigned_to_email)
on error goto 0
sDateMod = ConvertDate(sDue_at)
sNextDay = ConvertDate(DateAdd("d", 1, sDue_at))
If sAssigned_to_email <> "" then
sAttendee = "ATTENDEE;PARTSTAT=ACCEPTED:MAILTO:" & sAssigned_to_email & vbnewline
sAlert = ""
Else
sAlert = "**UNASSIGNED** "
End if
logfile.writeline("BEGIN:VEVENT" & vbnewline & "UID:" & sUIDType & vbnewline _
& "DTSTAMP:" & sDateMod & "T000000Z" & vbnewline _
& "ORGANIZER:MAILTO:" & sEscalateToGroup & vbnewline _
& "DTSTART;VALUE=DATE:" & sDateMod & vbnewline _
& "DTEND;VALUE=DATE:" & sNextDay & vbnewline _
& sAttendee _
& "CATEGORIES:" & sCategory & vbnewline _
& "BEGIN:VALARM" & vbnewline _
& "TRIGGER:-PT12H" & vbnewline _
& "ACTION:DISPLAY" & vbnewline _
& "DESCRIPTION:REMINDER" & vbnewline _
& "END:VALARM" & vbnewline _
& "TRIGGER:-P8H" & vbnewline _
& "SUMMARY:" & sAlert & sSubject & vbnewline _
& "DESCRIPTION:" & sTicketAddress & sID & "\n\n" & sDescription & "\n\nAdded by Spiceworks DB2ICAL, " & sScriptver & " \n")
logfile.writeline("X-MICROSOFT-CDO-ALLDAYEVENT:TRUE")
logfile.writeline("END:VEVENT")
on error goto 0
End Sub
Screenshots
