Combined queries, matching unique rex values and comparing

[mrhodes93]

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

[to4kawa · Accepted solution]

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.

[mrhodes93]

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

[twesty]

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

[mrhodes93]

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