Video Screencast Help

Need report for number of users logged in - CMS7

Created: 25 Oct 2012 • Updated: 15 Nov 2012 | 8 comments
This issue has been solved. See solution.

i am in need of a report to generate the number of users logged in to our computer labs for specific periods.  In particular, I need to generate a report for a specific subset of users belonging to an arm of the university which is slightly separate (financially).  Our staff numbers contain 6 digits, regular students 8 digits and the ones I'm in need of at the moment the user ids contain 7 digits.  Some acounts may start with a 0.

Can anyone help with the SQL for generating a report for a specific time period and possibly by campus (which can be obtained by the first 5 characters of the computer name)

 

Thanks in advance

Greg Lamb

Lab Manager

University of Technology, Sydney

Comments 8 CommentsJump to latest comment

dream's picture

Do you have details about the data layout for your select statement? I imagine your sql would look something like this:

select count(user id),

from whatever appropriate table

where datediff whatever appropriate date

JimChud's picture

Ok i'm using something abit different for slightly different reasons to you but:

I have a report that basically does a search for logon and log off times for specific users, how long they were in for and i can then do a filter for the users i'm looking for. You could adjust this and change the search perameters to computer name instead of users or add additional where clauses for the computer set you are looking for.

I then have a query parameter called LOUSERNAME and do a like for that. Using the Like % VARIABLE % means i can search for Dave and it will find all the daves in my company and their details.

If you change the WHERE [User] LIKE '%LOUSERNAME%' to WHERE [User] LIKE '0%' it will search for all usernames that start with a 0 for example and %0% would be all accounts that include a 0 or %0 would be all that end with 0.

In your where clause you can also use "WHERE LENGTH([LO].[USER]) = 7" and it will search for all usernames that have a length of 7 characters for example.

Give it a go and if you need more please say i will see if i can mock up something more appropriate.
 

SELECT cid.[Name] Computer
      ,(LO.[Domain] + '\' + LO.[User]) [User]
      ,LO.[Login Time]
   ,LO.[Time] [Logout Time]
      ,CONVERT(decimal(10,2),ROUND((LO.[Duration]/60.0),2)) [Duration In Hours]
      ,LO.[Duration]
FROM [Altiris_CMDB7].[dbo].[Evt_AeX_Client_LogOn] LO
 JOIN Inv_AeX_AC_Identification cid ON cid._ResourceGuid = LO._ResourceGuid
WHERE [User] LIKE '%LOUSERNAME%'
AND [LO].[Event] = 'Logoff'
ORDER BY LO.[Login Time] DESC

 

 

You would probably want something abit like this:

 

SELECT cid.[Name] Computer

,(LO.[Domain] + '\' + LO.[User]) [User]

,LO.[Login Time]

,LO.[Time] [Logout Time]

,CONVERT(decimal(10,2),ROUND((LO.[Duration]/60.0),2)) [Duration In Hours]

,LO.[Duration]

FROM [Altiris_CMDB7].[dbo].[Evt_AeX_Client_LogOn] LO

JOIN Inv_AeX_AC_Identification cid ON cid._ResourceGuid = LO._ResourceGuid

WHERE [User] LIKE '%dave%'

AND [LO].[Event] = 'Logoff'

AND (cid.[Name] = 'LABCOMPUTER1'

OR cid.[Name] = 'LABCOMPUTER2')

AND LO.[Login Time] Between '2012-10-25 07:00:00' AND '2012-10-25 18:00:00'

ORDER BY LO.[Login Time] DESC

 



Regards Jim.
Connect Etiquette: "Mark as Solution" those posts which resolve your problem and give a thumbs up to useful comments, articles and downloads.

gjlamb's picture

Thanks for the info.  I'll give it a try.  I'm not sure what the layout would be.  I don't delve into the SQL often so not sure what fields from what table are there for me to get the information.

Regards

gjlamb's picture

Can't run it as a query as the engineer has set up the server authentication in a different way than it should be.  Will have to wait till he gets back off leave.  Any chance it could be put into a report/xml that I could import into console?  Had a look and 7 is a lot harder to create a report from scratch than 6 was.

Thanks

Greg

gjlamb's picture

So far i've simplified the report to 

select * from Evt_AeX_Client_LogOn

WHERE [Login Time] between '2012-06-12' AND ' '2012-0907'

 

What I'd also like to capture is a specific user base (one school) where logins are 7 digits as opposed to the normal 8 digits for students or 6 for staff.

Any suggestions?

Thanks

JimChud's picture

Give this a go:

 

select * from Evt_AeX_Client_LogOn

WHERE [Login Time] between '2012-06-12' AND '2012-09-07'

and Len([User]) = 7

 

That should basically search for usernames with a string length of 7

Regards Jim.
Connect Etiquette: "Mark as Solution" those posts which resolve your problem and give a thumbs up to useful comments, articles and downloads.

gjlamb's picture

Thanks all.  I've combined the tips and come up with the following to get exactly what I want.  Now all I need to do is figure out how to add parameters to the report on the console so I can give it to the user to do themselves.

 

 

SELECT cid.[Name] Computer      ,(LO.[Domain] + '\' + LO.[User]) [User]
 
      ,LO.[Login Time]
 
   ,LO.[Time] [Logout Time]
 
      ,CONVERT(decimal(10,2),ROUND((LO.[Duration]/60.0),2)) [Duration In Hours]
 
      ,LO.[Duration]
 
FROM [Symantec_CMDB].[dbo].[Evt_AeX_Client_LogOn] LO
 
 JOIN Inv_AeX_AC_Identification cid ON cid._ResourceGuid = LO._ResourceGuid
 
WHERE [User] LIKE '%%'
and LO.[Domain] = 'DOMAIN'
and Len([User]) = 7
 
AND [Login Time] between '2012-05-12' AND '2012-09-07'
 
ORDER BY LO.[Login Time] DESC
gjlamb's picture

Worked it out.  Copied date range SQL and parameter settings from report Discovered Devices/Discovery and Inventory/Reports and added declare and set statements at top of SQL

 

 

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME 
 
SET @StartDate = CAST('%DateRangeFrom%' AS DATETIME)
SET @EndDate = CAST('%DateRangeTo%'AS DATETIME)
 
SELECT cid.[Name] Computer      ,(LO.[Domain] + '\' + LO.[User]) [User]
 
      ,LO.[Login Time]
 
   ,LO.[Time] [Logout Time]
 
      ,CONVERT(decimal(10,2),ROUND((LO.[Duration]/60.0),2)) [Duration In Hours]
 
      ,LO.[Duration]
 
FROM [Symantec_CMDB].[dbo].[Evt_AeX_Client_LogOn] LO
 
 JOIN Inv_AeX_AC_Identification cid ON cid._ResourceGuid = LO._ResourceGuid
 
WHERE [User] LIKE '%%'
and LO.[Domain] = 'Domain'
and Len([User]) = 7
 
AND [Login Time] between '%DateRangeFrom%' AND '%DateRangeTo%'
 
ORDER BY LO.[Login Time] DESC
SOLUTION