Altiris Technical Advisory Board Group

Back to Library

Handy SQL Queries 

Mar 26, 2009 12:35 PM

Like many others, I found the current report builder for 6.5 to be a little hard to use. So, out of necessity, I learned SQL the hard way.

With that, I will share here, some of the queries that I have found to help me tremendously. Please note that I am using some real life examples, so you may need to tweak some of the data that is matching in the queries:

Find SWD Package if I know the GUID:

SELECT
a.name [Advertisement Name],
pg.name [Program Name]
FROM
swdadvertisement a
JOIN
swdprogram pg
ON
a.programid = pg.programid
WHERE
pg.packageid = '315A773A-1F7F-4910-AD90-E7346B19CA78'

Find results from SWD job:

SELECT
vc.[Name],
vc.[OS Name],
swd.[AdvertisementName],
swd.[Start],
swd.[End],
swd.[Status],
swd.[ReturnCode]
FROM
Inv_AeX_SWD_Execution_Summary swd
JOIN
vComputer vc
ON
swd._ResourceGuid = vc.Guid
WHERE
[advertisementname] like '%SQL_Native_Client%'

Restrict results to a specific collection:

SELECT
vc.[Guid],
vc.[Name]
FROM
vComputerEx vc
join [Inv_AeX_SW_Audit_Software] sw
ON
vc.[Guid]=sw.[_ResourceGuid]
JOIN
CollectionMembership cm
ON
vc.[Guid]=cm.[ResourceGuid]
WHERE
sw.[File Name]='patrolagent.exe'
AND
sw.[ProductVersion] like '3.7.0%'
AND
vc.[IsManaged]='1'
AND
collectionguid='4ec2f9d8-6a8d-4803-9818-760804a77a30'

Find computers that have been deleted in last N=1 days:

SELECT
itemname AS 'Computer Name',
deleteddate AS 'Deleted Date'
FROM
itemdeleted
WHERE
itemclassguid = '539626D8-A35A-47EB-8B4A-64D3DA110D01'
AND
datediff(day, deleteddate, getdate()) <=1

Find last time computer checked in:

SELECT
*
FROM
evt_NS_client_config_request
WHERE
resourceguid like 'PlaceMachineGuidHere'

Last time computer sent Basic Inventory:

SELECT
vc.name,
vc.domain,
vc.[os name],
rus.[ModifiedDate] AS 'Last Basic Inventory'
FROM
vComputer vc
JOIN
ResourceUpdateSummary rus
ON
vc.Guid = rus.ResourceGuid
AND rus.InventoryClassGuid = 'C74002B6-C7B9-47BB-A5D6-3031AF73BB8D'
JOIN
collectionmembership cm
ON
vc.guid = cm.resourceGuid
AND
cm.CollectionGuid = '75dfb8bb-9695-48b5-9539-053443007211'

Find when specific Data Class was last updated:

SELECT
i.Name, rus.ModifiedDate
FROM
DataClass dc
JOIN
ResourceUpdateSummary rus
ON
dc.Guid=rus.InventoryClassGuid
JOIN
Item i
ON
rus.ResourceGuid=i.Guid
WHERE
dc.Name='AeX OS Operating System'

And the best yet for many reasons, such as Disaster Recovery, or building an identical NS or a Test NS and you want to ensure you have the same solutions and their versions on each NS:

SELECT
scu.name as [Installed Solutions],
substring ( i.state, charindex('', i.state) + 12, (charindex('',i.state) - charindex('',i.state) - 12)) AS [Product Version]
FROM
item i
INNER JOIN solutioncenterupdate scu
ON
i.guid = scu.guid
WHERE
scu.installed = 1
AND
i.state like '%%'
ORDER BY
[Installed Solutions]

Statistics
0 Favorited
9 Views
0 Files
0 Shares
0 Downloads

Tags and Keywords

Comments

Migration User
Dec 15, 2009 09:38 AM

