Video Screencast Help

Handy SQL Queries

Created: 26 Mar 2009 • Updated: 07 Jul 2009 | 11 comments
Language Translations
ziggy's picture
+28 28 Votes
Login to vote

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]

Comments 11 CommentsJump to latest comment

KSchroeder's picture

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%

Thanks,
Kyle
Symantec Trusted Advisor

For Forum threads, please click "Mark as Solution" if answered.
For all content, please give a thumbs up if you agree with or support the post.

+18
Login to vote
Symanticus's picture

thanks for the posting sir !

/* Infrastructure Support Engineer */

+4
Login to vote
dijoe's picture

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?

+4
Login to vote
KSchroeder's picture

dijoe,

Try this:
 

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

Thanks,
Kyle
Symantec Trusted Advisor

For Forum threads, please click "Mark as Solution" if answered.
For all content, please give a thumbs up if you agree with or support the post.

+5
Login to vote
andyolly's picture

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

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

+5
Login to vote
KSchroeder's picture

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

Thanks,
Kyle
Symantec Trusted Advisor

For Forum threads, please click "Mark as Solution" if answered.
For all content, please give a thumbs up if you agree with or support the post.

+4
Login to vote
andyolly's picture

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

+3
Login to vote
KSchroeder's picture

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.

Thanks,
Kyle
Symantec Trusted Advisor

For Forum threads, please click "Mark as Solution" if answered.
For all content, please give a thumbs up if you agree with or support the post.

+3
Login to vote
nick_123's picture

Hi andyolly,

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

Thanks....

+1
Login to vote
pragmmativco's picture

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

+1
Login to vote
JeffDG's picture

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

0
Login to vote