topic Combined queries, matching unique rex values and comparing in Splunk Search

Combined queries, matching unique rex values and comparing

mrhodes93 — Thu, 11 Jun 2020 17:35:53 GMT

Hi all,

I've got two queries I'm trying to combine to track authorizations that are completed, or expire after a period of seven days. The first query gets all of the authorizations sent, filtered by a unique AccountNum.

Query 1:

earliest=-8d@d latest=-7d@d sourcetype="PCF:log" cf_app_name=app1 "Sending authorization" | rex field=msg "BAN: (?<AccountNum>\w+)" | dedup AccountNum

The second query returns all authorizations that have expired after a period of inactivity.

Query 2:

earliest=@d latest=now sourcetype="PCF:log" cf_app_name=app2 "authorizationExpired" | rex field=msg ",ban:(?<AccountNum>\w+)" | dedup AccountNum

The closest I've gotten to combining them how I need is:

Query 3:

earliest=-8d@d latest=-7d@d sourcetype="PCF:log" cf_app_name=app1 "Sending authorization" | rex field=msg "BAN: (?<AccountNum>\w+)" | dedup AccountNum | append [search earliest=@d latest=now sourcetype="PCF:log" cf_app_name=app2 "authorizationExpired" | rex field=msg ",ban:(?<AccountNum>\w+)" | dedup AccountNum ] | fields msg | eval action=case( match(msg,"Sending authorization+"), "Total Authorizations Sent", match(msg,"authorizationExpired+"), "Authorizations Expired") | stats count(msg) by action

However, there are two mistakes/gaps with this third query. The first problem is I need the second query to only return results where AccountNum in query 2 is matching an AccountNum in query 1.

Secondly, I'd like to have a pie chart of Authorizations Expired (query 2) vs Authorizations Complete (total - expired = complete) but I'm struggling with the syntax on how to achieve that. This third query shows total + expired, where expired is actually a subset of total.

I guess a third thing would be I don't know that append is really what I need or if there's a better, more performant way to construct this query? I'd love to learn any helpful tips or tricks!

Greatly appreciate any help

Re: Combined queries, matching unique rex values and comparing

to4kawa — Thu, 11 Jun 2020 17:59:04 GMT

sourcetype="PCF:log" (earliest=-8d@d latest=-7d@d cf_app_name=app1 "Sending authorization" ) OR (earliest=@d latest=now cf_app_name=app2 "authorizationExpired" ) | rex field=msg "BAN: (?<AccountNum>\w+)" | stats count dc(cf_app_name) as flag values(cf_app_name) as cf_app_name by AccountNum | stats count as total count(eval(flag=2)) as expired | eval complete = total - expired | fields - complete

In this case, cf_app_name can be used to determine expire or not.

Re: Combined queries, matching unique rex values and comparing

mrhodes93 — Thu, 11 Jun 2020 19:40:07 GMT

I haven't yet tried the query but wondered if this will work as the regex for identifying the AccountNum varies slightly between the two queries?

rex field=msg "BAN: (?<BAN>\w+)" - space between colon and var value

rex field=msg ",ban:(?<BAN>\w+)" - prefixed comma, no space between colon and var value

Re: Combined queries, matching unique rex values and comparing

twesty — Thu, 11 Jun 2020 20:47:53 GMT

You can merge the two regex strings together for this

rex field=msg "BAN: (?<BAN>\w+)" - space between colon and var value

rex field=msg ",ban:(?<BAN>\w+)" - prefixed comma, no space between colon and var value

can just become

rex field=msg "(BAN|,ban): ?(?<BAN>'w+)"

The first part is a grouped OR statement specifying that the string must be either BAN or ,ban. The second change is the ? after space. this indicates the space is optional. Please make sure, however, that this pattern does in fact work with your data and I would advise running a test set in something like regex101

Re: Combined queries, matching unique rex values and comparing

mrhodes93 — Thu, 11 Jun 2020 22:54:04 GMT

Thank you both so much, both for the information and the explanations! Minor regex tinkering got this working as expected!