Goline Logo

News

  • Route RPKI validation April 1st, 2022
    RPKI is a security framework by which network owners can validate and secure the critical route updates or Border Gateway Protocol (BGP) announcements between public Internet networks. BGP is essentially the central nervous system of the Internet and one of its fundamental building blocks. The main function of BGP is to facilitate efficient routing between Autonomous Systems (AS), by building and maintaining the Internet routing table. The Internet routing table is effectively the navigation system of the Internet and without it, traffic would be unable to flow between its constituent networks. Unfortunately, routing equipment alone cannot distinguish between legitimate and malicious routing announcements,...
  • RIPE – Atlas Anchor February 17th, 2022
    We have become an even more integral part of the RIPE Atlas project by hosting an anchor, a device that allows for latency analysis of traffic between autonomous systems.https://atlas.ripe.net/probes/7073/RIPE Atlas anchors play an integral role in the RIPE Atlas network by acting both as enhanced RIPE Atlas probes with more measurement capacity, as well as regional measurement targets within the greater RIPE Atlas network. Anchors are able to perform many more measurements than a regular RIPE Atlas probe, and the large amount of data they collect is made available to everyone. In addition, anchors act as powerful targets that can...
  • MANRS June 20th, 2020
    GOLINE firmly believes in initiatives to protect networks, improve security and resilience of the global routing system. Therefore we decided to support the MANRS project and join as participants.Mutually Agreed Norms for Routing Security (MANRS) is a global initiative, supported by the Internet Society, that provides crucial fixes to reduce the most common routing threats. MANRS offers specific actions via four programs for Network Operators, Internet Exchange Points, CDN and Cloud Providers, and Equipment Vendors. Requirements for Participation Please read the full MANRS Actions document before applying. You can become a participant if you meet these requirements: You (or your company) support...

MS SQL Server: sp_processi, estensione della sp_who

Alessandro Moia SQL Server 22 June 2022

Estensione della sp_who

Per lanciarla:

