How to extract from a single search with multiple conditions and display the statistics along with records

[anjihari]

I have logs with below format:

Feb 18 23:25:49 ip-x-x-x-x customerService 2020-02-18T23:25:49.340Z level=INFO component=customerServiceresource message="Request {"customerDetails":{"customerName":"Jack","bank":"xxx","action":"decline"}}

Feb 18 23:25:49 ip-x-x-x-x customerService 2020-02-18T23:25:49.340Z level=INFO component=customerServiceresource message="Request {"customerDetails":{"customerName":"mike","bank":"xxx","action":"approve"}}


Feb 18 23:25:49 ip-x-x-x-x customerService 2020-02-18T23:25:49.340Z level=INFO component=customerServiceresource message="Request {"customerDetails":{"customerName":"holden","bank":"yyy","action":"approve"}}

Feb 18 23:25:49 ip-x-x-x-x customerService 2020-02-18T23:25:49.340Z level=INFO component=customerServiceresource message="Request {"customerDetails":{"customerName":"leslie","bank":"yyy","action":"decline"}}

1) Get the count records where bank is xxx and action is approve
2) Get the count records where bank is yyy and action is decline
3) display all records that are having bank yyy and action is decline (displaying point 2)

[to4kawa]

| makeresults 
| eval data="Feb 18 23:25:49 ip-x-x-x-x customerService 2020-02-18T23:25:49.340Z level=INFO component=customerServiceresource message=\"Request {\"customerDetails\":{\"customerName\":\"Jack\",\"bank\":\"xxx\",\"action\":\"decline\"}}|Feb 18 23:25:49 ip-x-x-x-x customerService 2020-02-18T23:25:49.340Z level=INFO component=customerServiceresource message=\"Request {\"customerDetails\":{\"customerName\":\"mike\",\"bank\":\"xxx\",\"action\":\"approve\"}}|Feb 18 23:25:49 ip-x-x-x-x customerService 2020-02-18T23:25:49.340Z level=INFO component=customerServiceresource message=\"Request {\"customerDetails\":{\"customerName\":\"holden\",\"bank\":\"yyy\",\"action\":\"approve\"}}|Feb 18 23:25:49 ip-x-x-x-x customerService 2020-02-18T23:25:49.340Z level=INFO component=customerServiceresource message=\"Request {\"customerDetails\":{\"customerName\":\"leslie\",\"bank\":\"yyy\",\"action\":\"decline\"}}", data=split(data,"|") 
| mvexpand data 
| eval _raw=data 
| rex "(?<json>{.*)" 
| spath input=json 
| stats count(eval('customerDetails.bank'="xxx" AND 'customerDetails.action'="approve")) as ans1
    ,count(eval('customerDetails.bank'="yyy" AND 'customerDetails.action'="decline")) as ans2
    ,values(eval(if('customerDetails.bank'="yyy" AND 'customerDetails.action'="decline",_raw,null()))) as ans3

[kamlesh_vaghela]

@anjihari

You can extract bank and action using rex command. https://docs.splunk.com/Documentation/SplunkInvestigate/Current/SearchReference/RexCommandOverview

YOUR_SEARCH | rex field=_raw "\"bank\":\"(?<bank>.+)\",\"action\":\"(?<action>.+)\"" 

1) Get the count records where bank is xxx and action is approve

YOUR_SEARCH | rex field=_raw "\"bank\":\"(?<bank>.+)\",\"action\":\"(?<action>.+)\"" | where bank="xxx" AND action="approve" | stats count

Sample:

| makeresults 
| eval data="Feb 18 23:25:49 ip-x-x-x-x customerService 2020-02-18T23:25:49.340Z level=INFO component=customerServiceresource message=\"Request {\"customerDetails\":{\"customerName\":\"Jack\",\"bank\":\"xxx\",\"action\":\"decline\"}}|Feb 18 23:25:49 ip-x-x-x-x customerService 2020-02-18T23:25:49.340Z level=INFO component=customerServiceresource message=\"Request {\"customerDetails\":{\"customerName\":\"mike\",\"bank\":\"xxx\",\"action\":\"approve\"}}|Feb 18 23:25:49 ip-x-x-x-x customerService 2020-02-18T23:25:49.340Z level=INFO component=customerServiceresource message=\"Request {\"customerDetails\":{\"customerName\":\"holden\",\"bank\":\"yyy\",\"action\":\"approve\"}}|Feb 18 23:25:49 ip-x-x-x-x customerService 2020-02-18T23:25:49.340Z level=INFO component=customerServiceresource message=\"Request {\"customerDetails\":{\"customerName\":\"leslie\",\"bank\":\"yyy\",\"action\":\"decline\"}}", data=split(data,"|") 
| mvexpand data 
| eval _raw=data 
| extract 
| rename comment as "Upto this is for data generation only" 
| rex field=_raw "\"bank\":\"(?<bank>.+)\",\"action\":\"(?<action>.+)\"" | where bank="xxx" AND action="approve" | stats count

