March 1, 2019 at 9:45 am
I want to insert Distinct Loginame into table how can I write a query to do
--drop table [dbo.Distinct_Login]
CREATE TABLE [dbo].[dbo.Distinct_Login](
[Today] [datetime] NOT NULL,
[DatabaseName] [nvarchar](128) NULL,
[lOGIN_TIME] [datetime] NOT NULL,
[HOSTNAME] [nchar](128) NOT NULL,
[CMD] [nchar](16) NOT NULL,
[NT_USERNAME] [nchar](128) NOT NULL,
[LOGINAME] [nchar](128) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo.Distinct_Login]
select GETDATE() AS today, DB_NAME(dbid)AS DatabaseName,lOGIN_TIME, HOSTNAME,CMD,NT_USERNAME,LOGINAME from MASTER.DBO.Sysprocesses where spid > 25
March 1, 2019 at 9:56 am
INSERT INTO [dbo.Distinct_Login]
select GETDATE(), DB_NAME(dbid)AS DatabaseName, MAX(LOGIN_TIME), HOSTNAME,CMD,NT_USERNAME,LOGINAME
from MASTER.DBO.Sysprocesses where spid > 25
group by DB_NAME(dbid),HOSTNAME,CMD,NT_USERNAME,LOGINAME
March 1, 2019 at 10:01 am
I am not getting Distinct Loginame with the solution you provided.
March 1, 2019 at 10:16 am
Nita Reddy - Friday, March 1, 2019 10:01 AMI am not getting Distinct Loginame with the solution you provided.
Some of the columns must be different then.
Run this:select GETDATE(), DB_NAME(dbid)AS DatabaseName, MAX(LOGIN_TIME), HOSTNAME,CMD,NT_USERNAME,LOGINAME
from MASTER.DBO.Sysprocesses where spid > 25
group by DB_NAME(dbid),HOSTNAME,CMD,NT_USERNAME,LOGINAME
Just select MAX(ColName) for those and remove them from the group by clause.
March 1, 2019 at 10:30 am
That's an odd requirement. The same login could have several different processes active on SQL at the same time. How do you want to determine which single one to list? The last one, so that you see the most recent activity, I guess? Is that correct?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 1, 2019 at 10:30 am
basically I am trying to get only one Login for each person not matter how many host or CMD he has connected
March 1, 2019 at 10:38 am
My requirement to keep track of how many logins we have in total in entire server, so I want to get any distinct Login name with othew column.
March 1, 2019 at 11:16 am
INSERT INTO [dbo.Distinct_Login] (
[Today], [DatabaseName], [lOGIN_TIME], [HOSTNAME],
[CMD], [NT_USERNAME], [LOGINAME]
)
select
GETDATE() AS today, DB_NAME(dbid) AS DatabaseName, LOGIN_TIME, HOSTNAME,
CMD, NT_USERNAME, LOGINAME
from (
select *, row_number() over(partition by LOGINNAME order by LOGIN_TIME) as row_num
from MASTER.DBO.Sysprocesses
where spid > 25
) as derived
where row_num = 1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply