Video Screencast Help
Endpoint Management Community Blog
Showing posts tagged with Database
Showing posts in English
Ludovic Ferre | 26 Jul 2010 | 4 comments

The vComputer view provides an easy access to managed computers, however in very large reporting server this view (built on many other abstration) is taking a lot of time to run and is not the most efficient base to built report. How could we provide similar information without the overhead?

The following SQL query will return the guid, name, IsManaged, server (based on OwnerNSGuid) from the database using 3 tables and 2 joins.

create view dbo.vComputer2 as
select ir.Guid, ir.IsManaged, im.Name, as 'Server'
  from ItemResource ir
  join Item im
    on ir.guid = im.guid
  join Item ns
    on im.ownernsguid = ns.guid
 where ResourceTypeGuid='493435F7-3B17-4C4C-B07F-C23E7AB7781F'

On a reporting server with 60,000+ managed computers this query ran in 12 seconds returning 62,921 rows. In...

Ludovic Ferre | 14 Jun 2010 | 0 comments

In some cases the NS 6.0 SP3 purging procedure fails because the data tables to purge are much too big and purge never completes in the given DBTimeout setting (in CoreSettings.config, set to 600 seconds default).

You can see some hints on this in the AKB,  (articles #33443, #31988, #1068 and possibly more).

So, in order to reduce run-away database sizes for my customer I crafted the SQL query listed below.

But before you check in the query, please let me details the differences between this query and the SP3 purging procedure, as well as some potential issues with it.

  • We first purge the dataclass data table using this base statement: "set @PurgeQuery = N'delete from [' + @CurrentClassTable + N'] where [_eventTime] < @Time'"
  • Second we purge the dataclass data table using the defined MaxRowCount in the purging policy
  • On very large tables and large database the first section may actually never...
Ludovic Ferre | 15 Dec 2009 | 5 comments

There is a fuzzy area between the count of managed machines in a Notification Server and the count of active machines.

First when is a managed machine considered as active or inactive? This can be defined and interpreted in many different ways based on the nature of one's business, whether it is fast changing or strictly controlled etc.

The SQL query below is not going to answer this question, but can serve as a starting point (you could add event request on the filter such as last basic inventory received or last client policy request etc):

use altiris

select count (*), ((DATENAME(Year,DataLastChangedDate)) + ' ' + (DATENAME(Month,DataLastChangedDate))) as 'Month'
  from (
			select ResourceGuid, Max(DataLastChangedDate) as 'DataLastChangedDate'
			  from ItemResource ir
			  join ResourceUpdateSummary rus
				on ir.guid = rus.resourceguid
			 where IsManaged = 1
			 group by resourceguid
		) t
 where datalastchangeddate > DATEADD(Month,- 3,GETDATE...
Ludovic Ferre | 08 Dec 2009 | 2 comments

So I logged on the server a few minutes ago and checked the following:

  • Sessions in the eXpress database = 1222
  • Tcp sessions reported by "netstat" = 523
  • Tcp sessions reported by "netstats" on port 402 = 451
  • Tcp sessions reported by "netstats" as CLOSE_WAIT = 275

Which means the server only has 176 active tcp sessions (including the PXE and SQL connections) when the axengine (and thus the DS console) reports 1222 active computers.

Looking on the server Aclient (which as the same symptoms as all other machines - a white icon instead of the expected blue one) we find the following entry:


Which is documented on the AKB ( So, I...

Ludovic Ferre | 07 Dec 2009 | 0 comments

I can happily state that I had no DS issues of the "bad kind" (hard to resolve, generic or systemic problem type) for a few month now.

No need to specify that I was very happy with this state of play, thanks to the release of DS 6.9 SP3! But has it happens, there's always a time when new problems are found, or old ones crop back up one way or another. So I'm back onto the classic Aclient / DAgent -> DS connectivity issue.

I'll take this opportunity to refresh my brain on how DS handles communications to the clients (... off to Google Doc to create a diagram for this -> back from Google doc, got the png files attached below).


This is only a very rough overview that shows which elements in the communication chain are creating log files.

From the AClient (or DAgent) up we have the Aclient.log generated on the client...

Ludovic Ferre | 30 Nov 2009 | 0 comments

One of the best features of SQL (the sql language, and it's various vendor specific implementations) is that (almost) everything is open and accessible.

Tables, views, stored procedures, triggers, indexes. You name it. You can look at all of those objects, edit them, do anything you like with them.

Hence the title of this blog entry: it's a poor man's version of open source in a sense that anyone with interest could contribute code review and improvement (I've have an article on this pending administrator's approval - so watch this space) for the benefits of everyone (using NS) thanks to this site (Connect, a name that serves the purpose).

And being open and allowing more people to help has proven to be a really successful strategy in many places and software projects (again IMHO).

btowne | 01 Oct 2009 | 0 comments

In NS6 there was a function called "GetItemPath" in which return the path for an object in the Console. How can I get the folder path in NS 7?

"GetItemPath" function does not exist in the NS 7 database. Here is the sql behind creating that function for changes in the NS 7 database:


USE [Symantec_CMDB]






ALTER FUNCTION [dbo].[GetItemPath]


      @Guid Varchar(40)


Returns varchar(8000)







  @SearchGuid VARCHAR(40),    

  @ItemPath VARCHAR(500),    

  @ExitCounter INT    


SET @SearchGuid = @Guid

SET @ItemPath = ''    

SET @ExitCounter = 0    


WHILE @ExitCounter = 0    


  IF EXISTS (SELECT * FROM ItemFolder WHERE ItemGuid = @SearchGuid AND ParentFolderGuid != 'D0E33520-C160-11D2-8612-00104B74A9DF')    


    SET @ItemPath = (SELECT TOP 1 [Name] + '/' FROM Item WHERE [Name] NOT LIKE '/_%' escape...
neil_rogers | 17 Sep 2009 | 0 comments

I had a call from a customer yesterday.  They had a vulnerability that had to be fix by end of day, and it was 3 pm when I got the call.

He was not the deployment server admin, and had minimal access into DS, but the admin was not available.  Passwords were getting locked out due to the issue.

Opening up the console just pulled up the sql connection option.  So here are the few steps to check:

1.  Check what services are running, Altiris services need to be running, Altiris Deployment services (2-3 of them), and Altiris Express service, Restarting will show if the account is locked.
2.  Check the ODBC connections, Administrative tools, Data Sources (odbc) -> System -> (express service) -> test the connection
3.  Check SQL services to make sure they are running.

We went on to build a new job to deploy the patch, that is for another post.

Mustafa Abdel-Aziz | 11 Aug 2009 | 0 comments

In the last few days I faced a problem with Helpdesk and Connector Solutions. I have imported some assets from external source using Connector solution and wanted to use these assets in my Helpdesk solution.

The problem I faced was that the new assets were imported with Asset Type (Asset). Hence, the Helpdesk system was not able to view it since the Resource Types list in the Admin menu does not include the (Asset) as an entry.

To solve this problem, I decided to change the asset type of my assets into a type that is supported by Helpdesk, like (Computer). But, I don't have Asset Management Solution to use it to change the asset type.

So, I had to run the below script on the SQL database to change the asset type. After I run that, all assets types are changed to (Computer) and I can see them in the Helpdesk Solution.

Here We Go


1) First, get the required ResourceType ID:

jjesse | 15 Apr 2009 | 2 comments

Start your downloads.  If you are a part of the Beta Team you should see CMDB 7 as a part of the available products to download and start testing.

From the email to start beta testing:

We are pleased to announce the beta release of Altiris CMDB 7. Registration for the CMDB 7 beta may be found under the profile tab of the beta portal. We anticipate the beta cycle to run approximately 8 weeks. We look forward to working with you to improve the product toward release.
There are a number of new features with CMDB 7 built on the Symantec Management Platform. The following features have been added or improved:
– Many reports have been rewritten using the new 7.0 reporting engine
– The rest of the reports are available in a legacy mode
Portals in the console
– Customize your own view with web parts
– Tree view of...