2) Get the count records where bank is yyy and action is decline

YOUR_SEARCH | rex field=_raw "\"bank\":\"(?<bank>.+)\",\"action\":\"(?<action>.+)\"" | where bank="yyy" AND action="decline" | stats count

Sample:

| makeresults 
| eval data="Feb 18 23:25:49 ip-x-x-x-x customerService 2020-02-18T23:25:49.340Z level=INFO component=customerServiceresource message=\"Request {\"customerDetails\":{\"customerName\":\"Jack\",\"bank\":\"xxx\",\"action\":\"decline\"}}|Feb 18 23:25:49 ip-x-x-x-x customerService 2020-02-18T23:25:49.340Z level=INFO component=customerServiceresource message=\"Request {\"customerDetails\":{\"customerName\":\"mike\",\"bank\":\"xxx\",\"action\":\"approve\"}}|Feb 18 23:25:49 ip-x-x-x-x customerService 2020-02-18T23:25:49.340Z level=INFO component=customerServiceresource message=\"Request {\"customerDetails\":{\"customerName\":\"holden\",\"bank\":\"yyy\",\"action\":\"approve\"}}|Feb 18 23:25:49 ip-x-x-x-x customerService 2020-02-18T23:25:49.340Z level=INFO component=customerServiceresource message=\"Request {\"customerDetails\":{\"customerName\":\"leslie\",\"bank\":\"yyy\",\"action\":\"decline\"}}", data=split(data,"|") 
| mvexpand data 
| eval _raw=data 
| extract 
| rename comment as "Upto this is for data generation only" 
| rex field=_raw "\"bank\":\"(?<bank>.+)\",\"action\":\"(?<action>.+)\"" | where bank="yyy" AND action="decline" | stats count

3) display all records that are having bank yyy and action is decline (displaying point 2)

YOUR_SEARCH | rex field=_raw "\"bank\":\"(?<bank>.+)\",\"action\":\"(?<action>.+)\"" | where bank="yyy" AND action="decline" | table *

Sample:

| makeresults 
| eval data="Feb 18 23:25:49 ip-x-x-x-x customerService 2020-02-18T23:25:49.340Z level=INFO component=customerServiceresource message=\"Request {\"customerDetails\":{\"customerName\":\"Jack\",\"bank\":\"xxx\",\"action\":\"decline\"}}|Feb 18 23:25:49 ip-x-x-x-x customerService 2020-02-18T23:25:49.340Z level=INFO component=customerServiceresource message=\"Request {\"customerDetails\":{\"customerName\":\"mike\",\"bank\":\"xxx\",\"action\":\"approve\"}}|Feb 18 23:25:49 ip-x-x-x-x customerService 2020-02-18T23:25:49.340Z level=INFO component=customerServiceresource message=\"Request {\"customerDetails\":{\"customerName\":\"holden\",\"bank\":\"yyy\",\"action\":\"approve\"}}|Feb 18 23:25:49 ip-x-x-x-x customerService 2020-02-18T23:25:49.340Z level=INFO component=customerServiceresource message=\"Request {\"customerDetails\":{\"customerName\":\"leslie\",\"bank\":\"yyy\",\"action\":\"decline\"}}", data=split(data,"|") 
| mvexpand data 
| eval _raw=data 
| extract | table _raw
| rename comment as "Upto this is for data generation only" 
| rex field=_raw "\"bank\":\"(?<bank>.+)\",\"action\":\"(?<action>.+)\"" | where bank="yyy" AND action="decline" | table *

I hope this will help you.

Thanks

[alonsocaio]

Hi,

I guess that you could try some searches like below:

1)

| search bank="xxx" AND action="approve"
| stats count

2)

| search bank="yyy" AND action="decline"
| stats count

3)

| search bank="yyy" AND action="decline"

Using just one search we would have something like this:

index="IDX" source="log_sample.log" bank="xxx" AND action="approve" 
| stats count as xxx_approve 
| appendcols 
    [| search index="IDX" source="log_sample.log" bank="yyy" AND action="decline" 
    | stats count as yyy_decline] 
| appendcols 
    [| search index="IDX" source="log_sample.log" bank="yyy" AND action="decline"
    | stats values(_raw) as yyy_decline_value]

Let me know if this helps you or you need anything else.