→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Filip_V
Lansweeper Alumni
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.

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
7 REPLIES 7
rader
Champion Sweeper III

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.

Alig26
Engaged Sweeper II

How would we import this into the new Database?

Mercedes_O
Community Manager
Community Manager

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 

esr
Champion Sweeper
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.
Susan_A
Lansweeper Alumni
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
jmje
Engaged Sweeper III
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


Best report ever made. This has saved me so much time. 🙂
friedbad
Engaged Sweeper II
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.

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now