Collection Based on OS install Date

nigel.gibb · 11-26-2008, 09:56 AM

Hi,

Can anyone advise on how to create a collection based on OS install Date.

I want to create a collection of PC's that are in the Deployment Stage e.g. OS is less than 8hrs old.

The Reason for this is so I can ensure that my sequestial SWD Tasks designed for install of Core Apps post initial Deployment (DS) only run on PC's that are in the Deployment Stage

Many Thanks

dominique · 11-26-2008, 11:51 AM

Hello,

You could try to create a
Software Management > Application Metering > Application Discovery > Newly Discovered Applications

Clone the report
Add the Guid
Create a dynamic collection on this report

I will give a try this week-end but these are ideas..

Thanks

dominique · 11-26-2008, 11:59 AM

Something like this:

Quote:

SELECT DISTINCT
da.[ProductName] [Application],
i.[Name],
i.[_ResourceGuid] [ResourceGuid],
i.[Domain],
MAX(da.[Discovery Date]) [Discovery Date]
FROM dbo.Evt_AeX_Discovered_Applications da
JOIN dbo.Inv_AeX_AC_Identification i
ON i.[_ResourceGuid] = da.[_ResourceGuid]
WHERE 1 = 1
AND da.[ProductName] Like '%Microsoft® Windows® Operating System%'
AND DATEDIFF([hh,da.[Discovery Date],GETDATE()) <= 8
AND da.[ProductName] IS NOT NULL
AND da.[ProductName] <> ''
GROUP BY
da.[ProductName],
i.[Name],
i.[_ResourceGuid],
i.[Domain]
ORDER BY
i.[Domain],
i.[Name]

Thanks

nigel.gibb · 11-26-2008, 12:24 PM

Thanks for your reply Dom

Test Fails with error: The datediff function requires 3 argument(s)

dominique · 11-26-2008, 12:38 PM

opps sorry my mistake
remove the [ in front of the hh

Quote:

SELECT DISTINCT
da.[ProductName] [Application],
i.[Name],
i.[_ResourceGuid] [ResourceGuid],
i.[Domain],
MAX(da.[Discovery Date]) [Discovery Date]
FROM dbo.Evt_AeX_Discovered_Applications da
JOIN dbo.Inv_AeX_AC_Identification i
ON i.[_ResourceGuid] = da.[_ResourceGuid]
WHERE 1 = 1
AND da.[ProductName] Like '%Microsoft® Windows® Operating System%'
AND DATEDIFF(hh,da.[Discovery Date],GETDATE()) <= 8
AND da.[ProductName] IS NOT NULL
AND da.[ProductName] <> ''
GROUP BY
da.[ProductName],
i.[Name],
i.[_ResourceGuid],
i.[Domain]
ORDER BY
i.[Domain],
i.[Name]

Thanks

nigel.gibb · 11-26-2008, 03:11 PM

I cant seem to get the Syntax right, I anxiously await the results of testing on the above, Thanks

dominique · 11-26-2008, 03:24 PM

Hello,

Which error do you get? For me this one works, so it could be the copy/paste which change some space but otherwise it should be okay.

Which message do you have?

Thanks

andykn · 11-26-2008, 10:01 PM

I think there's a "Client date" field or similar in the AeX_AC_Identification tble in Basic Inventory. This may populate your collections quicker when you use Dom's syntax with this date.

nigel.gibb · 11-30-2008, 03:46 PM

After getting my head around this error...

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

I have this query giving me what on the surface seems like an accurate collection based on the vCOMPUTER create date.

select Guid from vCOMPUTER
WHERE vCOMPUTER.[OS NAME] = 'Microsoft Windows XP'
GROUP BY GUID
HAVING datediff(hh,min(vCOMPUTER.[CreatedDate]),getdate()) <= '8'

I hope to get something more accurate olong the lines of the above working but my SQL knowledge is crap

dominique · 12-01-2008, 05:42 AM

I ran both queries and they both works.
see the screenshot, the first one is giving a list, the result of the second one is empty...

Thanks

dominique · 12-01-2008, 05:50 AM

Quote:

Originally Posted by nigel.gibb

After getting my head around this error...

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

I have this query giving me what on the surface seems like an accurate collection based on the vCOMPUTER create date.

select Guid from vCOMPUTER
WHERE vCOMPUTER.[OS NAME] = 'Microsoft Windows XP'
GROUP BY GUID
HAVING datediff(hh,min(vCOMPUTER.[CreatedDate]),getdate()) <= '8'

I hope to get something more accurate olong the lines of the above working but my SQL knowledge is crap

The error might ciome when you run the Select statement with Created date something like:
[q]
select Guid, [CreatedDate] from vCOMPUTER
WHERE vCOMPUTER.[OS NAME] = 'Microsoft Windows XP'
GROUP BY GUID
HAVING datediff(hh,min(vCOMPUTER.[CreatedDate]),getdate()) <= '8'
[/q]

If so you need to update your query as:
[q]
select Guid, [CreatedDate] from vCOMPUTER
WHERE vCOMPUTER.[OS NAME] = 'Microsoft Windows XP'
GROUP BY GUID, [Created Date]
HAVING datediff(hh,min(vCOMPUTER.[CreatedDate]),getdate()) <= '8'
[/q]

Thanks

nigel.gibb · 12-01-2008, 08:58 AM

It seems the syntax checking in SQL Query Analyser and the NS Collection creator are different.

When I run your query in SQL it works. (but shows no results?)

If I run it in NS Console I get this error..... The condition applied to this collection contains an ORDER BY clause without a corresponding TOP clause. Conditions require the use of both of these clauses, or neither of them to function correctly. Please modify the condition appropriately.

I'm starting to get a headache :-)

dominique · 12-01-2008, 09:29 AM

Hello Nigel,

Remove the order clause and it will work
[q]
SELECT DISTINCT
da.[ProductName] [Application],
i.[Name],
i.[_ResourceGuid] [ResourceGuid],
i.[Domain],
MAX(da.[Discovery Date]) [Discovery Date]
FROM dbo.Evt_AeX_Discovered_Applications da
JOIN dbo.Inv_AeX_AC_Identification i
ON i.[_ResourceGuid] = da.[_ResourceGuid]
WHERE 1 = 1
AND da.[ProductName] Like '%Microsoft® Windows® Operating System%'
AND DATEDIFF(hh,da.[Discovery Date],GETDATE()) <= 8
AND da.[ProductName] IS NOT NULL
AND da.[ProductName] <> ''
GROUP BY
da.[ProductName],
i.[Name],
i.[_ResourceGuid],
i.[Domain]
[/q]

Thanks

nigel.gibb · 12-01-2008, 09:35 AM

That got it.

Thanks for your Help DOM

dominique · 12-01-2008, 09:46 AM