Video Screencast Help
Endpoint Management Community Blog

A fast vComputer view: vComputer2 [for NS 6.x only]

Created: 26 Jul 2010 • Updated: 03 Dec 2010 • 4 comments
Ludovic Ferre's picture
+3 3 Votes
Login to vote

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, ns.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 comparison the vComputer query failed to return results on the same server due to the heavy use of the vComputer table (the process was a deadlock victim after 60~120s). This view built on the vItem, VComputerResource, vActiveAssets and vResourceHierarchy and also brings back data from the basic inventory which is not always used.

Comments 4 CommentsJump to latest comment

Blaine Baker's picture

This looks pretty cool but I don't seem to have any computer resources in my Item table. I seem to have everything *but* computers in the Item table. My normal vComputer view works, though.

Blaine Baker
Information Security Administrator

MySpace, Inc.

-2
Login to vote
TGiles's picture

Ludovic,

Did you create your new view for a 6.0 version of Notification Server? With the release of the 7.0 version the data base was re-organized and the Item table no longer holds all the data it did in the 6.0 version. The different resources have been moved into individual tables such as RM_ResourceComputer & RM_ResourceVirtual_Machine.

 

0
Login to vote
Ludovic Ferre's picture

You got me there... Sorry I'm an old school guys.

And I still have a vast majority of my customers working with 6.x (80% actually) so I should have specified, this is a 6.x vComputer update!

I'll correct this now.

I am currently off-net, on a retreat of some kind. I'll be back real soon, and you sure will hear from me then ;-).

Ludovic FERRÉ
Principal Remote Product Specialist
Symantec

-4
Login to vote
Ludovic Ferre's picture

Given we have almost all the interesting data in the RM_ResourceComputer by passing the vComputer view (legacy view in SMP?) is even simpler, as you can see here:

select pc.Guid, pc.Name, pc.IsManaged, it.name as 'Server'
  from RM_ResourceComputer pc
  join Item it
    on pc.ownernsguid = it.guid
 where Deleted = 0
 union
select vm.Guid, vm.Name, vm.IsManaged, it.name as 'Server'
  from RM_ResourceVirtual_Machine vm
  join Item it
    on vm.ownernsguid = it.guid
 where Deleted = 0

Note that I have taken the stance that deleted computers should not be in the query results, but this can be easily changed of course ;).

I am currently off-net, on a retreat of some kind. I'll be back real soon, and you sure will hear from me then ;-).

Ludovic FERRÉ
Principal Remote Product Specialist
Symantec

+1
Login to vote