SELECT
*
FROM
dbo.vComputerExcludingIPInfo
WHERE name LIKE 'gum%'

Migration User
Jun 10, 2009 12:40 AM

Anyone know how to create a sql query (NS7) to find all computers that start with "GUM" and add these to a filter folder

Thanks

Migration User
May 09, 2009 11:14 AM

Hi andyolly,

The query u posted for drop downs, really helped me.....

Thanks....

KSchroeder
Apr 23, 2009 02:23 PM

OK, this worked for me:

SELECT '--All--' AS [Version], '%' AS [Version2]

UNION

SELECT
[Version] AS [Version],
[Version] AS [Version2]
FROM [Inv_AeX_OS_Add_Remove_Programs] T1

WHERE [Name] like '%Office Communicator%'
ORDER BY [Version2]

In this case at least the % sorts higher than a number.

Migration User
Apr 23, 2009 10:44 AM

Hi Kyle,

Thanks for that, I've just tried it but the only thing is that the all entry appears at the bottom of the list instead of the default enrty.

Is there any way of changing your script to put the all statement at the top of the list?

Andy

KSchroeder
Apr 23, 2009 10:25 AM

Andy,
I have a bit of a simpler way to do this using the UNION operator..in the parameter query, do something like:
SELECT '--All--' AS [Version], '%' AS [Version2]
 
UNION
SELECT distinct
T1.[Version] AS [Version],
T1.[Version] AS [Version2]
FROM [Inv_AeX_OS_Add_Remove_Programs] T1

WHERE T1.[Name] like '%Office Communicator%'
ORDER BY T1.[Name] ASC

Migration User
Apr 23, 2009 04:55 AM

There are times when you use a table that you want ot use as a drop-down in a report but still want to have the default as bringing back all results first?

Using this query below will create a temporary table that will take advantage of the Hidden and visable fields in a drop down.

To change what information you are wanting to have in the drop-down just replace the highlighted part of the query with what ever you want and you will have the first enry of the drop down as 'All' with the background entry as '%'


---------------------------------------------------------------------------------------
declare @all nvarchar(50)

if(object_id('tempdb..#swversion') is not null)
begin
drop table #swversion
end

--prepare a temp table for the version values
create table #swversion
(
VisVer nvarchar(50),
HidVer nvarchar(50)
)
insert into #swversion
values ('All','%')

insert #swversion (visver,Hidver)

SELECT distinct
T1.[Version] AS 'Version',
T1.[Version] AS 'Version2'
FROM [Inv_AeX_OS_Add_Remove_Programs] T1

WHERE T1.[Name] like '%Office Communicator%'



select * from #swversion

drop table #swversion

----------------------------------------------------------------------------------------

KSchroeder
Apr 22, 2009 04:43 PM

dijoe,

Try this:
 

SELECT vcol.Name, vCol.Guid
FROM vCollection vcol
WHERE vcol.Guid = '<your guid here>'

Migration User
Apr 22, 2009 04:20 PM

Thanks...I don't have the time to teach myself SQL, so having this type of info handy is helpful.

Can you provide a SQL to find a collection if I have the GUID?

Migration User
Apr 21, 2009 07:59 PM

thanks for the posting sir !

KSchroeder
Mar 26, 2009 12:24 PM

is when you use report parameters to supply some of the details.  For instance, I don't know what all my package GUIDs are, so you can add a Dropdown parameter type with a "List type:" of Query Results, and value type of "String" to find all your custom packages (my code below shows only user-created SWD packages, based on the ClassGuid):
SELECT DISTINCT [Name], [Guid]
FROM vSWDPackage pack
WHERE pack.[ClassGuid] = '3DA89D2F-A4D8-4B3E-9FB7-CDA3BFC0B9CE'
AND [Name] NOT IN ('Some Example Package', 'Another Example')
ORDER BY [Name] ASC
If you name the parameter "_PackageID", then in your code example, you put: 
WHERE
pg.packageid = %_PackageID%

Related Entries and Links

No Related Resource entered.