Notification

Icon
Error

Export custom reports (SQL Server only) - Generate SQL queries to recreate custom reports on another database. Will not work on SQL Compact.

Posted: Monday, April 13, 2015 2:55:49 PM(UTC)
Filip.V

Filip.V

Member Lansweeper Developer Administration Original PosterPosts: 7
4
Like
Generate SQL queries to recreate your custom reports on another database. This code is designed for SQL Server and will not work on SQL Compact.

Code:
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
friedbad
#1friedbad Member Posts: 7  
posted: 4/27/2015 5:24:09 PM(UTC)
Is there a way to query just the Report Title and Query off in a report? I'm looking for the title and actual sql query (not the webxxxx name) text as a backup for all reports.
Susan.A
#2Susan.A Member Administration Posts: 1,535  
posted: 4/28/2015 10:56:59 AM(UTC)
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.
Code:
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
esr
#3esr Member Alpha Tester Posts: 69  
posted: 8/13/2015 8:19:21 PM(UTC)
Thank you very much-
This short bit of code is worth it's weight in gold!

I'd go so far as to suggest that it should be a default report in Lansweeper.
jmje
#4jmje Member Posts: 21  
posted: 1/29/2019 8:43:13 PM(UTC)
Originally Posted by: Susan.A Go to Quoted Post
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.
Code:
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


Best report ever made. This has saved me so much time. :)

Active Discussions

Lansweeper non-active computer report
by  Apaulcolypse   Go to last post Go to first unread
Last post: Yesterday at 5:16:21 PM(UTC)
Lansweeper Report for showing CPUs below or above
by  GBA Craig  
Go to last post Go to first unread
Last post: 7/18/2019 3:38:41 PM(UTC)
Lansweeper Hardware list
by  GBA Craig   Go to last post Go to first unread
Last post: 7/18/2019 3:34:43 PM(UTC)
Lansweeper Top 10 users submitting tickets
by  LGuth  
Go to last post Go to first unread
Last post: 7/17/2019 9:29:44 PM(UTC)
Lansweeper Microsoft Patch Tuesday Report - July 2019
by  Noobmode   Go to last post Go to first unread
Last post: 7/17/2019 8:48:08 PM(UTC)
Lansweeper Custom Scanning - file not existing
by  Bruce Garoutte  
Go to last post Go to first unread
Last post: 7/17/2019 8:19:50 PM(UTC)
Lansweeper MouseJack vulnerability affected devices report
by  Viper   Go to last post Go to first unread
Last post: 7/16/2019 5:19:14 PM(UTC)
Lansweeper TPM support
by  Sander Eerdekens  
Go to last post Go to first unread
Last post: 7/16/2019 1:38:17 PM(UTC)