• Locked Locked
  • Replies 5 replies
  • Subscribers 14 subscribers
  • Views 6939 views
  • Users 0 members are here
Options
Related
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Creating a report for Quest Knowledge Portal on specific Event IDs

nicole.h.dodd.ctr
over 7 years ago

Good Day!

I am trying to create a report that uses the data imported from a Repo to the Audit database to report on specific Event IDs in the security logs. Is there a way to create a report where I can type in any Event ID say 1104 and pull the computer and date that the event was triggered? I have several event IDs that are being requested that are not in the report packs and wanted to know how to generate a report for them with Quest Knowledge Portal.

 

 

Any feedback is greatly appreciated!

 

Thanks,

Nicole

Parents
  • over 7 years ago
    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"
Reply
  • over 7 years ago
    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"
Children
No Data