SQL Query that shows where the contents of the AgentBlackList table originated from
search cancel

SQL Query that shows where the contents of the AgentBlackList table originated from

book

Article ID: 179766

calendar_today

Updated On:

Products

IT Management Suite

Issue/Introduction

There are a lot of GUID entries in the agentBlackList table in the NS DB. How can I determine where the entries originated from?

Environment

ITMS 8.x

Resolution

Computers can get blacklisted as part of the NS auto-merge function in addition to the scenario as described in KB 181823

You can use the following SQL Query to find if the NS Merge process is responsible for the computers showing up in the agentBlackList table:

select
abl.BlacklistDate,
erm.Resource2Guid,
i2.name as Resource2Name, '**********' as '**********',
erm.Resource1guid,
i1.name as Resource1Name,
erm._resourceguid,
vi.name,  '**********' as '**********',
erm.MergeKeyName,
erm.MergeKeyValue

from agentblacklist abl
 left join vRM_Computer_Item vi on vi.guid = abl.guid
 left join Evt_Resource_Merge erm on erm.resource2guid = abl.guid
  left join vRM_Computer_Item i1 on i1.Guid = erm.Resource1Guid
 left join vRM_Computer_Item i2 on i2.Guid = erm.Resource2Guid
 
 order by abl.BlacklistDate desc

The preceding query also shows the current GUID and name of the resource. It also identifies what Merge Key and Key Value was used to determine that it needed to be merged with another resource.

The results of the query will show you if the computer has been automerged. Auto merging should store the losing GUID in the agentBlackList table and permit the winning GUID access to the NS.