Splunk Search

Combined queries, matching unique rex values and comparing

mrhodes93
Explorer

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

Labels (4)
0 Karma
1 Solution

to4kawa
SplunkTrust
SplunkTrust

 

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.

View solution in original post

to4kawa
SplunkTrust
SplunkTrust

 

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.

View solution in original post

mrhodes93
Explorer

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

0 Karma

twesty
SplunkTrust
SplunkTrust

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
Explorer

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

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!