UDT custom report for number of users per site

[jdw_uk]

Hi all,

I would like to create a report that counts the number of active users per office location per month.

In the ‘nodes’ table I have custom properties for each node that includes for example ‘Region’ and ‘Location’. Using these custom properties I would like to count the number of active users per month to allow me to view the user occupancy at remote sites over time.

Something like this:

Region	Location	Active users
AMER	Office 1	10
EMEA	Office 2	100
EMEA	Office 3	90
APAC	Office 4	36

I think there would need to be a JOIN between the ‘nodes’ table and one of the UDT tables, but with very little SQL skills I haven’t been able to craft a SQL query yet.

Any SQL guru’s out there?

Thanks!

[jdw_uk]

Perhaps the relevant database tables might be:

Table	Field	Field	Field	Field	Field	Field	Field
UDT_UserLastActivity	IP Address		UserID	UserName	LogonDate Time
UDT_AllEndPoints	IP Address	NodeID
Nodes		NodeID
NodesCustomProperties		NodeID				My_Location	My_ID

[thomasdrinkwater]

Did anyone have any luck to creating the SQL or SWQL query?

I am looking at reporting on something very similar, but instead of reporting on user's I am reporting on interface ports active in the last 30 days.