Symantec Management Platform (Notification Server)

I need to write a query that includes machines from a specific Organizational Group.

Can anyone help me in adding this to my sql?

I'm creating an automation policy based on this.

I will include my sql but I'm really interested in the details of how to get this data from the DB because I will probably want to use it in other cases also.

SELECT w1.Name as 'Name',

t1.[Device ID] as 'Drive',

t1.[Size (Bytes)] as 'Disk Size',

t1.[Free Space (Bytes)] as 'Free Space',

w1.Guid as 'ResourceGuid',

u1.[User] as 'Primary User',

[dca2_AeX AC Identification].[OS Name]

FROM Wrksta w1

INNER JOIN vHWLogicalDisk t1

ON w1.Guid = t1._ResourceGuid

INNER JOIN [Inv_AeX_AC_Identification] AS [dca2_AeX AC Identification]

ON (w1.Guid = [dca2_AeX AC Identification].[_ResourceGuid])

LEFT JOIN Inv_AeX_AC_Primary_User u1

ON w1.Guid = u1._ResourceGuid

WHERE u1.[Month] = DATENAME(mm, GETDATE())

AND LOWER(t1.[Description]) LIKE '%local fixed disk%'

AND t1.[Size (Bytes)] > '2000'

AND t1.[Free Space (Bytes)] < '4000000000'

AND ([dca2_AeX AC Identification].[OS Name] LIKE N'%Server 2003%')

ORDER BY w1.[Name]

In the above I'm trying to watch for low disk space on server 2003 machines but... I have a few non 2003 machines I also have to monitor that I maintain in an Organization group called
'Other Servers'.

I do all my testing in SQL first.  Saves a bucket of time.

Worksta is old - try to avoid it where possible.

You mentioned you're trying this, but not what is happening as a result.  What ARE you getting?

I'm assuming this is NS6.  Is that true?

Thanks!

This is NS7.

I do all my queries in SQL before Altiris also.

What I'm trying to do is get a free space report for all my servers.  We have a couple machines that run XP which we also need this information.  I have them in a group.

If you have a better way to display free space great (not using Worksta), please share.  Not sure where I got the org query but I have modified it to fit my needs.

Either way I still need to know how to add a Group into my query.

First, WorkSta is a deprecated view that was old as of NS6 and only exists now to catch the odd whatever that might have been missed in upgrades.  Use vComputer instead.

Second, to use an alias for a table that is longer than the table name itself almost 100% defeats the purpose of having an alias at all, other than if you're trying to confuse the ... out of someone.

Third, if you use VComputer, the OS Name is already in there, saving one of your joins.  The OS Name is also in wrksta...  hmmm...  Very strange SQL.

The OU is saved in Inv_OU_Membership if you've organized these per AD groups and imported them with the AD Import.  Here's something that got me pretty close to your information, except then you'll have to put some sort of filter on the ou.distinguishedname.  See if this helps:  (PS>  You'll notice I pulled a join or two out of it based on what I believe is already included in the VComputer view)

SELECT v1.[Name],

t1.[Device ID] as 'Drive',

t1.[Size (Bytes)] as 'Disk Size',

t1.[Free Space (Bytes)] as 'Free Space',

v1.Guid as 'ResourceGuid',

v1.[User] as 'Primary User',

v1.[OS Name],

ou.[Distinguished Name]

FROM vComputer v1

Left JOIN vHWLogicalDisk t1 ON v1.Guid = t1._ResourceGuid

JOIN Inv_OU_Membership ou on ou._ResourceGuid = v1.Guid

WHERE LOWER(t1.[Description]) LIKE '%local fixed disk%'

AND t1.[Size (Bytes)] > '2000'

AND t1.[Free Space (Bytes)] < '4000000000'

AND v1.[OS Name] LIKE '%Server 2003%'

ORDER BY v1.Name

Ok here is what I'm using now after looking at what you are doing but again how can I access Group information?  Is this not possible???

 

SELECT DISTINCT v1.[Name],

ir.[Collection Time],

t1.[Device ID] as 'Drive',

t1.[Size (Bytes)] as 'Disk Size',

t1.[Free Space (Bytes)] as 'Free Space',

/* v1.Guid as 'ResourceGuid',

v1.[User] as 'Primary User',

ou.[Distinguished Name], */

v1.[OS Name]

FROM vComputer v1

Left JOIN vHWLogicalDisk t1 ON v1.Guid = t1._ResourceGuid

/* JOIN Inv_OU_Membership ou ON ou._ResourceGuid = v1.Guid */

JOIN dbo.Inv_Inventory_Results ir ON ir._ResourceGuid = v1.Guid

/* LEFT JOIN Inv_AeX_AC_Primary_User u1 ON v1.Guid = u1._ResourceGuid */

WHERE LOWER(t1.[Description]) LIKE '%local fixed disk%'

AND v1.[OS Name] LIKE '%Server 2003%'

AND ir.Agent = 'Inventory Agent'

/* u1.[Month] = DATENAME(mm, GETDATE()) */

/* AND t1.[Size (Bytes)] > '%Drive Size%' */

/* AND t1.[Free Space (Bytes)] < '%Free Space%' */

ORDER BY t1.[Free Space (Bytes)]

 

or the group, assuming that by group you're talking about an AD group.  You can filter on that field as you see fit.

There may be a better way, but I'm not aware of it.  Sorry.

If on the other hand you mean Altiris groups or security groups, then it's totally different.

I mean Altiris Groups.

As in under Manage - Organizational Views and Groups.

How do I include a Group from here?

take out the link to InvOuMembership and the one dataset from that - not needed.  I really thought you meant AD.

Uhhh...  Looking.  BBS  I hope

SELECT v1.[Name],

t1.[Device ID] as 'Drive',

t1.[Size (Bytes)] as 'Disk Size',

t1.[Free Space (Bytes)] as 'Free Space',

v1.Guid as 'ResourceGuid',

v1.[User] as 'Primary User',

v1.[OS Name]

FROM vComputer v1

Left JOIN vHWLogicalDisk t1 ON v1.Guid = t1._ResourceGuid

WHERE LOWER(t1.[Description]) LIKE '%local fixed disk%'

AND t1.[Size (Bytes)] > '2000'

AND t1.[Free Space (Bytes)] < '4000000000'

AND v1.[OS Name] LIKE '%Server 2003%'

AND v1.Guid in (select ResourceGuid from ScopeMembership where ScopeCollectionGuid = 'The-GUID-Of-Your-Group-Goes-Here')

ORDER BY v1.Name

The above added code worked.  Thank you.

final code I'm using:

SELECT DISTINCT v1.[Name],

ir.[Collection Time],

t1.[Device ID] as 'Drive',

t1.[Size (Bytes)] as 'Disk Size',

t1.[Free Space (Bytes)] as 'Free Space',

/* v1.Guid as 'ResourceGuid',

v1.[User] as 'Primary User',

ou.[Distinguished Name], */

v1.[OS Name]

FROM vComputer v1

Left JOIN vHWLogicalDisk t1 ON v1.Guid = t1._ResourceGuid

/* JOIN Inv_OU_Membership ou ON ou._ResourceGuid = v1.Guid */

JOIN dbo.Inv_Inventory_Results ir ON ir._ResourceGuid = v1.Guid

WHERE LOWER(t1.[Description]) LIKE '%local fixed disk%'

AND ir.Agent = 'Inventory Agent'

/* AND t1.[Size (Bytes)] > '%Drive Size%' */

/* AND t1.[Free Space (Bytes)] < '%Free Space%' */

AND (v1.[OS Name] LIKE '%Server 2003%' OR v1.Guid in (select ResourceGuid from ScopeMembership where ScopeCollectionGuid = 'GROUP GUID'))

ORDER BY t1.[Free Space (Bytes)]

Give this a shot.. This will allow you to label the OU by LDAP query or do LIKE statement with Wild Card OU information...

Thank you, i was also searching for a way to do this.
and ths works great!

But i was wondering if it possible to use the name of the organizational group in stead of the GUID?
I was lokking for a table shere the link between the Name and GUID was made, but didn't found it.

any ideas