Client Management Suite

 View Only
  • 1.  Need report for number of users logged in - CMS7

    Posted Oct 25, 2012 09:44 PM

    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



  • 2.  RE: Need report for number of users logged in - CMS7

    Posted Oct 26, 2012 11:10 AM

    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



  • 3.  RE: Need report for number of users logged in - CMS7

    Posted Oct 26, 2012 12:10 PM

    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

     

    



  • 4.  RE: Need report for number of users logged in - CMS7

    Posted Oct 28, 2012 05:58 PM

    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



  • 5.  RE: Need report for number of users logged in - CMS7

    Posted Nov 07, 2012 09:21 PM

    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



  • 6.  RE: Need report for number of users logged in - CMS7

    Posted Nov 12, 2012 11:19 PM

    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



  • 7.  RE: Need report for number of users logged in - CMS7

    Posted Nov 14, 2012 03:52 AM

    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



  • 8.  RE: Need report for number of users logged in - CMS7

    Posted Nov 14, 2012 04:52 PM

    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


  • 9.  RE: Need report for number of users logged in - CMS7
    Best Answer

    Posted Nov 14, 2012 06:30 PM

    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