topic Re: How to visualize percentage rate of matching event pairs that share a common key value? in Splunk Search

How to visualize percentage rate of matching event pairs that share a common key value?

beetlegeuse — Wed, 26 Oct 2022 21:46:18 GMT

I'm working on a query with the goal of determining the percentage rate of request/response event pairs that match by way of a common field value. The events share the same index, sourcetype and source; the field name, however, is different. The field in the request event is called "ID" while the field in the response event is called "InResponseTo".

NOTE: The response event also contains a field called "ID" which should be ignored.

Here is a sample of each type of event (note that these two events share the matching value "_907b4184-e85c-41f2-9a32-c1c735f01510")...

Request:

Oct 26 17:32:29 ServerX knick.knack [10/26/22, 17:32:20:292 EDT] 00018baf id=00000000 om.tivoli.am.fim.trustserver.sts.utilities.IDMappingExtUtils I traceString INFO --saml20-access-policy-utilities.js - [axamf4EhUmaVjkwwd+akl10BbjbDS1vVg6YJhu2F2E8=]:[protocolContext.getAuthnRequest()] <samlp:AuthnRequest xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:saml="urn:oasis:names:tc:SAML:2.0:assertion" xmlns:samlp="urn:oasis:names:tc:SAML:2.0:protocol" AssertionConsumerServiceURL="https://xyz.com" Destination="https://foobar.com" ForceAuthn="true" ID="_907b4184-e85c-41f2-9a32-c1c735f01510"</samlp:Response>

Response:

Oct 26 17:32:29 ServerX knick.knack [10/26/22, 17:32:20:455 EDT] 00018baf id=00000000 .am.fim.saml20.types.SAML20HTTPPostBrowserResponseWriterImpl 1 getStringMessage Request XML message: <samlp:Response xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:saml="urn:oasis:names:tc:SAML:2.0:assertion" xmlns:samlp="urn:oasis:names:tc:SAML:2.0:protocol" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Destination="https://dundermifflin.com" ID="FIMRSP_1635e05b-0184-1dc4-803d-f94b11a6d04e" InResponseTo="_907b4184-e85c-41f2-9a32-c1c735f01510"</samlp:Response>

The development flow:

Look at all request and response events for a given timeframe.
Sometimes, duplicate events show up...so they would need to be deduplicated.
If the same value is found for both "ID" and "InResponseTo", that's considered a 100% success rate. Mark it as a match. The sample events provided above would be an example of this.
If the same value is not found (i.e., there's a request event but no matching response event), that's considered a 0% success rate. Mark it as a non-match.
Add up the number of matches and divide the sum by the total number of request events found. Multiply the quotient by 100 to determine the percentage rate.
The results will be visualized using a simple line chart (timechart) showing the average percentage rate over time.

I'm able to compare the overall number of requests against the overall number of responses (regardless of matching field values) to produce a percentage rate, as shown here:

index=zig sourcetype=zag samlp:AuthnRequest | timechart count as RequestCount | appendcols [ search index=zig sourcetype=zag samlp:Response | timechart count as ResponseCount ] | eval ResponseRequestRatio = round(((ResponseCount/RequestCount) * 100),2) | table _time, RequestCount, ResponseCount, ResponseRequestRatio

How would I modify this query to accommodate the additional rules? I'm guessing that some sort of count could be used to determine the percentage rate, with a "1" for matches and a "0" (zero) for non-matches.

Re: How to visualize percentage rate of matching event pairs that share a common key value?

johnhuang — Wed, 26 Oct 2022 18:54:27 GMT

To help understand this better, could you provide some sample data and rather than using foo/bar, provide the fieldname and sample values?

If you can create a mockup of the end result, that would help as well.

Re: How to visualize percentage rate of matching event pairs that share a common key value?

beetlegeuse — Wed, 26 Oct 2022 19:59:05 GMT

Good recommendations for clarity of my question. I've edited my initial ask to include sample data and the actual field names being used.

The end result is essentially just a line chart that shows the average percentage rate over the timeframe being queried; nothing fancy. I plan on using a timechart command to address that.

Re: How to visualize percentage rate of matching event pairs that share a common key value?

johnhuang — Wed, 26 Oct 2022 21:03:27 GMT

Example of one way this could be done:

index=zig sourcetype=zag ("samlp:AuthnRequest" OR "samlp:Response") | eval event_id=COALESCE(ID, InResponseTo) | eval event_type=CASE(ISNOTNULL(ID), "request", ISNOTNULL(InResponseTo), "response") | stats MIN(_time) AS _time DC(event_type) AS dc_event_type BY event_id | eval event_type=CASE(dc_event_type=1, "failure_ct", dc_event_type=2, "success_ct") | bucket _time span=1h | chart count AS event_count by _time event_type | addtotals fieldname="total_ct" | eval success_ratio = ROUND(((success_ct/total_ct) * 100),2) | eval failure_ratio = ROUND(((failure_ct/total_ct) * 100),2)

Re: How to visualize percentage rate of matching event pairs that share a common key value?

beetlegeuse — Wed, 26 Oct 2022 21:41:20 GMT

I just gave this a try, and found that the failure_ct and total_ct are the same, with a failure_ratio of 100.0.

After reviewing the event data again, I did notice that the "ID" field also appears in the response event (but should be ignored for this use case). I've updated the sample events in this question to reflect that. Is this throwing off your query?

Re: How to visualize percentage rate of matching event pairs that share a common key value?

johnhuang — Wed, 26 Oct 2022 21:54:59 GMT

Yep the logic assumes the ID is null for the response event. Try this instead:

| eval event_type=CASE(MATCH(_raw, "samlp:AuthnRequest"), "request", MATCH(_raw, "samlp:Response"), "response")

Re: How to visualize percentage rate of matching event pairs that share a common key value?

beetlegeuse — Wed, 26 Oct 2022 22:18:09 GMT

Same result...100.0 failure ratio.

It looks like the coalesce statement is grabbing the ID value from the response event (instead of the InResponseTo value) and assigning it to a "response" event_type. I'm wondering if it's doing that since the ID value appears before the InResponseTo value in the event.

Re: How to visualize percentage rate of matching event pairs that share a common key value?

johnhuang — Wed, 26 Oct 2022 22:35:29 GMT

You can try reversing the order of the coalesce.

Re: How to visualize percentage rate of matching event pairs that share a common key value?

beetlegeuse — Wed, 26 Oct 2022 22:52:35 GMT

Reversing the order appears to have resulted in the correct field value being aligned with the event_type.

I noticed that some of the values have a single quote on either side of the value; how would I remove those quotes?

Re: How to visualize percentage rate of matching event pairs that share a common key value?

johnhuang — Wed, 26 Oct 2022 22:58:04 GMT

One of these depending what you mean by single quote.

| rex field=event_id mode=sed "s/\"//g"

| rex field=event_id mode=sed "s/\'//g"

Re: How to visualize percentage rate of matching event pairs that share a common key value?

beetlegeuse — Thu, 27 Oct 2022 04:58:39 GMT

Thank you so much! The additional recommendations for sed, reversal of the coalesce field order and the adjustment to the CASE MATCH statement that you provided, when applied to your original solution, has given me a working query that meets expectations.