I have same problem, too!!
What information are you wanting, IP, vulnerability found? What information do you know going into this effort, IP, ticket number?
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.
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],
Tickets.Created AS [Ticket Create Date] ,
Tickets.DueDate AS [Ticket Due Date]
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
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'
dbo.Assets.Criticality DESC, dbo.Assets.IPAddress, Content.Vuln.Risk DESC
I had a PER(Product enhance request), but "NO" any responds.....yet!!
And thanks sonic, you are very Inconceivable!!
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!
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.
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.