7 Replies Latest reply on Jul 30, 2015 4:02 PM by Chris McLane

    I need an Audit History report

    Richard Lester
      Share This:

      I need to create a report on the audit history of computers does anyone know what table would show the Audit Date history?

        • 1. Re: I need an Audit History report

          There are several tables involved with the Audit data:

           

          WORKSTAT (the base records you see in the Inventory grid view)

          WORKSTATION_AUDIT_HISTORY (the Audit dates you see listed under the Hardware tab of the asset)

          WORKSTATION_COMPONENTS (most of the data that you see under an asset's Hardware tab)

          IN_WORKSTATIONINSTALLEDPROGRAMS

          WORKSTATION_SOFTWARE

          Others I'm sure

           

          You can find each asset's latest audit dates in the WORKSTAT table, or you can join the WORKSTAT, WORKSTATION_AUDIT_HISTORY, and WORKSTATION_COMPONENTS tables by WS_NUM and get the Audit Dates from WORKSTATION_AUDIT_HISTORY.

          • 2. Re: I need an Audit History report
            Richard Lester

            Chris,

             

            If I wanted to only pull the number of users that were audit related to workstat.login?  I am trying to log different users logging onto one computer

            • 3. Re: I need an Audit History report
              Richard Lester

              If I could just pull the data that would show on the hardware tab in a asset would be the best.

              • 4. Re: I need an Audit History report

                The ability to have a report show you all the users who have ever logged on to a computer (I assume that is what you are trying to do) might not work out very well. There are cases where the logged on user may not have been captured by the audit so you'd never see those. The WORKSTAT.LOGIN column isn't going to help since it is replaced every time the user changes. The only place you might be able to see a list of all the users who had logged on to a single computer would be the WORKSTATION_COMPONENTS table, but that table is very hard to report off of since it contains entries for lots of different attributes.

                • 5. Re: I need an Audit History report
                  Richard Lester

                  You must have been watching my computer I just figured that out:)  It seems I can use domain\ as a filter on the componentValue and that looks like that will work.

                  • 6. Re: I need an Audit History report

                    Yeah, everything from the Hardware tab in the asset comes from the WORKSTATION_COMPONENTS table, but it's not the friendliest to work with. You would want to join the WORKSTAT and WORKSTATION_COMPONENTS tables by WS_NUM, and then filter out the "IsCurrent = 0" records so you can get the list of the most recent values.

                     

                    Here is a sample SQL statement that would show you all of the Hardware tab data, ordered by WS_NUM to get you started. Maybe you can take it from there and play around with it until you get something close to what you want:

                     

                    EDIT: filters out the MS Hotfixes and related information:

                     

                    SELECT w.WS_NUM, wc.ComponentID, c.ComponentDescription, wc.ComponentValue

                    FROM dbo.WORKSTAT w

                    INNER JOIN dbo.WORKSTATION_COMPONENTS wc

                    ON w.WS_NUM = wc.WS_NUM

                    INNER JOIN dbo.COMPONENTS c

                    ON wc.ComponentID = c.ComponentID

                    WHERE wc.IsCurrent=1 AND

                    wc.ComponentID NOT IN (65, 66, 67, 68, 69)

                    ORDER BY w.WS_NUM;

                    • 7. Re: I need an Audit History report

                      Haha. I can get along pretty good, but I don't think I'm that good.