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 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,536  
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 local admin users of a specific device
by  kdunnett   Go to last post Go to first unread
Last post: 5/25/2020 9:30:26 PM(UTC)
Lansweeper How to get total disk usage of all VM assets
by  Erik.T  
Go to last post Go to first unread
Last post: 5/25/2020 4:49:58 PM(UTC)
Lansweeper Windows Server 2016 & Patch Tuesday May 2020
by  Hendrik.VE   Go to last post Go to first unread
Last post: 5/22/2020 8:20:05 PM(UTC)
Lansweeper Users mapped "shared" printers
by  Andy.S  
Go to last post Go to first unread
Last post: 5/22/2020 4:16:23 PM(UTC)
Lansweeper Report on Assets in a Static Group
by  Andy.S   Go to last post Go to first unread
Last post: 5/22/2020 2:55:03 PM(UTC)
Lansweeper Windows 10 Activation
by  TruSynergy  
Go to last post Go to first unread
Last post: 5/21/2020 7:54:25 PM(UTC)
Lansweeper Filtering Report Based On Active Status
by  CyberCitizen   Go to last post Go to first unread
Last post: 5/21/2020 4:04:33 AM(UTC)
Lansweeper Windows: Unauthorized Administrators (Built-in)
by  Jackie.L  
Go to last post Go to first unread
Last post: 5/20/2020 8:01:17 PM(UTC)