3 Replies Latest reply on Nov 7, 2012 6:14 AM by Bedrich.Michalek

    How to create and Advanced SQL Query to show UDT_Users by department

    jerf

      I am trying to create an advanced SQL Query using the template for User History which is on the Orion Report Writer:

       

      SELECT  TOP 10000 UDT_UserHistory.UserName AS UserName,

      UDT_UserHistory.LogonDateTime AS LogonDateTime,

      UDT_UserHistory.IPAddress AS IPAddress,

      UDT_UserHistory.DNSName AS DNSName,

      UDT_UserHistory.DaysBeforeLogin AS DaysBeforeLogin,

      UDT_UserHistory.UserID AS UserID

       

      FROM

      UDT_UserHistory

       

       

      ORDER BY 1 ASC, 2 DESC

       

       

      I would like to also include information about the user such as AD username & Department. If anyone has any ideas or a custom report they could share that would be great.

       

      Thank you,

        • Re: How to create and Advanced SQL Query to show UDT_Users by department
          Bedrich.Michalek

          Hi,

          UDT_UserHistory view doesn't return Department column by default, so you need to take the query from this view and modify it. You can add any column from UDT_User table where the attributes about users are stored. Let me know if you'll have any questions or troubles creating the report.

           

          SELECT

              u.UserID as UserID

              , u.AccountName as UserName

              , u.Department

              , u2ip.IPAddress

              , u2ip.LogonDateTime  

              , dns.DNSName

              , case DATEDIFF(day, u2ip.LogonDateTime, GETUTCDATE())

                  when 0 then N'Today'

                  when 1 then N'Yesterday'

                  else REPLACE(N'{0} days before', '{0}', cast(DATEDIFF(day, u2ip.LogonDateTime, GETUTCDATE()) as varchar(10)))

              end

              as DaysBeforeLogin

          FROM [dbo].[UDT_User] u

          inner join [dbo].[UDT_UserToIPAddress] u2ip on u.UserID = u2ip.UserID

          left join [dbo].[UDT_DNSName] dns on dns.IPAddress = u2ip.IPAddress

              and (dns.FirstSeen < u2ip.LogoffDateTime or u2ip.LogoffDateTime is null)

              and (dns.LastSeen is null or dns.LastSeen >= u2ip.LogonDateTime)