Exec sp_processi 'active'

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_processi] Script Date: 24/04/2015 09:47:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_processi] –'active' — 2004/04/15
@loginame sysname = NULL
as
set nocount on
declare
@retcode int
declare
@sidlow varbinary(85)
,@sidhigh varbinary(85)
,@sid1 varbinary(85)
,@spidlow int
,@spidhigh int
declare
@charMaxLenLoginName varchar(6)
,@charMaxLenDBName varchar(6)
,@charMaxLenCPUTime varchar(10)
,@charMaxLenDiskIO varchar(10)
,@charMaxLenHostName varchar(10)
,@charMaxLenProgramName varchar(10)
,@charMaxLenLastBatch varchar(10)
,@charMaxLenCommand varchar(10)
declare
@charsidlow varchar(85)
,@charsidhigh varchar(85)
,@charspidlow varchar(11)
,@charspidhigh varchar(11)
—–per campo dbcc
declare @dbcc varchar(255),
@spid_v varchar(3)
——–
select
@retcode = 0 — 0=good ,1=bad.
——–defaults
select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))
select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))
select
@spidlow = 0
,@spidhigh = 32767
————————————————————–
IF (@loginame IS NULL) –Simple default to all LoginNames.
GOTO LABEL_17PARM1EDITED
——–
– select @sid1 = suser_sid(@loginame)
select @sid1 = null
if exists(select * from master.dbo.syslogins where loginname = @loginame)
select @sid1 = sid from master.dbo.syslogins where loginname = @loginame
IF (@sid1 IS NOT NULL) –Parm is a recognized login name.
begin
select @sidlow = suser_sid(@loginame)
,@sidhigh = suser_sid(@loginame)
GOTO LABEL_17PARM1EDITED
end
——–
IF (lower(@loginame) IN ('active')) –Special action, not sleeping.
begin
select @loginame = lower(@loginame)
GOTO LABEL_17PARM1EDITED
end
——–
IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0) –Is a number.
begin
select
@spidlow = convert(int, @loginame)
,@spidhigh = convert(int, @loginame)
GOTO LABEL_17PARM1EDITED
end
——–
RaisError(15007,-1,-1,@loginame)
select @retcode = 1
GOTO LABEL_86RETURN
LABEL_17PARM1EDITED:
——————– Capture consistent sysprocesses. ——————-
SELECT
spid
,status
,sid
,hostname
,program_name
,cmd
,cpu
,physical_io
,blocked
,dbid
,convert(sysname, rtrim(loginame))
as loginname
,spid as 'spid_sort'
, substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' '
+ substring( convert(varchar,last_batch,113) ,13 ,8 )
as 'last_batch_char'
INTO #tb1_sysprocesses
from master.dbo.sysprocesses (nolock)
——–Screen out any rows?
IF (@loginame IN ('active'))
DELETE #tb1_sysprocesses
where lower(status) IN (
'sleeping'
,'background')
and upper(cmd) IN (
'AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER'
,'TASK MANAGER',
'RESOURCE MONITOR',
'XE TIMER ',
'XE DISPATCHER ',
'LOG WRITER ',
'LOCK MONITOR ',
'SIGNAL HANDLER ',
'TRACE QUEUE TASK',
'BRKR EVENT HNDLR',
'BRKR TASK ',
'CHECKPOINT '
)
and blocked = 0
——–Prepare to dynamically optimize column widths.
Select
@charsidlow = convert(varchar(85),@sidlow)
,@charsidhigh = convert(varchar(85),@sidhigh)
,@charspidlow = convert(varchar,@spidlow)
,@charspidhigh = convert(varchar,@spidhigh)
SELECT
@charMaxLenLoginName =
convert( varchar
,isnull( max( datalength(loginname)) ,5)
)
,@charMaxLenDBName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6)
)
,@charMaxLenCPUTime =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7)
)
,@charMaxLenDiskIO =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6)
)
,@charMaxLenCommand =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7)
)
,@charMaxLenHostName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8)
)
,@charMaxLenProgramName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11)
)
,@charMaxLenLastBatch =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9)
)
from
#tb1_sysprocesses
where
spid >= @spidlow
and spid <= @spidhigh
—Aggiunta di un campo che proviene dalla dbcc inputbuffer
declare @val int
create table #dbcc (c1 varchar(4000),par int,cmd varchar(4000), spid int)
declare q cursor for
select distinct spid
from #tb1_sysprocesses
open q
fetch next from q into @val
WHILE @@FETCH_STATUS=0
BEGIN
set @spid_v = @val
insert into #dbcc (c1,par,cmd) Exec ('dbcc inputbuffer('+@spid_v+')')

update #dbcc
set spid = @val
where spid is null
FETCH NEXT FROM q INTO @val
END
CLOSE q
DEALLOCATE q
——–Output the report.
EXECUTE(
'
SET nocount off
SELECT
SPID = convert(char(5),a.spid)
,Status =
CASE lower(status)
When ''sleeping'' Then lower(status)
Else upper(status)
END
,Login = substring(loginname,1,' + @charMaxLenLoginName + ')
,BlkBy =
CASE isnull(convert(char(5),blocked),''0'')
When ''0'' Then '' .''
Else isnull(convert(char(5),blocked),''0'')
END
,DBName = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')
,Command = substring(a.cmd,1,' + @charMaxLenCommand + ')
,Sql = b.cmd
,CPUTime = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
,DiskIO = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')
,LastBatch = substring(last_batch_char,1,' + @charMaxLenLastBatch + ')
,ProgramName = substring(program_name,1,' + @charMaxLenProgramName + ')
,HostName =
CASE hostname
When Null Then '' .''
When '' '' Then '' .''
Else substring(hostname,1,' + @charMaxLenHostName + ')
END
from
#tb1_sysprocesses a
left join #dbcc b on –Usually DB qualification is needed in exec().
a.spid = b.spid and
a.spid >= ' + @charspidlow + '
and a.spid <= ' + @charspidhigh + '
–and a.spid > 49 and b.cmd is not null
order by Login, substring(last_batch_char,1,' + @charMaxLenLastBatch + '),a.spid
– (Seems always auto sorted.) order by spid_sort
SET nocount on
'
)
/*****AKUNDONE: removed from where-clause in above EXEC sqlstr
sid >= ' + @charsidlow + '
and sid <= ' + @charsidhigh + '
and
**************/
LABEL_86RETURN:
if (object_id('tempdb..#tb1_sysprocesses') is not null)
drop table #tb1_sysprocesses
return @retcode — sp_processi

GO

0 0 votes
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x