Importing ticket comments, public vs private

[dhoward]

I've been put in charge of establishing the exact procedure by which my organization will move from Spiceworks to Lansweeper (using ethanthekiwi's handy and life-saving guide), but one thing I noticed is that the import template doesn't seem to have a column that differentiates between internal notes and public replies.

Am I missing something, or will we be forced to have all internal notes become publicly viewable replies upon migration?

[dhoward]

I've been in contact with Lansweeper support agents regarding this inquiry; at this point the only way to accomplish this is via manual edits to the database, which cannot be realistically be supported by Lansweeper support agents.

The way I accomplished this was, after exporting from Spiceworks, to use Excel magic to add the string __PRIVATESPICE__ to the body of each comment that was private in Spiceworks. Then, after the import into Lansweeper, manually edit the database to set any comment with __PRIVATESPICE__ present to private, then remove the __PRIVATESPICE__ string from all private notes.

UPDATE htblnotes
SET notetype = 2
WHERE note LIKE '%__PRIVATESPICE__%' AND NOT notetype = 3;
UPDATE htblnotes
SET note = REPLACE(CONVERT(VARCHAR(MAX), note), ' __PRIVATESPICE__', '')
WHERE notetype = 2;

After we finally cutover from Spiceworks to Lansweeper, I'm going to post the full process here. It's heavily based on the guide I linked to above, but with some cool improvements.