Accessing SVS Information from the NS Database
Especially for anyone wanting to build or modify reports or notification policies in the NS Console, Brian Mann describes the data structure and how the data gets from client to server. He also describes and gives examples of accessing the data.
The Software Virtualization Solution (SVS) that plugs into the Altiris Notification Server (NS Server) allows you to centrally manage your virtual applications, and can also provide an excellent source of information about your applications. Every SVS agent comes ready to provide information to a Notification Server to make it easy on you when it comes time to report on the status of your applications. The Solution ships with a report pack that gives you easy access to the data, and will get you through most of your daily tasks, but for those of you who enjoy digging through the data, and knowing how it all works, here it is:
The SVS agent will send data to an NS server only when an NS Agent is installed. As soon as it detects an NS Agent, it will start forwarding events and inventory every time that there are changes on the SVS machine. For example... when you deploy a layer to the machine, you will get an event when it is imported, as well as when it is activated, and you will also receive them for deactivation, reset, capture and delete. These are sent in an XML format through the NS agent to the NS Server; these are called NSE files. You can also force the SVS Agent to send inventory through the NS Agent from the command line. The syntax is:
SVSCMD SEND
Here is a sample SVS inventory NSE file:
<?xml version='1.0' ?>
<XmlData>
<WbemEventObject class='CE_NfySvrRemoteEvent'>
<MessageID></MessageID>
<Resource guid='{1DBD12EB-BD72-46B4-A5A2-86B2B8CA7D93}' typeGuid='493435F7-3B17-4C4C-B07F-C23E7AB7781F'>
<Attribute name='Domain' value='DOMAINNAME'/>
<Attribute name='Name' value='MACHINE NAME'/>
<Attribute name='Altkey1' value=''/>
<Attribute name='Altkey2' value='00-12-3F-3C-43-14'/>
</Resource>
<ScenarioGUID>1592B913-72F3-4C36-91D2-D4EDA21D2F96</ScenarioGUID>
<message>
<from>
<resource typeGuid="493435F7-3B17-4C4C-B07F-C23E7AB7781F" guid="1DBD12EB-BD72-46B4-A5A2-86B2B8CA7D93" />
</from>
<to>1592B913-72F3-4C36-91D2-D4EDA21D2F96</to>
<body>
<inventory>
<dataClass guid="A8F3A5C2-86D7-46CC-9434-3F4D4C16DD6B">
<data>
<resource>
<row c0="a591128c-1ff4-4e27-be5b-15865ffcf0f1" c1="Office2003" c2="Active" c3="Application" c4="2.0" c5="2005-12-06 15:24:02" c6="2006-02-13 10:12:32" c7="2006-02-03 17:41:01" c8="1" />
<row c0="4d8d007e-215b-4be0-817b-2f7f844dbb89" c1="Adobe_7" c2="Active" c3="Application" c4="2.0" c5="2005-12-06 15:54:46" c6="2006-02-13 10:12:32" c7="2006-01-20 08:49:33" c8="1" />
<row c0="7104e5d2-1edd-4d0d-af33-5a4519880e21" c1="MSN Messenger 7.5" c2="Active" c3="Application" c4="2.0" c5="2005-12-02 09:45:51" c6="2006-02-13 10:12:32" c7="2006-02-06 12:32:26" c8="1" />
<row c0="3fcc196a-eef3-4401-b00b-576de03831ed" c1="Aclient 6.5.233" c2="Inactive" c3="Application" c4="2.0" c5="2006-01-27 17:23:26" c6="2006-02-06 12:34:08" c7="2006-01-27 17:32:43" c8="0" />
<row c0="7f5837bb-dd3a-47d7-9ba8-5ca89e318925" c1="Winzip 10.0" c2="Active" c3="Application" c4="2.0" c5="2005-12-02 13:01:09" c6="2006-02-13 10:12:32" c7="2006-02-10 12:40:49" c8="1" />
<row c0="f10d1266-7bd0-47d5-96df-df57dc666ca9" c1="OneNote 2003 Pro SP2" c2="Active" c3="Application" c4="2.0" c5="2005-12-15 17:36:40" c6="2006-02-13 10:12:32" c7="" c8="1" />
</resource>
</data>
</dataClass>
</inventory>
</body>
</message>
<TimeGenerated>20060213215512.280000+420</TimeGenerated>
<MessageBody></MessageBody>
<MessageLabel></MessageLabel>
<TransportFlag></TransportFlag>
<TransactionCookie></TransactionCookie>
</WbemEventObject>
</XmlData>
Here is a Sample Event NSE:
<?xml version='1.0' ?>
<XmlData>
<WbemEventObject class='CE_NfySvrRemoteEvent'>
<MessageID></MessageID>
<Resource guid='{1DBD12EB-BD72-46B4-A5A2-86B2B8CA7D93}' typeGuid='493435F7-3B17-4C4C-B07F-C23E7AB7781F'>
<Attribute name='Domain' value='ALTIRIS'/>
<Attribute name='Name' value='BMANN'/>
<Attribute name='Altkey1' value=''/>
<Attribute name='Altkey2' value='00-12-3F-3C-43-14'/>
</Resource>
<ScenarioGUID>099C620F-49FB-44F0-9DF8-E9214CB095F5</ScenarioGUID>
<ScenarioGUID>{099C620F-49FB-44F0-9DF8-E9214CB095F5}</ScenarioGUID>
<body>
<SVEvent>
<C0>{4d8d007e-215b-4be0-817b-2f7f844dbb89}</C0>
<C1>Adobe_7</C1>
<C2>Deactivate</C2>
<C3>Success.</C3>
<C4></C4>
</SVEvent>
</body>
<TimeGenerated>20060213232201.026000+420</TimeGenerated>
<MessageBody></MessageBody>
<MessageLabel></MessageLabel>
<TransportFlag></TransportFlag>
<TransactionCookie></TransactionCookie>
</WbemEventObject>
</XmlData>
The XML includes a Resource GUID used in most of the Altiris Database tables for primary lookup, a Dataclass GUID which tells the NS which table to put the data into, and some column aliases C0 - C8 that correspond with column names in the table. SVS has two data classes that it uses to store the data sent from the SVS Agent EVT_Software_Virtualization_Events for the event information and INV_Software_Virtualization_Status for inventory information. References to the data classes are stored in the Altiris Database in the dataclass table; to see them you can run a query such as:
SELECT * FROM dataclass
Then you can lookup the ID from there and query dataclassattribute or join to dataclassattribute which stores the column names and their aliases used in the NSE XML.
SELECT * FROM dataclass JOIN dataclassattribute ON dataclass.id = dataclassattribute.InvClassid
*You should add filters to this when run on a production server to avoid putting a heavy load on your NS Server.
Now that it is understood how the data is sent and stored, let's talk about how to turn this data into usable information by using the two tables discussed above and a few of the built in NS tables.
The EVT_Software_Virtualization_Events table contains the following columns:
- id - Auto Generated Primary Key
- _ResourceGuid - The GUID of the computer that sent the event
- _EventTime - The time the event was generated
- _forward - Indicates whether this is forwarded to a reporting NS
- Layer GUID - The GUID of the Layer from which the event was generated
- Layer Name - The name of the layer
- CommandName - The action that was taken on the layer
- Status - Shows success or failure of the action
- ErrorDescription - a description of the error generally an exit code
In order to make this useful you will generally also want a machine name, domain, or possible a primary user of the computer. In order to get this data you will need to use some NS tables. Some good tables (views) to use to get this data are:
- VComputer - Computer Information
- VCollection - List of all collections
- VUser - User Information
- CollectionMembership - Links computers to the collections they are part of
Most of these are joined using the computer GUID.
Here are some examples:
To Show Failures:
SELECT v.name AS [Computer Name] ,svse.[layer Name] AS [Layer Name] ,svse.CommandName AS [Command] ,CONVERT(CHAR(10),CAST(svse._eventTime AS SMALLDATETIME),101) AS [Date] ,CONVERT(CHAR(10),CAST(svse._eventTime AS SMALLDATETIME),108) AS [Time] ,svse.status AS [Status] ,svse.ErrorDescription AS [Exit Code] FROM Evt_Software_Virtualization_Events svse JOIN vcomputer v ON svse._ResourceGuid = v.Guid WHERE svse.status NOT LIKE 'Success%' AND svse._eventTime > GETDATE() -30 --This is where you would add your additional filters ORDER BY svse._eventTime DESC
This will display all of the events that have come back to the server with a status other than Success. I have added a wildcard to the success status 'Success%' because on some of the beta builds there has been a trailing '.', and not on others. This will account for both situations. The same rule applies to 'Failed'. Until you know what data you have, use 'Failed%'. Another tip when using SVS data from an Altiris Database is that anytime you have a column name with a space you should enclose it in brackets [] – otherwise, SQL will not properly handle the statement. I have also used some coverts/casts in the query only to make the dates more readable. Anyone familiar with DATETIME, and SMALLDATETIME datatypes in SQL will understand. These are in no way manditory, but most people do not use milliseconds in business reports. I have also joined to vcomputer which allows me to get a computer name instead of just a GUID. Vcomputer can also give you a primary user, domain, OS, SP etc...
Once you are comfortable querying these tables you can use the Notification Server to build reports or notification policies using these queries.
The INV_Software_Virtualization_Status table contains the following information:
- _id - Auto Generated Primary Key
- _ResourceGuid - Computer GUID
- VSPName - Layer Name
- Active - Displays the status of the layer (Active/Inactive)
- Type -- Type of layer (Application/Data)
- Layer_Version - The version of SVS that the layer was created on
- Created - When the layer was created
- Last_Activated_Time - When the layer was last activated
- Last_Refreshed_Time - When the layer was last Reset
- VSPGuid - The layer Guid
Here is a sample query using Inventory information:
SELECT v.name AS [Computer Name] ,svsi.VSPName AS [Layer Name] ,svsi.Active AS [Active] ,svsi.Type AS [Type] ,svsi.Created AS [Created] ,svsi.LASt_Activated_Time AS [LASt Activated] ,svsi.LASt_Refreshed_Time AS [LASt Refreshed] ,svsi.Activate_On_Start AS [Active On Start] FROM Inv_Software_virtualization_Status svsi JOIN vcomputer v ON svsi._ResourceGuid = v.Guid /*WHERE --This is where you would add your filters */ ORDER BY 1
This will display the inventory information from your computers, ordered by computer name (column 1 in the results). I have also used aliases to avoid repetitive typing – that is what the svsi. and v. mean in the query. You can see where both aliases were made in the FROM statement of the query. Again I have joined to the vcomputer view to get the machine information to make the information more usable.
Another trick that you can use to organize your SVS data into reports that meet your needs is to take an existing report in the SVS report pack, and clone it through the NS console. Then you can use the existing report as a template and make changes as needed forming a new report.
Using this information and a little SQL you should now be able to create your own SVS reports and notification policies.
Comments
Greetings
You cant be more right.
Would you like to reply?
Login or Register to post your comment.