I have a query that returns the Last Scan Date, Name, Make and Model of machines scanned in the last 10 days.

 

DECLARE @dLastScanDate DATETIME

SELECT @dLastScanDate = getdate() -10
SELECT A.LastHWScan,
B.Name0,
B.Manufacturer0,
B.Model0,

FROM v_GS_WORKSTATION_STATUS A
JOIN v_GS_COMPUTER_SYSTEM B ON A.ResourceID = B.ResourceID
WHERE A.LastHWScan > @dLastScanDate

 

I have 13 machines with two rows v_GS_COMPUTER_SYSTEM.  This is from a total of over 60,000.

 

The primary key of the base tables WorkstationStatus_DATA is MachineID, and for

Computer_System_DATA it is MachineID,InstanceKey

 

It looks like the 13 machines were rebuilt with a new name.

 

How do I know which is the current one?

 

I could use the most recent TimeKey but that seems resource intensive to join the table back to itself with a max(TimeKey)

 

Any insight would be most appreciated

 

 

Thanks.

 

Mike.

 

The data in v_GS_WORKSTATION_STATUS  contains one row per machine.

 

v_GS_COMPUTER_SYSTEM contains information about the machines.

 

In my mind this should be a one to one relationship.  A machine should have one Name, Make and Model.

 

Here is an example where this is not the case:

 

select machineid,lasthwscan from WorkstationStatus_Data where machineid = 1782

 

machineid   lasthwscan                                            
----------- ------------------------------------------------------
1782        2008-11-08 17:47:00.000

select machineid,instancekey,name0,model0,manufacturer00 from computer_system_data where machineid = 1782

 

machineid   instancekey name0      model0           manufacturer00   
----------- ----------- ---------- ---------------- -----------------
1782        1           aaaaaaa    Latitude D630    Dell Inc.
1782        1           xxxxxxx    Latitude D630    Dell Inc.

 

 

When I join these tables I want one row.  How do I pick the correct one? Is  Name0 = aaaaaaa or xxxxxx?

 

I can do this

 

DECLARE @dLastScanDate DATETIME
SELECT @dLastScanDate = getdate() -10
SELECT A.LastHWScan,
B.Name0,
B.Manufacturer0,
B.Model0
FROM v_GS_WORKSTATION_STATUS A
JOIN v_GS_COMPUTER_SYSTEM B ON A.ResourceID = B.ResourceID
join (select resourceID,max(TimeStamp) max_TimeStamp
     from v_GS_COMPUTER_SYSTEM group by resourceid) c on c.ResourceID = b.ResourceID and b.TimeStamp = c.max_TimeStamp
WHERE A.LastHWScan > @dLastScanDate

 

Which used the most recent timestamp from v_GS_COMPUTER_SYSTEM, but I don't think that is very logical and my query goes from 2 seconds to 26 in a test database with 2000 machines.

 

 

I could join to System_Disc which has Netbios_Name0 and Name0, (which one is the right field?  All of mine are equal) but all of these approaches seem to indicate something wrong with my data.  Should there be more than one row per machine in v_GS_COMPUTER_SYSTEM?  The data model allows it, but it does not make sense to me.

 

Run this to check your databases

select count(*),machineid
from computer_system_data
group by machineid
having count(*) > 1

 

Thanks.

 

(in reply to MikeTeske)

(in reply to jnelson993)

(in reply to MikeTeske)

(in reply to jnelson993)

(in reply to jnelson993)

(in reply to MikeTeske)