Creating a report for Quest Knowledge Portal on specific Event IDs

Hi Nicole,

Is your choice of Knowledge Portal deliberate and final? Because for such simple reports as you mentioned we now strongly recommend using Repository Viewer (RV) instead of Audit DB import + SSRS Report Packs + Knowledge Portal. You work with the Repository directly, and the design of reports in RV is much simpler, you can just drag and drop fields to create the layout, and then save report interactively or schedule it to pdf or csv. Please take a look on the 1104 report I've made in one minute in RV.

1104.pdf

Yes, Knowledge Portal is deliberate and final as there is a different group of individuals that would be reporting on these event IDs. I was looking to see if there was a simple way to get the report based on whichever event ID is being looked for via the Knowledge Portal. They don't have access to the Repo Viewer. This would be on going reporting for internal security requirements.

Thanks,
Nicole

I see. The simple way of creating reports is described for example here: https://docs.microsoft.com/en-us/sql/reporting-services/tutorial-creating-a-basic-table-report-report-builder. You download Report Builder, connect to SSRS (http://yourSSRS/reportserver), create a report, specify the data source (use InTrust shared data source which is /QKP/SharedDataSources/InTrust Audit), then create a simple query and simple layout. The query may be like this:

SELECT
  Events.EventID
  ,Events.GatheringComputer
  ,Events.Category
  ,Events.[LocalTime]
FROM
  Events
Where EventID=1104

After that you save the report in some folder to make it available for SSRS and QKP users.

For more complex reports you should learn the InTrust Events Model and all the relations in the DB which is quite complex, or order new reports via Quest Custom Development.

Igor:

Is there anyway to use this Query for different Event IDs? I want to be able to select from a date range and see the date, time, and computer that event was triggered on.


=" SET XACT_ABORT OFF EXEC('IF not (object_id(''" & Parameters!TEMP_MACRO.Value & "'') is null) DROP TABLE " & Parameters!TEMP_MACRO.Value & " IF not (object_id(''" & Parameters!TEMP_MACRO.Value & "_Session'') is null) DROP TABLE " & Parameters!TEMP_MACRO.Value & "_Session') SET XACT_ABORT ON EXEC('SELECT Events." & Replace(IIF(Parameters!LocalTime.Value = 0, "GMT", "LocalTime"), "'", "''") & " AS [EventDate], S1.StringValue AS [InitiatorName], S2.StringValue AS [InitiatorDomain], Events.GatheringComputer AS [Computer], Computers.Domain AS [Domain], FLOOR(CAST(Events." & Replace(IIF(Parameters!LocalTime.Value = 0, "GMT", "LocalTime"), "'", "''") & " AS FLOAT )) AS [FDate] INTO " & Parameters!TEMP_MACRO.Value & " FROM " & Parameters!ITFE_8.Value & "Events AS Events INNER JOIN " & Parameters!ITFE_8.Value & "Computers AS Computers ON Computers.ComputerName = Events.GatheringComputer INNER JOIN " & Parameters!ITFE_8.Value & "EventsStrings AS S1 ON S1.EventID = Events.ID AND S1.SessionID = Events.SessionID AND S1.StringIndex = CASE WHEN Events.EventID = 1102 THEN 2 ELSE 4 END INNER JOIN " & Parameters!ITFE_8.Value & "EventsStrings AS S2 ON S2.EventID = Events.ID AND S2.SessionID = Events.SessionID AND S2.StringIndex = CASE WHEN Events.EventID = 1102 THEN 3 ELSE 5 END WHERE (((Events.EventLog = ''Security'' AND Events.Source = ''Security'' AND Events.EventID = 517) OR (Events.EventLog = ''Security'' AND Events.Source = ''Microsoft-Windows-Eventlog'' AND Events.VersionMajor = 6 /*AND Events.VersionMinor = 0*/ AND Events.EventID = 1102)) ) AND (" & "(" & "(" & "(" & Replace(IIF( Parameters!Interval.Value Is Nothing, iif ( Parameters!StartDate.Value is Nothing OR Parameters!EndDate.Value is nothing, " (4>1) ", " [Events].[" & IIF(Parameters!LocalTime.Value = 0, "GMT", "LocalTime") & "] between '" & Format(Parameters!StartDate.Value, "yyyy-MM-ddTHH:mm:ss.fff") & "' and '" & Format(Parameters!EndDate.Value, "yyyy-MM-ddTHH:mm:ss.fff") & "' " ), " (3>1)") & " AND " & IIF(Parameters!Interval.Value Is Nothing, "(2>1)", "DATEADD(HOUR,-CAST(" & IIF(Left(Parameters!Interval.Value, 1) = "'", Parameters!Interval.Value, "N'" & Replace(Parameters!Interval.Value, "'", "''") & "'") & " AS INT), " & Parameters!CURRENT_TIME.Value & ")<=[Events].[" & Replace(IIF(Parameters!LocalTime.Value = 0, "GMT", "LocalTime"), "'", "''") & "]"), "'", "''") & ") AND (" & Code.ParamSQL_Computer_like_Item1(Parameters!Computer_like.Value) & ")" & ") AND (" & Code.ParamSQL_Initiator_like_Item1(Parameters!Initiator_like.Value) & ")" & ") AND (" & "NOT (" & Code.ParamSQL_Initiator_NOT_like_Item1(Parameters!Initiator_NOT_like.Value) & ")" & ")" & " )') SET XACT_ABORT ON EXEC('CREATE TABLE " & Parameters!TEMP_MACRO.Value & "_Session ( SessionID UNIQUEIDENTIFIER ) INSERT INTO " & Parameters!TEMP_MACRO.Value & "_Session VALUES ( NewID() )')
SELECT EventDate AS [EventDate], InitiatorDomain + N'\' + InitiatorName AS [Initiator], Computer AS [Computer], Domain AS [Domain], CAST(FDate AS DATETIME ) AS [Date], FDate AS [FDate] FROM " & Parameters!TEMP_MACRO.Value & " ORDER BY 4, 5, 1, 3"

Hi Nicole,

Unfortunately, I cannot describe here the full technology of creating reports with parameters like this one. And if you "edit" this "Event Log Cleared" report in Report Builder you'll see it doesn't allow to launch the query designer because the query was created manually. I see 3 ways for you from this point:

1. You may edit it manually at your own risk. Of course such simple changes can work only if events have the same structure (here 1102 and 1104):
   1. download the "Event Log Cleared" report to .rdl file
   2. change 1102 to 1104
   3. change "Event Log Was Cleared" to "Security Log Is Full"
   4. change description
   5. rename file to "Security Log Is Full.rdl"
   6. upload it to SSRS to the same folder
2. Use Report Builder with InTrust Model to create your own report:
   1. In SSRS click "New Paginated Report"
   2. Choose Blank Report
   3. Data Sources - Add MyDataSource - Browse - InTrust 10.0 model
   4. Data Set - Add Data Set - Use embedded, specify MyDataSource
   5. Query Designer - Generic Events
   6. Add fields that you want by double click, add filters: event log equals Security, event id equals 1104
   7. Drag and drop these fields to the page and type the caption.
3. But the recommended way is to order new reports via Quest support representative.