‎04-13-2015 03:55 PM
SELECT 'INSERT INTO [tsysreports]
([Reportquery]
,[Reporttitle]
,[Sendmail]
,[Mailgroup]
,[Created]
,[LastChanged]
,[CreatedBy]
,[ChangedBy]
,[LastRun]
,[Permissions]
,[Total])
VALUES
('''+Reportquery+''',
'''+Reporttitle+''',
'+coalesce(''''+Convert(NVARCHAR, Sendmail)+'''', 'null')+',
'+coalesce(''''+Mailgroup+'''', 'null')+',
'+coalesce(''''+Convert(NVARCHAR, Created)+'''', 'null')+',
'+coalesce(''''+Convert(NVARCHAR, LastChanged)+'''', 'null')+',
'+coalesce(''''+CreatedBy+'''', 'null')+',
'+coalesce(''''+ChangedBy+'''', 'null')+',
'+coalesce(''''+Convert(NVARCHAR, LastRun)+'''', 'null')+',
'+coalesce(''''+Permissions+'''', 'null')+',
'+coalesce(''''+Convert(NVARCHAR, Total)+'''', 'null')+'
)' TsysReports,
OBJECT_DEFINITION (OBJECT_ID(Reportquery)) [View]
FROM tsysreports
WHERE Created IS NOT NULL
‎05-03-2024 09:01 PM - edited ‎05-03-2024 09:03 PM
I recently had to run @Susan_A 's custom report extraction to repopulate another installation and I have one recommendation to the process.
If you run the report and export it via excel, you can do a search/replace on the "CREATE VIEW dbo.web..." script in column B to get just the SQL code you need to import back into another install of Lansweeper. Of course this is a good way to have a backup of the custom reports you've accumulated over time.
My search/replace string is as follows: Search for "CREATE VIEW dbo.web* AS " and Replace is "" (blank). This gets most or all of the beginning of the script statement and leaves you with the starting "Select Top 1000000" for each report.
Then it's as simple as copy/pasta into new reports. Bada Bing - Bada Boom.
‎11-01-2023 01:57 PM
How would we import this into the new Database?
‎11-01-2023 06:21 PM
Hello, due to the age of this post I recommend searching through our articles here Reporting - Lansweeper Community or request help with a specific report here in a forum post
‎08-13-2015 09:19 PM
‎04-28-2015 11:56 AM
Select Top 1000000 tsysreports.Reporttitle As Title,
OBJECT_DEFINITION(Object_Id(tsysreports.Reportquery)) ViewScript
From tsysreports
Where tsysreports.Created Is Not Null
Order By Title
‎01-29-2019 08:43 PM
Susan.A wrote:
A sample report can be seen below. It lists the display names of custom reports, as well as the scripts used to create the report views.Select Top 1000000 tsysreports.Reporttitle As Title,
OBJECT_DEFINITION(Object_Id(tsysreports.Reportquery)) ViewScript
From tsysreports
Where tsysreports.Created Is Not Null
Order By Title
‎04-27-2015 06:24 PM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now