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
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.
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.
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
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
Thank you both so much, both for the information and the explanations! Minor regex tinkering got this working as expected!