· 8 years ago · Apr 16, 2018, 01:41 PM
1DECLARE @NAME NVARCHAR(MAX) = 'SPLA Report-SQL Details'
2DECLARE @Description NVARCHAR(MAX) = 'SQL Server details and SAL'
3DECLARE @ColumnList NVARCHAR(MAX) = 'CID,Organization,ServerName,Instance,UserName,ProcessorCount,Product,Lastscan'
4DECLARE @ColVis NVARCHAR(MAX) = '-1,1,1,1,1,1,1,1'
5DECLARE @KeyFieldName NVARCHAR(MAX) = NULL
6DECLARE @RowTargetLink NVARCHAR(MAX) = 'Computer.aspx?id='
7
8DECLARE @SQL NVARCHAR(MAX) = 'CustomReport_SQLdetails'
9
10DELETE FROM tblReport WHERE Name = @NAME
11
12exec StockReportAddUpdate @ReportID=N'00000000-0000-0000-0000-000000000000'
13 ,@ReportType=N'0'
14 ,@Name=@NAME
15 ,@Description=@Description
16 ,@SQLQuery=@SQL
17 ,@ColumnList=@ColumnList
18 ,@GroupList=NULL
19 ,@GroupSummaryList=NULL
20 ,@ColumnVisibility=@ColVis
21 ,@KeyFieldName=@KeyFieldName
22 ,@RowTargetLink=@RowTargetLink
23 ,@KeysCacheID=NULL
24 ,@UsesCustomFields=N'0'
25 ,@CustomFieldCategoryID=NULL
26 ,@TranslateName=NULL
27 ,@TranslateDescription=NULL
28 ,@MultiEditPage=NULL
29 ,@DocumentType=N'0'
30 ,@DocumentParentFieldName=NULL
31 ,@Parameters=NULL
32 ,@ViewName=N'PROCEDURE2'
33 ,@IsStockReport=N'0'
34 ,@IsCustomReport=N'1'
35
36
37
38--INSERT INTO tblreportsecurity
39--SELECT ReportID, 0 FROM tblReport WHERE ReportID = @uid
40
41go
42
43DECLARE @NAME NVARCHAR(MAX) = 'SPLA Report-SQL Summary'
44DECLARE @Description NVARCHAR(MAX) = 'SQL Server details and SAL summary'
45DECLARE @ColumnList NVARCHAR(MAX) = 'CID,Organization,ServerName,SALS,Product,Lastscan'
46DECLARE @ColVis NVARCHAR(MAX) = '-1,1,1,1,1,1'
47DECLARE @KeyFieldName NVARCHAR(MAX) = NULL
48DECLARE @RowTargetLink NVARCHAR(MAX) = 'Computer.aspx?id='
49
50DECLARE @SQL NVARCHAR(MAX) = 'CustomReport_SQLsummary'
51
52DELETE FROM tblReport WHERE Name = @NAME
53
54exec StockReportAddUpdate @ReportID=N'00000000-0000-0000-0000-000000000000'
55 ,@ReportType=N'0'
56 ,@Name=@NAME
57 ,@Description=@Description
58 ,@SQLQuery=@SQL
59 ,@ColumnList=@ColumnList
60 ,@GroupList=NULL
61 ,@GroupSummaryList=NULL
62 ,@ColumnVisibility=@ColVis
63 ,@KeyFieldName=@KeyFieldName
64 ,@RowTargetLink=@RowTargetLink
65 ,@KeysCacheID=NULL
66 ,@UsesCustomFields=N'0'
67 ,@CustomFieldCategoryID=NULL
68 ,@TranslateName=NULL
69 ,@TranslateDescription=NULL
70 ,@MultiEditPage=NULL
71 ,@DocumentType=N'0'
72 ,@DocumentParentFieldName=NULL
73 ,@Parameters=NULL
74 ,@ViewName=N'PROCEDURE2'
75 ,@IsStockReport=N'0'
76 ,@IsCustomReport=N'1'
77
78
79
80--INSERT INTO tblreportsecurity
81--SELECT ReportID, 0 FROM tblReport WHERE ReportID = @uid
82
83
84go
85
86
87use SnowLicenseManager --or snowdb, or SnowInventory
88go
89IF OBJECT_ID ( 'dbo.CustomReport_SQLsummary', 'P' ) IS NOT NULL
90 DROP PROCEDURE dbo.CustomReport_SQLsummary;
91GO
92
93
94CREATE PROCEDURE [dbo].[CustomReport_SQLsummary]
95(
96 @CID integer = 0,
97 @UserID integer = 0,
98 @Parameters nvarchar(255) = NULL
99)
100WITH ENCRYPTION
101AS
102BEGIN
103
104 DECLARE @LOCCID int = @CID
105 DECLARE @ComputerID int
106 DECLARE @servername varchar(250)
107 DECLARE @server varchar(250)
108 DECLARE @Instance varchar(max)
109 DECLARE @ValueName varchar(250)
110 --DECLARE @LOCCID varchar(50)
111 DECLARE @ID int
112 DECLARE @SALS int
113 DECLARE @ProcessorCount int
114 DECLARE @Version varchar(10)
115 DECLARE @ProductLevel varchar(10)
116 DECLARE @ServerProduct varchar(20)
117 DECLARE @ServerType varchar(25)
118 DECLARE @Edition varchar(20)
119 DECLARE @ClusterName varchar(250)
120 DECLARE @ClientCursor CURSOR
121 DECLARE @Org nvarchar(max)
122 DECLARE @SnowName nvarchar(max)
123 DECLARE @Lastscan varchar(20)
124 DECLARE @DelimitedString NVARCHAR(128)
125 DECLARE @SERVERX NVARCHAR(128)
126CREATE TABLE #SQLServer (
127CID int,
128ServerName varchar(255),
129SALS int,
130ProcessorCount int,
131ServerProduct varchar(255),
132ServerType varchar(255),
133ProductLevel varchar(50),
134Edition varchar(255),
135Version varchar(50),
136Org nvarchar(max),
137Instance nvarchar(max),
138ComputerID int,
139SnowName nvarchar(max),
140Lastscan varchar(20)
141)
142--SET @LOCCID ={0}
143DECLARE Client_Cursor CURSOR
144FOR
145 SELECT DISTINCT ValueData FROM tblComputerRegistry where ValueName like '%.servername' AND CID =@LOCCID
146OPEN Client_Cursor
147 FETCH NEXT FROM Client_Cursor INTO @Instance
148 WHILE (@@FETCH_STATUS <> -1)
149 BEGIN
150 Set @server = '['+@Instance+']'
151 SET @servername = (SELECT ValueData from tblComputerRegistry where keyName = 'HKLM:\Software\Snow\Report-SQL' AND ValueName Like '[[]'+@Instance+'].ComputerNamePhysicalNetBIOS')
152 SET @ProcessorCount = (SELECT max(ValueData) from tblComputerRegistry where ValueName = @server+'.Processors' AND CID is not NULL)
153 SET @ServerProduct = (SELECT DISTINCT ValueData from tblComputerRegistry where ValueName = @server+'.Product' AND CID is not NULL)
154 SET @ServerType = (SELECT DISTINCT ValueData from tblComputerRegistry where ValueName = @server+'.ServerType' AND CID is not NULL)
155 SET @ProductLevel = (SELECT DISTINCT ValueData from tblComputerRegistry where ValueName = @server+'.ProductLevel' AND CID is not NULL)
156 SET @Edition = (SELECT DISTINCT ValueData from tblComputerRegistry where ValueName = @server+'.EngineEdition' AND CID is not NULL)
157 SET @Version = (SELECT DISTINCT ValueData from tblComputerRegistry where ValueName = @server+'.Version' AND CID is not NULL)
158 SET @Org = (Select Hierarchy from tblOrganization Where OrgChecksum = (
159Select OrgChecksum from tblComputerInfo where ComputerID = (
160SELECT ComputerID from tblComputer Where HostName = @servername)))
161SET @ComputerID = (SELECT ComputerID from tblComputer Where HostName = @servername)
162SET @Lastscan = (SELECT LastScanDate from tblComputer Where HostName = @servername)
163SET @SALS = (SELECT DISTINCT COUNT(ValueData) from tblComputerRegistry where keyName = 'HKLM:\Software\Snow\Report-SQL' AND ValueName Like '[[]'+@server+'%'+'].Logins(%)' AND ComputerID =@ComputerID)
164SET @SnowName = (SELECT Name as ProductName
165 FROM [SnowLicenseManager].[dbo].[tblComputerApplications] as a
166 right join tblApplication as b on a.ApplicationID = b.ApplicationID
167 Where ComputerID = @ComputerID AND b.Name like 'Microsoft SQL Server 20% Enterprise (reg)'
168 OR ComputerID = @ComputerID AND b.Name like 'Microsoft SQL Server 20% Standard (reg)'
169 OR ComputerID = @ComputerID AND b.Name like 'Microsoft SQL Server 20% Express (reg)'
170 OR ComputerID = @ComputerID AND b.Name like 'Microsoft SQL Server 20% Windows Internal Database (reg)'
171 OR ComputerID = @ComputerID AND b.Name like 'Microsoft SQL Server 20% Enterprise Core (reg)'
172 OR ComputerID = @ComputerID AND b.Name like 'Microsoft SQL Server 20% Developer (reg)')
173 SET @SnowName = (SELECT LEFT(@SnowName, CHARINDEX('(', @SnowName) - 1))
174 IF @Org is not NULL
175 begin
176 IF NOT EXISTS (select * from #SQLServer where Servername = @Servername)
177 BEGIN
178 INSERT INTO #SQLServer (CID,ServerName,SALS,ProcessorCount,ServerProduct,ServerType,ProductLevel,Edition,Version,Org,Instance,ComputerID,SnowName,Lastscan)
179 Values (@LOCCID,@servername,@SALS,@ProcessorCount,@ServerProduct,@ServerType,@ProductLevel,@Edition,@Version,@Org,@Instance,@ComputerID,@SnowName,@Lastscan)
180 end
181 end
182 FETCH NEXT FROM Client_Cursor INTO @Instance
183 END
184 CLOSE Client_Cursor
185 DEALLOCATE Client_Cursor
186 SET NOCOUNT OFF
187SELECT CID,Org as Organization,ServerName,SALS,SnowName as Product,ProcessorCount,ServerType,ProductLevel,Edition,Version,Instance,ComputerID,Lastscan
188 from #SQLServer
189DROP TABLE #SQLServer
190
191
192END
193GO
194
195
196go
197
198
199
200
201use SnowLicenseManager --or snowdb, or SnowInventory
202go
203IF OBJECT_ID ( 'dbo.CustomReport_SQLdetails', 'P' ) IS NOT NULL
204 DROP PROCEDURE dbo.CustomReport_SQLdetails;
205GO
206
207
208CREATE PROCEDURE [dbo].[CustomReport_SQLdetails]
209(
210 @CID integer = 0,
211 @UserID integer = 0,
212 @Parameters nvarchar(255) = NULL
213)
214AS
215BEGIN
216
217 DECLARE @LOCCID int = @CID
218 DECLARE @ComputerID int
219 DECLARE @servername varchar(250)
220 DECLARE @server varchar(250)
221 DECLARE @Instance varchar(max)
222 DECLARE @ValueName varchar(250)
223 --DECLARE @LOCCID varchar(50)
224 DECLARE @ID int
225 DECLARE @SALS int
226 DECLARE @ProcessorCount int
227 DECLARE @Version varchar(10)
228 DECLARE @ProductLevel varchar(10)
229 DECLARE @ServerProduct varchar(20)
230 DECLARE @ServerType varchar(25)
231 DECLARE @Edition varchar(20)
232 DECLARE @ClusterName varchar(250)
233 DECLARE @ClientCursor CURSOR
234 DECLARE @Org nvarchar(max)
235 DECLARE @SnowName nvarchar(max)
236 DECLARE @Lastscan varchar(20)
237 DECLARE @UserName varchar(max)
238 DECLARE @ServerX varchar(max)
239 DECLARE @REGKEY varchar(250)
240CREATE TABLE #SQLServer (
241CID int,
242UserName varchar(max),
243ServerName varchar(255),
244SALS int,
245ProcessorCount int,
246ServerProduct varchar(255),
247ServerType varchar(255),
248ProductLevel varchar(50),
249Edition varchar(255),
250Version varchar(50),
251Org nvarchar(max),
252Instance nvarchar(max),
253ComputerID int,
254SnowName nvarchar(max),
255Lastscan varchar(20)
256)
257--SET @LOCCID={0}
258SET @REGKEY ='HKLM:\Software\Snow\Report-SQL'
259DECLARE Client_Cursor CURSOR
260FOR
261 SELECT DISTINCT ValueData FROM tblComputerRegistry where ValueName like '%.servername' AND CID=@LOCCID AND KEYNAME = @REGKEY
262OPEN Client_Cursor
263 FETCH NEXT FROM Client_Cursor INTO @Instance
264 WHILE (@@FETCH_STATUS <> -1)
265 BEGIN
266 Set @server = '['+@Instance+']'
267 SET @servername = (SELECT ValueData from tblComputerRegistry where keyName = 'HKLM:\Software\Snow\Report-SQL' AND ValueName Like '[[]'+@Instance+'].ComputerNamePhysicalNetBIOS')
268 SET @SALS =(SELECT max(ValueData) from tblComputerRegistry where ValueName = @server+'.Count' AND CID=@LOCCID AND KEYNAME = @REGKEY)
269 SET @ProcessorCount = (SELECT max(ValueData) from tblComputerRegistry where ValueName = @server+'.Processors' AND CID=@LOCCID AND KEYNAME = @REGKEY)
270 SET @ServerProduct = (SELECT DISTINCT ValueData from tblComputerRegistry where ValueName = @server+'.Product' AND CID=@LOCCID AND KEYNAME = @REGKEY)
271 SET @ServerType = (SELECT DISTINCT ValueData from tblComputerRegistry where ValueName = @server+'.ServerType' AND CID=@LOCCID AND KEYNAME = @REGKEY)
272 SET @ProductLevel = (SELECT DISTINCT ValueData from tblComputerRegistry where ValueName = @server+'.ProductLevel' AND CID=@LOCCID AND KEYNAME = @REGKEY)
273 SET @Edition = (SELECT DISTINCT ValueData from tblComputerRegistry where ValueName = @server+'.EngineEdition' AND CID=@LOCCID AND KEYNAME = @REGKEY)
274 SET @Version = (SELECT DISTINCT ValueData from tblComputerRegistry where ValueName = @server+'.Version' AND CID=@LOCCID AND KEYNAME = @REGKEY)
275 SET @Org = (Select Hierarchy from tblOrganization Where OrgChecksum = (
276Select OrgChecksum from tblComputerInfo where ComputerID = (
277SELECT ComputerID from tblComputer Where HostName = @servername)))
278SET @ComputerID = (SELECT ComputerID from tblComputer Where HostName = @servername)
279SET @Lastscan = (SELECT LastScanDate from tblComputer Where HostName = @servername)
280SET @SnowName = (SELECT Name as ProductName
281 FROM [SnowLicenseManager].[dbo].[tblComputerApplications] as a
282 right join tblApplication as b on a.ApplicationID = b.ApplicationID
283 Where ComputerID = @ComputerID AND b.Name like 'Microsoft SQL Server 20% Enterprise (reg)'
284 OR ComputerID = @ComputerID AND b.Name like 'Microsoft SQL Server 20% Standard (reg)'
285 OR ComputerID = @ComputerID AND b.Name like 'Microsoft SQL Server 20% Express (reg)'
286 OR ComputerID = @ComputerID AND b.Name like 'Microsoft SQL Server 20% Windows Internal Database (reg)'
287 OR ComputerID = @ComputerID AND b.Name like 'Microsoft SQL Server 20% Enterprise Core (reg)'
288 OR ComputerID = @ComputerID AND b.Name like 'Microsoft SQL Server 20% Developer (reg)')
289SET @SnowName = (SELECT LEFT(@SnowName, CHARINDEX('(', @SnowName) - 1))
290 SET @Lastscan = (SELECT LastScanDate from tblComputer Where HostName = @servername)
291 IF @Org is not NULL
292 begin
293 --
294 DECLARE USER_Cursor CURSOR
295 FOR
296 SELECT DISTINCT ValueData FROM tblComputerRegistry where ValueName like '%.Login%' AND CID is not NULL
297 OPEN USER_Cursor
298 FETCH NEXT FROM USER_Cursor INTO @UserName
299 WHILE (@@FETCH_STATUS <> -1)
300 BEGIN
301 --
302 SET @ServerX =@server+'.Lo%'
303 SET @ServerX = REPLACE( @ServerX, '[', '[[]');
304 IF (select COUNT(*) from tblComputerRegistry Where KeyName = 'HKLM:\Software\Snow\Report-SQL' AND ValueData = @UserName AND ValueName like @ServerX) > 0
305 begin
306 INSERT INTO #SQLServer (CID,UserName,ServerName,ProcessorCount,ServerProduct,ServerType,ProductLevel,Edition,Version,Org,Instance,ComputerID,SnowName,Lastscan)
307 Values (@LOCCID,@UserName,@servername,@ProcessorCount,@ServerProduct,@ServerType,@ProductLevel,@Edition,@Version,@Org,@Instance,@ComputerID,@SnowName,@Lastscan)
308 END
309 --
310 FETCH NEXT FROM USER_Cursor INTO @UserName
311 END
312 CLOSE USER_Cursor
313 DEALLOCATE USER_Cursor
314 SET NOCOUNT OFF
315 --
316 end
317 FETCH NEXT FROM Client_Cursor INTO @Instance
318 END
319 CLOSE Client_Cursor
320 DEALLOCATE Client_Cursor
321 SET NOCOUNT OFF
322SELECT CID,Org as Organization,ServerName,Instance,UserName,ProcessorCount,SnowName as Product,Lastscan
323 from #SQLServer
324DROP TABLE #SQLServer
325
326
327END
328GO