July 2, 2019 at 4:08 pm
A database was upgraded from 2008 to 2014 sqlserver, a report that was running using COMPUTE to summarize totals quit working, as this function was deprecated with SS 2012.
I wrote this query some years ago, adapted from an Oracle database I was pulling similar information from.
The original query gave nice totals by SystemUser, and was like this:
use AuditDb
go
select
cast(SystemUser as varchar(25)) as "User",
LogonTime as "Date",
cast(HOST_NAME as varchar(25)) as "Workstation",
SPID as "session",
cast(APP_NAME as varchar(55)) as "Program"
from ServerLogonHistory
where LogonTime between '01-jun-19' and '30-jun-19'
and app_name not like 'Microsoft SQL Server VSS Writer'
order by SystemUser, LogonTime
compute count(SPID) by SystemUser
go
I tried to replicate it using ROLLUP with this:
use AuditDb
go
select
cast(SystemUser as varchar(25)) as "User",
LogonTime as "Date",
cast(HOST_NAME as varchar(25)) as "Workstation",
cast(APP_NAME as varchar(55)) as "Program",
SPID as "session",
count(APP_NAME)
from ServerLogonHistory
where LogonTime between '01-jun-19' and '30-jun-19'
and app_name not like 'Microsoft SQL Server VSS Writer'
--order by SystemUser, LogonTime
group by SystemUser, SPID, HOST_NAME,APP_NAME,LogonTime
with rollup
go
I am missing something here, as the results look a lot different. The ROLLUP also did not like the order by clause in front of the group by clause.
What am I doing wrong here?
July 2, 2019 at 4:16 pm
Try this:
select
cast(SystemUser as varchar(25)) as "User",
LogonTime as "Date",
cast(HOST_NAME as varchar(25)) as "Workstation",
SPID as "session",
cast(APP_NAME as varchar(55)) as "Program",
count(SPID) over () AS SystemUserCount /*add this line*/
from ServerLogonHistory
where LogonTime between '01-jun-19' and '30-jun-19'
and app_name not like 'Microsoft SQL Server VSS Writer'
order by SystemUser, LogonTime
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 2, 2019 at 4:22 pm
Thanks for the reply, it gives me total in the added column, but does not sum by SystemUser with subtotals like the query with COMPUTE (SPID) by SystemUser did.
The query using COMPUTE function gave a result set like this, which is what I want to replicate:
User Date Workstation session Program
------------------------- ----------------------- ------------------------- ----------- -------------------------------------------------------
USER1 2019-06-25 08:15:19.067 STATIONBPDEV 60 Microsoft SQL Server Management Studio
USER1 2019-06-25 08:15:19.150 STATIONBPDEV 61 Microsoft SQL Server Management Studio
USER1 2019-06-25 08:15:19.160 STATIONBPDEV 60 Microsoft SQL Server Management Studio
USER1 2019-06-25 08:15:19.240 STATIONBPDEV 62 Microsoft SQL Server Management Studio
USER1 2019-06-25 08:15:19.247 STATIONBPDEV 61 Microsoft SQL Server Management Studio
USER1 2019-06-25 08:15:32.300 STATIONBPDEV 61 Microsoft SQL Server Management Studio
USER1 2019-06-25 08:16:54.440 STATIONBPDEV 62 Microsoft SQL Server Management Studio
USER1 2019-06-25 08:16:54.970 STATIONBPDEV 62 Microsoft SQL Server Management Studio
USER1 2019-06-25 08:16:55.017 STATIONBPDEV 62 Microsoft SQL Server Management Studio
USER1 2019-06-25 08:17:13.227 STATIONBPDEV 62 Microsoft SQL Server Management Studio
USER1 2019-06-25 08:23:10.130 STATIONBPDEV 61 Microsoft SQL Server Management Studio
USER1 2019-06-25 08:23:11.520 STATIONBPDEV 94 Microsoft SQL Server Management Studio
USER1 2019-06-25 08:33:56.957 STATIONBPDEV 75 Microsoft SQL Server Management Studio
USER1 2019-06-25 08:34:01.147 STATIONBPDEV 76 Microsoft SQL Server Management Studio
USER1 2019-06-25 08:34:02.640 STATIONBPDEV 79 Microsoft SQL Server Management Studio
USER1 2019-06-25 08:34:37.830 STATIONBPDEV 75 Microsoft SQL Server Management Studio
USER1 2019-06-25 08:34:41.430 STATIONBPDEV 76 Microsoft SQL Server Management Studio
USER1 2019-06-25 08:34:42.910 STATIONBPDEV 79 Microsoft SQL Server Management Studio
USER1 2019-06-25 08:35:19.333 STATIONBPDEV 75 Microsoft SQL Server Management Studio
USER1 2019-06-25 08:35:23.790 STATIONBPDEV 79 Microsoft SQL Server Management Studio
USER1 2019-06-25 08:35:25.190 STATIONBPDEV 82 Microsoft SQL Server Management Studio
USER1 2019-06-25 09:20:18.350 STATIONBPDEV 67 Microsoft SQL Server Management Studio
USER1 2019-06-25 09:20:22.070 STATIONBPDEV 69 Microsoft SQL Server Management Studio
USER1 2019-06-25 09:20:22.613 STATIONBPDEV 70 Microsoft SQL Server Management Studio
USER1 2019-06-25 09:40:53.530 STATION-SERVER2SQL 75 Microsoft SQL Server Management Studio
USER1 2019-06-25 09:40:54.030 STATION-SERVER2SQL 76 Microsoft SQL Server Management Studio
USER1 2019-06-25 09:41:02.197 STATION-SERVER2SQL 76 Microsoft SQL Server Management Studio
USER1 2019-06-25 09:41:02.443 STATION-SERVER2SQL 77 Microsoft SQL Server Management Studio
USER1 2019-06-25 11:17:48.020 STATION-SERVER2SQL 87 Microsoft SQL Server Management Studio
USER1 2019-06-25 11:17:48.477 STATION-SERVER2SQL 88 Microsoft SQL Server Management Studio
USER1 2019-06-25 11:17:55.657 STATION-SERVER2SQL 88 Microsoft SQL Server Management Studio
USER1 2019-06-25 11:18:00.900 STATION-SERVER2SQL 89 Microsoft SQL Server Management Studio
USER1 2019-06-25 11:18:01.180 STATION-SERVER2SQL 91 Microsoft SQL Server Management Studio
cnt
-----------
33
User Date Workstation session Program
------------------------- ----------------------- ------------------------- ----------- -------------------------------------------------------
USER2 2019-06-25 16:43:29.827 STATIOND08710 74 Microsoft® Windows® Operating System
USER2 2019-06-25 16:43:51.627 STATIOND08710 75 Microsoft® Windows® Operating System
cnt
-----------
2
July 2, 2019 at 5:42 pm
It looks like COMPUTE is returning multiple result sets - and ROLLUP and GROUPING SETS will not reproduce those same results. Either will return a single result set with the totals included in that result set.
If you cannot modify the receiving system to use a single result set - then I think your only solution is to rewrite the procedure to return separate results sets with the expected totals. This could get quite ugly...as you would probably need a query to return the initial results and a dynamic query to return multiple resultsets depending on what is being computed and the data from the initial results.
Personally - I wouldn't even try to get this to work...I would modify the receiving system to use either the detail data provided and summarize within that system - or use ROLLUP/GROUPING SETS and modify the receiving system to parse out the totals.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 2, 2019 at 6:23 pm
I wish they hadn't dropped the compute function, I generate a similar report in Oracle SQLPLUS that's worked with the same code from 9i to current version, using a "break on Y skip 2 compute count of X on Y" before I even issue the select statement.
However I did get it to look somewhat better by using grouping sets as follows:
select
cast(SystemUser as varchar(25)) as "User",
LogonTime as "Date",
cast(HOST_NAME as varchar(25)) as "Workstation",
cast(APP_NAME as varchar(55)) as "Program",
SPID as "session",
count(SPID) as "count"
from ServerLogonHistory
where LogonTime between '01-jun-19' and '30-jun-19'
and app_name not like 'Microsoft SQL Server VSS Writer'
group by grouping sets ((SystemUser, SPID, HOST_NAME,APP_NAME,LogonTime),(SystemUser),());
But instead of a tidy little count below each group I just get a row of nulls and had to add the extra count(SPID) as "count" column:
User Date Workstation session Program Count
------------------------- ----------------------- ------------------------- ----------- -------------------------------------------------------
USER1 2019-06-25 08:15:19.067 STATIONBPDEV 60 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 08:15:19.150 STATIONBPDEV 61 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 08:15:19.160 STATIONBPDEV 60 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 08:15:19.240 STATIONBPDEV 62 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 08:15:19.247 STATIONBPDEV 61 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 08:15:32.300 STATIONBPDEV 61 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 08:16:54.440 STATIONBPDEV 62 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 08:16:54.970 STATIONBPDEV 62 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 08:16:55.017 STATIONBPDEV 62 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 08:17:13.227 STATIONBPDEV 62 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 08:23:10.130 STATIONBPDEV 61 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 08:23:11.520 STATIONBPDEV 94 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 08:33:56.957 STATIONBPDEV 75 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 08:34:01.147 STATIONBPDEV 76 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 08:34:02.640 STATIONBPDEV 79 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 08:34:37.830 STATIONBPDEV 75 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 08:34:41.430 STATIONBPDEV 76 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 08:34:42.910 STATIONBPDEV 79 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 08:35:19.333 STATIONBPDEV 75 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 08:35:23.790 STATIONBPDEV 79 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 08:35:25.190 STATIONBPDEV 82 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 09:20:18.350 STATIONBPDEV 67 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 09:20:22.070 STATIONBPDEV 69 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 09:20:22.613 STATIONBPDEV 70 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 09:40:53.530 STATION-SERVER2SQL 75 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 09:40:54.030 STATION-SERVER2SQL 76 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 09:41:02.197 STATION-SERVER2SQL 76 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 09:41:02.443 STATION-SERVER2SQL 77 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 11:17:48.020 STATION-SERVER2SQL 87 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 11:17:48.477 STATION-SERVER2SQL 88 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 11:17:55.657 STATION-SERVER2SQL 88 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 11:18:00.900 STATION-SERVER2SQL 89 Microsoft SQL Server Management Studio 1
USER1 2019-06-25 11:18:01.180 STATION-SERVER2SQL 91 Microsoft SQL Server Management Studio 1
USER1 NULL NULL NULL NULL 33
USER2 2019-06-25 16:43:29.827 STATIOND08710 74 Microsoft® Windows® Operating System 1
USER2 2019-06-25 16:43:51.627 STATIOND08710 75 Microsoft® Windows® Operating System 1
USER1 NULL NULL NULL NULL 2
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply