SQL table associations

Bennyboy · 12-21-2005, 01:44 AM

Hey People

I'm toying with a small app (if it works I'll let you know

) I'm having trouble associating a SWD Task with a computer. My goal is to be able to see what tasks are available to a machine by simply typing in the IP but I can't find a way to associate a SWD task guid with a computer guid.

Any ideas?

Thanks in advance.

Ben

jperry933 · 12-22-2005, 04:27 PM

Something like this?

select c.name,
c.[ip address],
s._eventTime,
_forward,
EventType,
AdvertisementId,
AdvertisementName,
PackageId,
Time,
Status,
FirstRun,
Message
from dbo.Evt_AeX_SWD_Status s
join vComputer c
on s._resourceguid = c.guid
where c.name = '%CN%'
or c.[ip address] = '%IP%'

You could then create 2 paramaters called CN and IP, use basic parameter types, value type of string.

Bennyboy · 12-22-2005, 06:54 PM

Quote:

Originally Posted by jperry933

Something like this?

select c.name,
c.[ip address],
s._eventTime,
_forward,
EventType,
AdvertisementId,
AdvertisementName,
PackageId,
Time,
Status,
FirstRun,
Message
from dbo.Evt_AeX_SWD_Status s
join vComputer c
on s._resourceguid = c.guid
where c.name = '%CN%'
or c.[ip address] = '%IP%'

You could then create 2 paramaters called CN and IP, use basic parameter types, value type of string.

Thanks very much jperry933

I'm not at work at the moment so I can't check if that's what I'm after but it sure looks promising

I've got 2 weeks off starting today

so I'll let you know how it goes when I get back.

Cheers

Ben

jperry933 · 12-23-2005, 08:15 AM

I made a typo, try this instead. Changed the OR to AND. Otherwise, if you used a wildcard for 1 of the paramaters, the OR would have given you back all entries. The AND will still give you back only the single entry if 1 of the paramaters is left as a wildcard.

select c.name,
c.[ip address],
s._eventTime,
_forward,
EventType,
AdvertisementId,
AdvertisementName,
PackageId,
Time,
Status,
FirstRun,
Message
from dbo.Evt_AeX_SWD_Status s
join vComputer c
on s._resourceguid = c.guid
where c.name = '%CN%'
AND c.[ip address] = '%IP%'

Bennyboy · 01-11-2006, 11:50 PM

Thanks Jperry

That query is working very well up to a point (and I greatly appreciate your help with it). It's limitation is that it you can only use it to see what tasks are available to a machine after the task has some kind of history on the machine. What I'm trying to do is to be able to see what tasks are available to the machine regardless of wether or not the agent has downloaded them. The current query is looking at a history based table ie. Evt_AeX_SWD_Status shows the status of events that have already happened therefore you cannot use it to see all task available to the machine.

Basically what Tasks the machine is able to run, even if they have not been run on the machine before or deployed to the machine yet.

I hope that all made sense...
I've literally spent hours looking for a way to do this and I'm on the verge of giving up. So any one who solves this for me instantly gets hero status in my book!

Cheers

Ben

Bennyboy · 01-12-2006, 08:49 PM

I'm having a bit more luck now with Evt_AeX_SWD_Package but it's still not what I"m trying to achive.

This is unbelievably frustrating. Surely there is a way of knowing exactly what packages are available to a machine, the same way the NS knows which packages to advertise to the client to download...

Oh S&#t. I think I may have just answered my own question

The NS advertises what is available to the client... therefore it stands to reason that there is an advertisement guid of some sort that can associated with package and resource guids.

This post is starting to look more like a braindump rather than a question. Sorry peoples.

Nick · 01-12-2006, 10:28 PM

No apologies needed Bennyboy. Doing what you're doing may jar something loose for someone else to help contribute.

Keep "braindumping" away, especially if it helps you solve the problem.

Bennyboy · 01-13-2006, 02:36 AM

Looks like advertisement table is the way to go here. Next problem is being able to distinguish between a tasks that enable and disabled.

I can't seem to find any entries in any of the tables that show a task as being enable or disabled.

Any ideas??

Tom.Reyntjens · 01-13-2006, 03:39 AM

Try table ItemActive (Two fields : Guid and Enabled: 1 or 0)

LordofthePatch · 01-13-2006, 09:17 AM

You can use that in conjuction with the Software Package resource guid (D109F74C-0D56-400D-B213-C62D71D1EEFC) and the ItemResource table.

Bennyboy · 01-15-2006, 09:58 PM

Thanks very much for all your help guys.

If anyone is interested this SQL (theoretically) will display all enabled tasks for a machine. Changing the originator ID from 1 to 0 differentiates between internal Tasks and manually created tasks.

Combine this with the script that is available (in the scripting section of the forum) for executing Altiris Tasks remotely and you have quite a handy little tool.

SELECT DISTINCT vSWDTask.[Name],
vAdvertisementAppliesToResource.AdvertisementGuid,

vComputer.[Name],

vComputer.[Domain],

SWDAdvertisement._originatorID

FROM

vAdvertisementAppliesToResource

INNER JOIN vComputer ON vAdvertisementAppliesToResource.ResourceGuid = vComputer.Guid

INNER JOIN vswdTask ON vAdvertisementAppliesToResource.AdvertisementGuid = vSWDTask.Guid

INNER JOIN SWDAdvertisement ON vAdvertisementAppliesToResource.AdvertisementGuid = SWDAdvertisement.AdvertisementId

INNER JOIN itemActive ON SWDAdvertisement.AdvertisementId = itemActive.GUID

WHERE

vComputer.[Name] LIKE '%'

AND vComputer.[Domain] LIKE '%'

AND vSWDTask.[Name] LIKE '%%'

AND SWDAdvertisement._originatorID = 1

AND itemActive.Enabled = 1

ORDER BY

vSWDTask.[Name]

Please feel free to check my logic on this one and correct me if I've got something wrong or could do this more efficiantly.

Cheers

Ben

JAustgen · 01-16-2006, 08:52 PM

Thanks, Ben! I'll give this one a shot later.