June 12, 2012 at 3:10 am
Hi,
the script below returns duplicates, how can i make it return 1 result per machine_name.
SELECT DISTINCT (a.Name0) AS [Machine Name],
b.SiteCode,c.FileVersion AS [IE Version],
d.Operating_System_Name_and0
FROM v_GS_SoftwareFile c INNER JOIN
v_GS_SYSTEM a ON c.ResourceID = a.ResourceID INNER JOIN
v_R_System d ON a.ResourceID = d.ResourceID INNER JOIN
v_FullCollectionMembership b ON a.ResourceID = b.ResourceID
WHERE (c.FileName = 'iexplore.exe') AND (c.FileVersion not like '9.%' )
ORDER BY a.Name0
June 12, 2012 at 3:15 am
Can you provide sample data, ddl statements, expected outcome?
June 12, 2012 at 5:24 am
Bring your results into an CTE also add row_number() to your select and then select from cte where row_number =1 like for example
;with duplicates (rn,one,two,three)
As
(select row_number() over(partition by one,two,three ) as rn,
one,two,three
from Table)
select * FROM duplicates WHERE rn = 1
***The first step is always the hardest *******
June 12, 2012 at 6:13 am
Hi guys this script displays duplicate machine name because it is possible that 1 machine may heve 20 patch levels installed. Now i dont want that, i want to see the distinct version of that patch. for example if you have ie8 installed on the machine, it should not show you the same machine 3 times cause of the three different patches that exists for the same version. ie 8.01111, ie8.011112, ie8.011113
Oh and that CTE hpw do you apply it on this scenario?
June 12, 2012 at 6:15 am
Again provide data and table sturctures and we can help you out (see the second link in my signature if you need help)
June 12, 2012 at 6:45 am
THE-FHA (6/12/2012)
Hi guys this script displays duplicate machine name because it is possible that 1 machine may heve 20 patch levels installed. Now i dont want that, i want to see the distinct version of that patch. for example if you have ie8 installed on the machine, it should not show you the same machine 3 times cause of the three different patches that exists for the same version. ie 8.01111, ie8.011112, ie8.011113Oh and that CTE hpw do you apply it on this scenario?
You should follow Antony's advice, look at his signature, click at its first line wants an answer fast and follow the directions in that page.
You have a design issue here, because if you remove the column [IE Version] from you query you will be stopping duplicates, then why are you including it on the query to begin with (that's a design consideration), once you remove the offending column, it is very likely that the Operating_System_Name_and0 will give you the same dramas, by the way, that's a silly named column as well.
Cheers,
Hope this helps,
Rock from VbCity
June 12, 2012 at 7:05 am
bodged together some sample data to match the what the OP has provided so far and a couple of scripts to get the information out but will need more data especially for none IE processes, as if they want to check MS Office the version number is now 12 (I think) so will need to be left 2 on the first select.
DECLARE @v_gs_softwarefile TABLE (resourceid int, fileversion nvarchar(100), filename nvarchar(100))
DECLARE @v_gs_system TABLE (resourceid int, Name0 nvarchar(100))
DECLARE @v_r_system TABLE (resourceid int, Operating_System_Name_and0 nvarchar(100))
DECLARE @v_fullcollectionmembership TABLE (resourceid int, SiteCode int)
insert into @v_gs_softwarefile values (100,'8.0111111', 'iexplore.exe')
insert into @v_gs_softwarefile values (100,'8.0111112', 'iexplore.exe')
insert into @v_gs_softwarefile values (100,'8.0111113', 'iexplore.exe')
insert into @v_gs_system values (100,'Server1')
insert into @v_r_system values (100,'Windows Server 2008 R2')
insert into @v_fullcollectionmembership values (100,10)
SELECT
DISTINCT
a.Name0 AS [Machine Name],
b.SiteCode,
LEFT(c.FileVersion,1) AS [IE Version],
d.Operating_System_Name_and0
FROM
@v_GS_SoftwareFile c
INNER JOIN
@v_GS_SYSTEM a
ON
c.ResourceID = a.ResourceID
INNER JOIN
@v_R_System d
ON
a.ResourceID = d.ResourceID
INNER JOIN
@v_FullCollectionMembership b
ON
a.ResourceID = b.ResourceID
WHERE
c.FileName = 'iexplore.exe'
AND
c.FileVersion not like '9.%'
ORDER BY
a.Name0
;WITH CTE AS
(
SELECT
ROW_NUMBER() OVER( PARTITION BY a.Name0 ORDER BY c.FileVersion DESC) AS RowNum,
a.Name0 AS [Machine Name],
b.SiteCode,
c.FileVersion AS [IE Version],
d.Operating_System_Name_and0
FROM
@v_GS_SoftwareFile c
INNER JOIN
@v_GS_SYSTEM a
ON
c.ResourceID = a.ResourceID
INNER JOIN
@v_R_System d
ON
a.ResourceID = d.ResourceID
INNER JOIN
@v_FullCollectionMembership b
ON
a.ResourceID = b.ResourceID
WHERE
c.FileName = 'iexplore.exe'
AND
c.FileVersion not like '9.%'
)
SELECT * FROM CTE WHERE RowNum = 1
June 12, 2012 at 7:14 am
Hi,
Thanks for all the effort guys.
Apparently the other reason for this was that One machine had different pathes which it had the software installed so where i just looked at the recent path by applying the where clause and all the duplicates where removed. Thanks a million for the assistance.
June 12, 2012 at 7:15 am
this was the final script fyi
Select a.Name0, b.Operating_System_Name_and0, c.FileVersion, c.filepath
FROM v_GS_SYSTEM AS a
INNER JOIN v_R_System AS b
ON a.ResourceID = b.ResourceID
INNER JOIN v_GS_SoftwareFile as C
ON a.ResourceID = c.ResourceID
Where c.FileName = 'iexplore.exe' AND c.FilePath like '%c:\Program %'
AND c.FileVersion NOT LIKE '%9.0%' AND c.FilePath NOT LIKE '%c:\Program Files (x86)%'
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply