Introduction

Snow Inventory 5 introduced a new function: "deduplication", which was designed to handle multiple sources. The script below can be used to adjust the hostnameonly function or identify issues in multi-source environments.

How the script works

Snow Inventory (SI) Server creates "Identities" for the incoming Inventory Files.  The Server looks the identity up:

• If it has a perfect match, nothing happens with the identity.
• If one parameter changes, the Value "IdentityUpdateCount" in the inv.AgentManifest table raises by one.
• If there is no match found, it will create a new Identity and a new Computer.

This means that if you have a Infrastructure with computers all sharing the same Biosserialnumber and MachineSID, Snow Inventory is able to handle them all on one Dataset. With the Hostnameonly mode, available since SI 5.1.7 this can be corrected.
 

The second value, which is used from the script, is the check "How many Inventory files were handled during the last seven days for a specific Client?" The default is that the Agent should send one file per day. There may be more files and it would be ok however having both a high "IdentityUpdateCount" plus many handled Files would usually indicate an issue.

You can adjust the @identitygap value at the top of the script especially if the customer had several attempts to switch the datasource.

The columns prefixed with "identity-" indicate how unique the used values are.

The computers with the highest counts in the identity-hostname column are displayed at the top, followed by the most processed files during the last 8 Days, followed by the Computers with the highest non-unique Biossn / machinesid combination. NULL Values will also be given an extra boost.

At the end of the script there are some additional lines which allow you start evaluating what's wrong.
 

Analysing the data

After you have upgraded Snow Inventory (SI) from version 3.x to version 5.x the Identity Update Count should be set to 0, if not this will produce orphaned Computers in Snow License Manager.
 

During normal processes, the Identity Update count could rise. Some examples that might cause this:

• The Computer is renamed.
• The Agent delivers more data after update.

Abnormal processes that increase the Identity Update count could include:

• Too few unique identifiers with no good or no "Hostnameonly" configuration.
• Multiple primary Inventory Sources (i.e. SCCM and Snow Inventory Agent).

During normal processes, the Filecount per week could rise. Some examples might be:

• The computer was Offline a long time, and sent a lot of inventory files at once.
• Microsoft Windows Server is inventoried via an Agent/3rd Party Inventory and our Hyper-V Scanner.

During abnormal processes, the filecount per week could rise.  For example:

• Multiple primary Inventory Sources (i.e. SCCM and Snow Inventory Agent).

The Script

use SnowInventory
go

declare @identitygap int = 0 -- depending on the environment - if everything is perfect, each client would have 0 identity changes and wouldn't be displayed 
							 -- if the list is too long start raising the number (happens i.e. with multiple inventory sources)
declare @scope int = 30 --days in the past to anaylze

select c.clientid,
		c.hostname,
		c.LastUpdate,
		c.ClientVersion,
		r.SourceName,
		c.SiteName,
		r.[files per day],
		r.[days of scope],
		r.identityupdatecount,
		case
			when lu.AssignedId	is not null then 1
			else 0
			end as 'hostnameonly activated',
		stat.[identity-site],
		stat.[identity-hostname],
		stat.[identity-Biossn],
		stat.[identity-sid],
		stat.[identity-Biosman],
		stat.[identity-model],
		c.model,
		c.BiosSerialNumber,
		c.BiosManufacturer,
		c.MachineSid
		from inv.DataClientView2 c
inner join (
	select a.clientid, a.AssignedId,a.identityupdatecount,a.SourceName ,avg(1.00 * a.filename) 'files per day', count(distinct day(a.processingdate)) as 'days of scope' from (
		select am.clientid,am.AssignedId, am.IdentityUpdateCount,am.SourceName, s.ProcessingDate,s.FileName from inv.AgentManifest am
		inner join (select ClientId, day(processingdate) as processingdate, count(filename) as filename from inv.ChangeSequenceNumber where ProcessingDate > dateadd(day,-1*@scope,getdate())
					group by clientid, day(processingdate)) s on am.ClientId = s.ClientId
	where am.IdentityUpdateCount > @identitygap
) as a group by Assignedid,ClientId,IdentityUpdateCount,SourceName) r on c.ClientId = r.ClientId
inner join (select c.clientid,
		count(*) over (partition by c.hostname) as 'identity-hostname',
		count(*) over (partition by isnull(c.model,0)) as 'identity-model',
		count(*) over (partition by isnull(c.biosserialnumber,99999)) as 'identity-Biossn',
		count(*) over (partition by isnull(c.Biosmanufacturer,0)) as 'identity-Biosman',
		count(*) over (partition by isnull(c.machinesid,99999)) as 'identity-sid',
		count(*) over (partition by isnull(c.sitename,0)) as 'identity-site'
		from inv.dataclientview2 c) stat on c.ClientId = stat.ClientId
left outer join (select * from inv.AgentIdentityLookup where Lookup like '/h/%') lu on r.AssignedId = lu.AssignedId
where [files per day] > 1.00 and [days of scope] > 1
order by stat.[identity-hostname] desc ,[files per day] * [days of scope] desc,stat.[identity-Biossn]+stat.[identity-sid] desc

/*
-- Optional - more research?
declare @clientid int = 139
select * from inv.ChangeSequenceNumber where clientid = @clientid order by ProcessingDate
select * from inv.DataClientView2 where ClientId = @clientid
select * from inv.AgentManifestView2 where clientid = @clientid
select * from inv.AgentIdentitylookup where assignedid in (select assignedid from inv.AgentManifestView2 where clientid = @clientid)
-- slm8
select * from SnowLicenseManager.dbo.tblComputer where ClientID = @clientid
-- slm9
select * from SnowLicenseManager.dbo.tblComputer c
	inner join snowlicensemanager.inv.tblComputerInvSlmMap cc on c.ComputerID = cc.ComputerID where cc.ClientID = @clientid
select * from inv.DataClientView2 c
	inner join inv.DataClientView2 c2 on c2.ClientId = @clientid and c.hostname = c2.hostname
*/