October 11, 2010 at 1:51 pm
Originally my dataset used to look like this:
NameExe-Count DomainLast Logon UserStart Date
C0370221HANOVERSLE6092010-09-14 09:09:03.000
C0370221HANOVERSLE6092010-09-13 10:35:24.000
C0370221HANOVERSLE6092010-09-13 09:26:39.000
C0370221HANOVERSLE6092010-09-09 10:15:38.000
C0371220HANOVER2010-10-05 07:04:45.000
C0371220HANOVER2010-10-01 06:58:42.000
C0371220HANOVER2010-09-30 07:08:30.000
C0371220HANOVER2010-09-29 06:59:11.000
Now I have it like this:
NameExe-Count DomainLast Logon User
C0370221HANOVERSLE609
C0371220HANOVER
C037211HANOVERJLK487
C0372223HANOVERSLL
C0372721HANOVERTDD488
C0372821HANOVERLXB888
C037331HANOVERCAA326
C0374231HANOVER
C0375313HANOVERMAP129
As soon as I include Start Date field to select and groupby i get the data like first dataset above.
can some one help me with this please? Here is the script below.
use altiris
DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = GETDATE() - 90
SET @EndDate = GETDATE()
select max(t.[Name]) as Asset,
max(t.execution)as [Execution Count],
t.[Domain] AS 'Domain',
t.[Last Logon User] AS 'Last Logon User',
t.[File Name],
t.[File Path],
t.[FileVersion],
t.[Policy Name]
--convert(char(10), t.[Start Date], 101) as [Start Date]
--t.[Start Date]
from
(SELECT
T0.[Name],
COUNT(*) OVER (PARTITION BY T0.[Name]) as Execution,
T1.[Domain] AS 'Domain',
T1.[Last Logon User] AS 'Last Logon User',
T2.[Start Date] AS 'Start Date',
T2.[File Name] AS 'File Name',
T2.[File Path] AS 'File Path',
T2.[FileVersion] AS 'FileVersion',
T2.[Policy Name] AS 'Policy Name'
FROM ( [vResourceEx] T0
INNER JOIN [Inv_AeX_AC_Identification] T1
ON
T0.[Guid] = T1.[_ResourceGuid] )
INNER JOIN [Evt_AeX_Application_Start] T2
ON
T1.[_ResourceGuid] = T2.[_ResourceGuid]
WHERE T2.[Policy Name] = 'Extra - All versions'
AND T2.[Start Date] BETWEEN @StartDate AND @EndDate
AND T0.[ResourceTypeGuid] = '493435f7-3b17-4c4c-b07f-c23e7ab7781f'
Group by
T0.[Name],
T1.[Domain],
T1.[Last Logon User],
T2.[Start Date],
T2.[File Name],
T2.[File Path],
T2.[FileVersion],
T2.[Policy Name])t
group by
t.[Name],
t.Execution,
t.[Domain],
t.[Last Logon User],
t.[File Name],
t.[File Path],
t.[FileVersion],
t.[Policy Name]
--t.[Start Date]
order by
t.[Name]
--t.[Start Date] desc
October 11, 2010 at 2:23 pm
sbrochu
To help others help you, please post your table definition, sample data, required result and the code you have run following the directions contained in the first link in my signature block. For example:
CREATE TABLE #T ([Name] VARCHAR(10), ExeCount INT, Domain VARCHAR(10),
[Last Logon User] VARCHAR(10), [Start Date] DATETIME )
INSERT INTO #T
SELECT 'C03702', 21,'HANOVER','SLE609','2010-09-14 09:09:03.000'
October 12, 2010 at 6:38 am
Ron,
Can you read my original post, I am not having trouble when I include "startdate" in my select statement. It gives me the first dataset with duplicates. I want it to return the second dataset with out duplicates. Please see my sample dataset from my original post.
October 12, 2010 at 6:47 am
I totally agree with bitbucket.
Please read the first link in his signature.
Your need to make your question much clearer.
At a minimum you need to post sample data in the form of INSERT statements, and you need to post the expected result based on your sample data.
October 12, 2010 at 6:54 am
The "start date" field data is like this in the database.
StartDate Internal Name FileName
2010-09-13 09:22:21.000SignIn.exesignin.exe
2010-09-13 09:23:53.000EXTRAextra.exe
2010-09-13 09:23:53.000EXTRAextra.exe
2010-09-13 11:33:41.000ConsoleOneconsoleone.exe
2010-09-13 14:57:25.000MSACCESSmsaccess.exe
2010-09-13 17:59:36.000CSAcsacontrol.exe
2010-09-14 07:54:24.000SignIn.exesignin.exe
2010-09-14 07:55:43.000EXTRAextra.exe
2010-09-14 07:55:43.000EXTRAextra.exe
2010-09-14 08:35:30.000Microsoft PowerPoint*powerpnt.exe
I was and am able to extract the "StartDate" field. but, with latest version of my script, the dataset changes when I include "StartDate" field. It's back to all duplicate records is what I am saying.
October 12, 2010 at 6:57 am
I want my dataset to be
Name Exe-Count Domain Last Logon User Start Date
C03702 21 HANOVER SLE609 2010-09-14 09:09:03.000
C03712 20 HANOVER 2010-10-05 07:04:45.000
Instead of
Name Exe-Count Domain Last Logon User Start Date
C03702 21 HANOVER SLE609 2010-09-14 09:09:03.000
C03702 21 HANOVER SLE609 2010-09-13 10:35:24.000
C03702 21 HANOVER SLE609 2010-09-13 09:26:39.000
C03702 21 HANOVER SLE609 2010-09-09 10:15:38.000
C03712 20 HANOVER 2010-10-05 07:04:45.000
C03712 20 HANOVER 2010-10-01 06:58:42.000
C03712 20 HANOVER 2010-09-30 07:08:30.000
C03712 20 HANOVER 2010-09-29 06:59:11.000
October 12, 2010 at 7:05 am
Well if you include the start date in the group by, it'll output a row per different start date.
So it's working the way it's supposed to work ...
To resolve your problem, remove start date from the group by and do a max(startdate) in your select.
October 12, 2010 at 8:07 am
It works thank you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply