8 Replies Latest reply on Apr 16, 2012 2:32 PM by adionne

    Querying the DB for remediation ticket reporting

      I note that some folks are querying the DB directly to extract remediation ticket info.  Could someone give me a sample script?  I think we're going to have to do something like this.  We're running 7.0.3, on an MVM3100.

       

      Thanks in advance.

       

      J.

        • 1. Re: Querying the DB for remediation ticket reporting
          jackson1978

          I have same problem, too!!

           

          Anyone help?

          • 2. Re: Querying the DB for remediation ticket reporting
            sonic

            What information are you wanting, IP, vulnerability found?  What information do you know going into this effort, IP, ticket number?

            • 3. Re: Querying the DB for remediation ticket reporting

              I think I would like to get a report of all tickets that have a status other than 'closed', and would want (probably)

              ticket status, assigned to, asset owner, IP, vuln found, when ticket was created, when ticket is due

               

              Once I had that in a CSV, I could probably do something with it. Maybe.

               

              What I would *really* like is for the various views that exist in the ticket reporting model to have an 'export as CSV' button.  And also the ability to display (filter) by ticket status.  I guess I should submit a feature request.

               

              J.

              • 4. Re: Querying the DB for remediation ticket reporting
                sonic

                 

                 

                SELECT Tickets.TicketID,

                            Tickets.Status ,

                            Users_Tickets.FirstName + ' ' + Users_Tickets.LastName AS [Assigned To],

                            Users.FirstName + ' ' + Users.LastName AS [Asset Owner],

                            Assets.IPAddressStr AS [IPAddress] ,

                            Assets.Criticality AS [Asset Criticality],

                        Content.vuln.Name AS[Vulnerability],

                        Content.vuln.Risk,

                        Tickets.Created AS [Ticket Create Date] ,

                        Tickets.DueDate AS [Ticket Due Date]

                FROM tickets

                JOIN dbo.assets ON dbo.Tickets.AssetID = dbo.Assets.AssetID

                JOIN Content.Vuln ON dbo.Tickets.FaultlineID = Content.Vuln.FaultlineID

                --LEFT OUTER JOIN Users in case a user is not selected either as the Asset owner or assigned to a ticket

                LEFT OUTER JOIN dbo.Users ON dbo.Assets.UserID = dbo.Users.UserID

                LEFT OUTER JOIN dbo.Users ASUsers_Tickets ON dbo.Tickets.AssignedTo =Users_Tickets.UserID

                 

                WHERE

                dbo.Tickets.Status NOT IN ( 'closed', 'Auto-closed', 'Complete')

                -- Add data limiters below, otherwise you will pull a ton of data

                -- Look for Assets found aka "scanned" in the last 11 days, current date is 8/31/2011

                AND dbo.Assets.lastfounddatetime > '2011-08-20 00:00:00.000'

                 

                ORDER BY

                dbo.Assets.Criticality DESC, dbo.Assets.IPAddress, Content.Vuln.Risk DESC

                • 5. Re: Querying the DB for remediation ticket reporting
                  jackson1978

                  I had a PER(Product enhance request), but "NO" any responds.....yet!!

                   

                  And thanks sonic, you are very Inconceivable!!

                  awesome

                  • 6. Re: Querying the DB for remediation ticket reporting
                    adionne

                    Sonic,

                     

                    If i were to want to also add into the WHERE section a filter for a specific user's assigned tickets, what would I need to add?

                     

                    Thanks a lot!

                    • 7. Re: Querying the DB for remediation ticket reporting
                      sonic

                      Adionne,

                       

                      At the end of the WHERE clause, add the following line...

                       

                      AND Users_Tickets.Name = 'adionne'

                       

                      You set the Users_Tickets.Name equal to the User Name that is used at the login screen of MVM.  All User Names used to log into MVM are listed in the Users table, to see them, execute the query SELECT Name from Users ORDER BY Name.  This is the same list you see when manually assigning tickets to users.

                       

                      Hope this helps.

                      Sonic

                      • 8. Re: Querying the DB for remediation ticket reporting
                        adionne

                        That's perfect, That will help with compliance reports.

                         

                        I've got one more query question, but I will try to see if i can figure it out on my own to filter by asset owner, and report back.

                         

                        Thanks again!