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,510  
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 Windows 7 Assets, their user, and users manager
by  Jeff Hilmo   Go to last post Go to first unread
Last post: Today at 6:51:55 PM(UTC)
Lansweeper Firefox 67 Critical Security Issue
by  Esben.D  
Go to last post Go to first unread
Last post: Today at 11:40:51 AM(UTC)
Report Center BIOS version check
by  Esben.D   Go to last post Go to first unread
Last post: Yesterday at 1:22:28 PM(UTC)
Lansweeper report in defined period
by  CHIREC  
Go to last post Go to first unread
Last post: Yesterday at 10:46:34 AM(UTC)
Lansweeper Microsoft Patch Tuesday Report - May 2019
by  Hendrik.VE  
Go to last post Go to first unread
Last post: 5/21/2019 8:32:41 AM(UTC)
Lansweeper Patch Tuesday report, last 3 months
by  Esben.D   Go to last post Go to first unread
Last post: 5/21/2019 8:25:30 AM(UTC)
Lansweeper Pc's with SSD
by  Rick I  
Go to last post Go to first unread
Last post: 5/17/2019 6:21:48 PM(UTC)