Some SQLite queries to pull data out of a Spiceworks DB for migration to Freshservice

spiceworks.txt

Not sure if these are still valid, wrote them back in 2015. Might come in handy for somebody.

-------------------------------------------------------------------------------------------------------------
- All comments for a specific ticket

select u.email as created_by, c.body,c.is_public,c.comment_type,c.attachment_location,c.attachment_content_type,c.attachment_name from comments as c
inner join users as u on c.created_by = u.id
where ticket_id = 5500
order by c.created_at
-------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------
- Time spent per user on a specific ticket

select w.created_at,u.email,w.time_spent from ticket_work as w
inner join users as u on w.user_id = u.id
where ticket_id = 5500
order by w.created_at
-------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------
- Get ALL users

select distinct first_name,last_name,email,title from users
where (disabled is null) and ((first_name is not null) and (last_name is not null))
-------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------
- Get users who have opened a ticket in the past 2 years

select distinct first_name,last_name,email,title from users as u
inner join tickets as t on u.id = t.created_by
where (u.disabled is null) and ((u.first_name is not null) and (u.last_name is not null))
        	and (t.created_at > date('now','-2 years'))
order by u.last_name
-------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------
- CSV format export of Tickets for FreshService

    select  t.id as TicketID,
        t.summary as Subject,
        t.description as Description,
        (CASE WHEN t.status = 'open' THEN 2 WHEN t.status = 'waiting' THEN 3 ELSE 4 END) as Status,
        (CASE WHEN t.priority = 1 THEN 3 WHEN t.priority = 2 THEN 2 ELSE 1 END) as Priority,
        c.email as RequesterEmail,
        c.first_name || ' ' || c.last_name as RequesterName,
        c.office_phone as RequesterPhone,
        a.email as ResponderEmail,
        strftime('%m/%d/%Y %H:%M:%S',datetime(t.created_at)) as Created_At,
        strftime('%m/%d/%Y %H:%M:%S',datetime(t.closed_at)) as Closed_At
        from tickets as t
    inner join users as c on c.id = t.created_by
    inner join users as a on a.id = t.assigned_to
-------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------
- CSV format export of Ticket Comments for FreshService

select  c.id as note_id,
        c.ticket_id as ticket_id,
        u.email as note_created_by,
        (CASE WHEN c.is_public = 'f' THEN 'true' WHEN c.is_public = 't' THEN 'false' END) as private,
        strftime('%m/%d/%Y %H:%M:%S',datetime(c.created_at)) as Created_At,
        c.body as note_body
        from comments as c
inner join users as u on c.created_by = u.id

-------------------------------------------------------------------------------------------------------------