Client Management Suite

Hi,

Is there a way to join the vcomputer table (type computer) to the Aex Client LogOn table (Type Event)  ?

In fact, I need to have a report having the primary user, the machine name and the last login time of that primary user.

See attached report and please help me include the last logon time of the primary user.

Thanks for your help.

Attachment(s)

Search Machines by Filter.docx   12 KB 1 version

yes this is just a straight inner join between those tables, joining the GUID column of vComputer to the _ResourceGuid of the LogOn table, where the name = name.  I'm just getting into 7.x, let me try to create this report real quick.

I commented out the stuff for the filter and the Case statement to make it shorter; easy enough to re-add it:

SELECT DISTINCT

i.Name, i.[User], max(logon.time) as [logon time], i.[OS Name], i.[OS Revision], i.[System Type],

vhw.[Manufacturer],

vhw.[Model],

hwc.[Serial Number], hwc.[Chassis Package Type]

/*

Case hwc.[Chassis Package Type]

When 1 Then'Other'

When 2 Then'Unknown'

When 3 Then'Desktop'

When 4 Then'Low Profile Desktop'

When 5 Then'Pizza Box'

When 6 Then'Mini Tower'

When 7 Then'Tower'

When 8 Then'Portable'

When 9 Then'Laptop'

When 10 Then'Notebook'

When 11 Then'Hand Held'

When 12 Then'Docking Station'

When 13 Then'All in One'

When 14 Then'Sub Notebook'

When 15 Then'Space-Saving'

When 16 Then'Lunch Box'

When 17 Then'Main System Chassis'

When 18 Then'Expansion Chassis'

When 19 Then'Sub Chassis'

When 20 Then'Bus Expansion Chassis'

When 21 Then'Peripheral Chassis'

When 22 Then'Storage Chassis'

When 23 Then'Rack Mount Chassis'

When 24 Then'Sealed Case PC'

End [Chassis Type]

*/FROM dbo.vComputer i

INNER JOIN vHWComputerSystem vhw ON vhw._ResourceGuid = i.Guid

INNER JOIN Inv_HW_Chassis hwc ON vhw._ResourceGuid = hwc._ResourceGuid

--JOIN dbo.CollectionMembership cm

-- ON i.[Guid] = cm.ResourceGuid

--JOIN dbo.vCollection it

-- ON it.Guid = cm.CollectionGuid

JOIN dbo.Evt_AeX_CLient_Logon logon on i.Guid = logon._ResourceGuid

WHERE i.[System Type] LIKE 'Win%'

AND LOWER (i.[Name]) LIKE LOWER ('%%')

AND LOWER (vhw.[model]) LIKE LOWER ('%')

--AND lower (it.[Guid]) LIKE lower ('%Filter%')

GROUP BY i.Name, i.[User], i.[OS Name], i.[OS Revision], i.[System Type],

vhw.[Manufacturer],

vhw.[Model],

hwc.[Serial Number], hwc.[Chassis Package Type]

Jack, does this answer your question?

I have never been a big fan of using Views. Just too much information that I may not need. I like to go to the source. This should work for what you are asking though.

select rc.Guid as '_ItemGuid',

      id.Name as 'Computer',

      gd.[Display Name] as 'Primary User',

      MAX(cl.[Login Time]) as 'Last Login' from RM_ResourceComputer rc

left outer join Inv_AeX_AC_Identification id

on id._ResourceGuid = rc.Guid

left outer join ResourceAssociation ra

on ra.ParentResourceGuid = rc.Guid

      and ra.ResourceAssociationTypeGuid = 'ed35a8d1-bf60-4771-9dde-092c146c485a'

left outer join RM_ResourceUser ru

on ru.Guid = ra.ChildResourceGuid

left outer join Inv_AeX_AC_Primary_User pu

on pu._ResourceGuid = rc.Guid

      and pu._id = (select top(1) Inv_AeX_AC_Primary_User._id

                                    from Inv_AeX_AC_Primary_User

                              where (id._ResourceGuid = Inv_AeX_AC_Primary_User._ResourceGuid)

                              order by _id desc)

left outer join Inv_Global_Windows_Users wu

on wu.UserId = pu.[User]

left outer join Inv_Global_User_General_Details gd

on gd._ResourceGuid = wu._ResourceGuid

join Evt_AeX_Client_LogOn cl

on cl._ResourceGuid = id._ResourceGuid

group by rc.[Guid], id.Name, gd.[Display Name]

I really appreciate your help !!! Thanks, I got exactly what I need.

Be careful with using RM_ResourceComputer, it will only get you physical computers.  To get both physical and virtual computers, use vRM_Computer or vRM_Computer_Item, if you need computer name.