Query Vs Dynamic Group (Weird result)

[Remy Dessureault]

Hi,

 

I've notice a disperancy between a query result or preview and the dynamic group linked to that query.

 

Almost all query or computer group show the same kind of behavior...

 

Exemple 1:  Duplicate Hostname 

Query :  dis_hw_uuid IN (select dh.dis_hw_uuid from ca_discovered_hardware dh where dh.host_name in (select distinct(host_name) from ca_discovered_hardware group by host_name having count(*)>1))

 

 

Dynamic group link to the query

 

 

----

example 2:

 

ca_discovered_hardware.dis_hw_uuid in (SELECT TOP 950 dis_hw_uuid from ca_discovered_hardware order by creation_date asc )

 

Query result:

 

Dynamic group link to query:

 

 

 

this one should return 950 computer

but the query return only 940 and the dynamic group 921.

 

Any idea what could be the problem?

 

 

ourenvironment is CA Client Automation 14 SP2 with SQL 2012 database.

 

thanks!

 

Rémy Dessureault

[Brian_Fontana]

DSM Explorer groups may be a bit more complicated than the query return results.  Generally, DSM Explorer is joining these four tables in order to present the list of computers in a group:

 

ca_discovered_hardware
ca_agent
ca_group_member
ca_group_def

 

Note: This list does not include the list of tables necessary to verify your user and security profile has permissions to view the data involved.  I'm keeping it simpler for now.

 

The query designer may be showing you results based on only ca_discovered_hardware or ca_agent, without all the others.

 

Let's start here.  Are these two counts the same?

select count(*) from ca_discovered_hardware

select count(*) from ca_agent where agent_type=1

[Remy Dessureault]

Hi 

 

here the results

 

select count(*) from ca_agent where agent_type=1 

5591 records

 

select count(*) from ca_discovered_hardware

5664 records

 

 

Rémy

[Steve_McCormick]

Hi Rémy,

 

I'm betting then you have only 5591 computers in your Domain, not 5664.

 

If you now select the NAME field from those two tables and put the results into adjacent columns in an excel sheet, then us How to compare two columns to find duplicates in Excel - Excel Tutorial for Excel 2013  to find the ones which are in ca_discovered_hardware but not in ca_agent, my bet would be you will find the computers you are missing from your query groups.

 

I will leave it up to Brian to tell you how to clean up the orphaned records.

 

Steve

[Brian_Fontana]

Yupp, that's the problem.  The query results will reveal the computers that are only inside ca_discovered_hardware table as part of the results, however DSM Explorer will not display these systems in any groups, because they don't have a corresponding ca_agent record.

 

Normally, when a computer is deleted from DSM Explorer, the corresponding ca_discovered_hardware record is cleaned up.  There are a few scenarios where this does not happen, or allowable scenarios where the ca_discovered_hardware record exists, without a ca_agent record.  Off the top of my head...

 

a- The computer was discovered by NSM/CCS, but not yet managed by an ITCM agent.

 

b- Registration of Software Delivery profiles was turned on, and the ca_discovered_hardware record is referenced by BOTH the computer (ca_agent.agent_type=1) and the user profile (ca_agent.agent_type=4).  Hence when deleting the computer, the ca_discovered_hardware record remains, because it is being referenced by the SD user profile.

 

Anyways, its probably best you remove these "orphaned" discovered hardware records from the database.  We can use a simple delete script for this:

 

declare @uuid int
declare uid CURSOR for select dis_hw_uuid from ca_discovered_hardware where dis_hw_uuid not in (select object_uuid from ca_agent)
open uid
fetch next from uid into @uuid
while @@FETCH_STATUS=0
begin
  delete from usd_applic where target=@uuid
  delete from ca_group_member where member_uuid=@uuid
  delete from ca_link_dis_hw_user where dis_hw_uuid=@uuid
  delete from ca_server where dis_hw_uuid=@uuid
  delete from ca_agent where object_uuid=@uuid
  delete from ca_discovered_hardware where dis_hw_uuid=@uuid
fetch next from uid into @uuid
end
close uid
deallocate uid

 

That should do the trick!

 

-Brian

[Remy Dessureault]

Hi,

 

ok I've done the script yesterday, there was  a record difference of one.

 

This morning, I tried the query again to see, and there is still one record difference between the two.

 

 

I'll keep monitoring for a few days and update or close later...

 

 

Remy

[Brian_Fontana]

So these two queries still show one record in difference?

select count(*) from ca_discovered_hardware

select count(*) from ca_agent where agent_type=1 

 

Let me know which table has more.  The delete script is focussed on records in ca_discovered_hardware that do not have a corresponding record in ca_agent.  It should not have left 1 record in difference, unless somehow you have something in ca_agent that isn't in ca_discovered_hardware...

 

[Remy Dessureault]

Hi

 

as of this morning:

 

select count(*) from ca_agent where agent_type=1

5731
select count(*) from ca_discovered_hardware

5735

 

Rémy

[Brian_Fontana]

select host_name from ca_discovered_hardware where dis_hw_uuid not in (select object_uuid from ca_agent)

 

For the host names that are returned, are you able to find out any information about what happened to them?  To be clear, these are computers that are still in ca_discovered_hardware, but have otherwise been deleted from ITCM, hence not visible in DSM Explorer.

[Remy Dessureault]

Hi

 

for what I can see, the computer are deleted.  

the 6 computers are :

 

3 test computer that are more or less deleted every day to be rebuild for sofware test.

2 are OS installation test computer  (May have been deleted often to make test)

1 is an unknown, it seem to be an old computer (it might have been retire, but it was deleted from CA recently).

 

 

 

Remy

[Brian_Fontana]

Possibly some of the mismatches between ca_discovered_hardware and ca_agent are OSIM related then.  To my knowledge, OSIM computers can be viewed by selecting all records from csm_v_computer... possibly you can cross check and see if some of these computers are simply listed there.

 

The other possibility is software delivery user profile registration might be enabled.

 

Domain/Control Panel/Configuration/Configuration Policy/Default Computer Policy/DSM/Agent/Common Agent/Software Delivery: Registration: Supported unit types

 

If set to Computer+User Profiles, this would enable creation of multiple ca_agent.agent_type=4 records, one for each user profile that is registered.  Each of them will reference back to the ca_discovered_hardware record for the computer which they belong.

 

So when you delete the computer agent, but he software delivery user profiles remain, so will the ca_discovered_hardware record.

[Remy Dessureault]

Hi

 

Yes we have user profil activated...

 

Does it mean that we have to delete those profil prior to deleting the computer?

 

 

Now I've redone the script and both result re now equal.

 

declare @uuid int
declare uid CURSOR for select dis_hw_uuid from ca_discovered_hardware where dis_hw_uuid not in (select object_uuid from ca_agent)
open uid
...

 

 

I'll continue monitoring those....

[Brian_Fontana]

Order doesn't matter-- you don't have to delete the user profile first.  Whichever is last to delete should remove the ca_discovered_hardware record.  Just both are referencing the same hardware record, so whichever is last to go will remove it.  I believe that explains the difference all along!