topic Re: Is there a better way to search than appendcols ? in Splunk Search

Is there a better way to search than appendcols ?

chsuresh09 — Mon, 21 Mar 2022 18:37:49 GMT

Hi Guys,

I am looking search thru, splunk index for presence of multiple conditions as below.

index = "ind_name" return object|bin _time span=1d |
where log like "%'feature1': {'result': '-9999%" | stats count as cnt_feature1_NOT_NULL by _time |
appendcols [search index = "ind_name" return object |bin _time span=1d | where log like "%'feature1': {'result': '%" | stats count as cnt_feature1_NOT_NOT_NULL by _time] |
appendcols [search index = "ind_name" return object |bin _time span=1d | where log like "%'feature2': {'result': '-9999%"| stats count as cnt_feature2_NULL by _time] |
appendcols [search index = "ind_name" return object |bin _time span=1d | where log like "%'feature2': {'result': '%" | stats count as cnt_feature2_NOT_NOT_NULL by _time] |

I have to search for multiple expressions and count them (20) of them, is there a better way to search than appendcols ?

Thank you

Re: multiple where clauses and count by

gcusello — Mon, 21 Mar 2022 09:37:54 GMT

Hi @chsuresh09,

at first, you don't need to use where instead to put the where condition in the main search, rather it's a bad idea because in this way you have a slower search.

Then I don't understand what's your need and I'm not sure that the appencols correctly wpork for you.

What's your requisite?

could you share a sample of the results you're waiting for?

I try to suppose your need, maybe you want something like this:

index = "ind_name" return object | rex "\'(?<feature>[^\']+)\':\s+\{\'result\':\s+(?<result>[^\']+)\'" | eval type="cnt_".feature."_".if(result="-9999","NULL","NOT NULL") | timechart count BY type span=1d

I'm not so sure about the regex, if you could share some sample of your logs I could be more detailed.

Ciao.

Giuseppe

Re: multiple where clauses and count by

chsuresh09 — Mon, 21 Mar 2022 09:59:45 GMT

Thanks for response, I am not very efficient with Splunk, my log file looks like this. feature1 can be 'BALANCE_MAX1_10000_SEC_EXT_WOE_INT', I have to search for all products and see if they are NULL or not NULL.

{'requestId': 'cibilcreditscore/6a373431-014c-4531-8332-746c5033e85c', 'entity': 'CIBIL_CHECK', 'results': {'BALANCE_MAX1_10000_SEC_EXT_WOE_INT': {'result': '-9999', 'version': 'v4'}, 'BALANCE_MEAN1_10000_APPROVED_AMT_MEAN1_10000_POS_EXT_WOE_INT': {'result': '-9999.0', 'version': 'v4'}, 'CIBIL_ACC_DTCLOSED_FIRSTCLEND': {'result': '-9999', 'version': 'v4'}, 'CIBIL_ACC_LIMIT_OPEN_REV': {'result': '-9999', 'version': 'v4'}, 'CIBIL_CC_AMTOVERDUE_SUM': {'result': '-9999', 'version': 'v4'}, 'CIBIL_DPD_LAST_5DPD': {'result': '-9999', 'version': 'v4'}, 'CIBIL_DPD_MAX_13_18_NUM': {'result': '-9999', 'version': 'v4'}, 'CIBIL_DPD_RATE_10DPD_3M': {'result': '-9999', 'version': 'v4'}, 'CIBIL_ENQ_CNT_90D': {'result': '-9999', 'version': 'v4'}, 'CIBIL_ENQ_LN_NOHC_CNT_24M': {'result': '2', 'version': 'v4'}, 'CIBIL_ENQ_NOHC_TMSNC_LAST': {'result': '0', 'version': 'v4'}, 'CIBIL_NOHC_INSTL_PAID_NORMALLY_60M': {'result': '-9999', 'version': 'v4'}, 'CIBIL_P_OCCUPATION': {'result': 'N/A', 'version': 'v4'}, 'CIBIL_SCORE': {'result': '171.0', 'version': 'v4'}, 'DPD_NUM_ADJ_MAX1_10000_MEAN1_10000_SEC_EXT_WOE_INT': {'result': '-9999.0', 'version': 'v4'}, 'DPD_NUM_ADJ_MEAN1_10000_EXT_WOE_INT': {'result': '-9999.0', 'version': 'v4'}, 'FLAG_Cons_Loan_SUM1_EXT': {'result': '2', 'version': 'v4'}, 'FLAG_ENQ_ANY_SUM1_90_EXT': {'result': '2', 'version': 'v4'}, 'TS_ENQ_F_D': {'result': '0', 'version': 'v4'}, 'TS_OPEN_DT_F_SEC_CLS_D_EXT': {'result': '-9999', 'version': 'v4'}, 'TS_OPEN_DT_F_SEC_STAT_UNIF_CLSD_D_EXT_WOE_INT': {'result': '-9999', 'version': 'v4'}, 'cbActiveEmi': {'result': '-9999', 'version': 'v3'}, 'cbDisposableIncome': {'result': '-9999', 'version': 'v2'}, 'cbDpdMax18mNum': {'result': '-9999', 'version': 'v4'}, 'cbDpdRate1Dpd12m': {'result': '-9999', 'version': 'v4'}, 'cbEnqCnt90d': {'result': '-9999', 'version': 'v4'}, 'cbEstimatedIncome': {'result': '-9999', 'version': 'v3'}, 'ciAccVehClosedCnt': {'result': '-9999', 'version': 'v2'}, 'ciAmountMax1180Mean1180': {'result': '1.606425702811245', 'version': 'v6'}, 'ciAmountMean110000': {'result': '62250.0', 'version': 'v6'}, 'ciAmtOverdue2BalSum90': {'result': '-9999.0', 'version': 'v7'}, 'ciAmtOverdue2FaOvdRtExt': {'result': '-9999', 'version': 'v2'},

Re: multiple where clauses and count by

chsuresh09 — Mon, 21 Mar 2022 10:06:35 GMT

I want to search for BALANCE_MAX1_10000_SEC_EXT_WOE_INT, if it is followed by 'result': '-9999', then I count it as NULL else I count it as NOT NULL, I want to count all such features (e.g. CIBIL_DPD_LAST_5DPD).

Re: multiple where clauses and count by

gcusello — Mon, 21 Mar 2022 10:44:01 GMT

Hi @chsuresh09,

please try this:

You can check the regex at https://regex101.com/r/q2bKfP/1

Ciao.

Giuseppe

Re: multiple where clauses and count by

chsuresh09 — Mon, 21 Mar 2022 12:52:43 GMT

Thank you, it is matching all the variables in search result, may I know how to calculate statistics other than count, for example percent of null (which is given by cnt_feature_NULL/(cnt_feature_NULL + cnt_deature_NOT_NULL) also the span=1d is giving data for every 5 minutes, how can I limit it to last 4hours, meaning I want one count for each 4 hours. I did try to find answers by self, but could not. Thanks for your time.

Re: multiple where clauses and count by

gcusello — Mon, 21 Mar 2022 13:16:12 GMT

Hi @chsuresh09,

about statistics, you can elaborate results after the stats command, e.g.

index = "ind_name" return object | rex "\'results\':\s+\{\'(?<feature>[^\']+)\':\s+\{\'result\':\s+\'(?<result>[^\']+)" | eval type="cnt_".feature."_".if(result="-9999","NULL","NOT NULL") | bin _time span=1d | stats count(eval(result="-9999")) AS NULL count(eval(result!="-9999")) AS NOT_NULL count AS total BY _time type | eval perc_null=(NULL/total)*100, perc_not_null=(NOT_NULL/total)*100

about the duration, you can choose the time period and the span you like:

time period using the Time Picker or adding the time modifiers,
span modifying the value in the bin command.

Ciao.

Giuseppe

Re: Is there a better way to search than appendcols ?

chsuresh09 — Tue, 22 Mar 2022 06:23:36 GMT

Hi, If someone can help, My log file is as below :

2022.03.22 06:05:16 ["6c74f67eff58131d" "0e056f566ee8453bac585b95ab0a2eed"] [MainProcess] INFO Task completed in 39.15966860949993 ms. Return object {'requestId': 'cibilcreditscore/11109dac-a807-4022-9a48-471f717a88f1', 'entity': 'CIBIL_CHECK', 'results': {'BALANCE_MAX1_10000_SEC_EXT_WOE_INT': {'result': '-9999', 'version': 'v4'}, 'BALANCE_MEAN1_10000_APPROVED_AMT_MEAN1_10000_POS_EXT_WOE_INT': {'result': '-9999.0', 'version': 'v4'}, 'CIBIL_ACC_DTCLOSED_FIRSTCLEND': {'result': '-9999', 'version': 'v4'}, 'CIBIL_ACC_LIMIT_OPEN_REV': {'result': '-9999', 'version': 'v4'}, 'CIBIL_CC_AMTOVERDUE_SUM': {'result': '-9999', 'version': 'v4'}, 'CIBIL_DPD_LAST_5DPD': {'result': '-9999', 'version': 'v4'}, 'CIBIL_DPD_MAX_13_18_NUM': {'result': '-9999', 'version': 'v4'}, 'CIBIL_DPD_RATE_10DPD_3M': {'result': '-9999', 'version': 'v4'}, 'CIBIL_ENQ_CNT_90D': {'result': '-9999', 'version': 'v4'}, 'CIBIL_ENQ_LN_NOHC_CNT_24M': {'result': '3', 'version': 'v4'}, 'CIBIL_ENQ_NOHC_TMSNC_LAST': {'result': '0', 'version': 'v4'}, 'CIBIL_NOHC_INSTL_PAID_NORMALLY_60M': {'result': '-9999', 'version': 'v4'}, 'CIBIL_P_OCCUPATION': {'result': 'N/A', 'version': 'v4'}, 'CIBIL_SCORE': {'result': '150.0', 'version': 'v4'}, 'DPD_NUM_ADJ_MAX1_10000_MEAN1_10000_SEC_EXT_WOE_INT': {'result': '-9999.0', 'version': 'v4'}, 'DPD_NUM_ADJ_MEAN1_10000_EXT_WOE_INT': {'result': '-9999.0', 'version': 'v4'}, 'FLAG_Cons_Loan_SUM1_EXT': {'result': '1', 'version': 'v4'}, 'FLAG_ENQ_ANY_SUM1_90_EXT': {'result': '3', 'version': 'v4'}, 'TS_ENQ_F_D': {'result': '19', 'version': 'v4'}, 'TS_OPEN_DT_F_SEC_CLS_D_EXT': {'result': '-9999', 'version': 'v4'}, 'TS_OPEN_DT_F_SEC_STAT_UNIF_CLSD_D_EXT_WOE_INT': {'result': '-9999', 'version': 'v4'}, 'cbActiveEmi': {'result': '-9999', 'version': 'v3'}, 'cbDisposableIncome': {'result': '-9999', 'version': 'v2'}, 'cbDpdMax18mNum': {'result': '-9999', 'version': 'v4'}, 'cbDpdRate1Dpd12m': {'result': '-9999', 'version': 'v4'}, 'cbEnqCnt90d': {'result': '-9999', 'version': 'v4'}, 'cbEstimatedIncome': {'result': '-9999', 'version': 'v3'}, 'ciAccVehClosedCnt': {'result': '-9999', 'version': 'v2'}, 'ciAmountMax1180Mean1180': {'result': '2.5141069333482315', 'version': 'v6'}, 'ciAmountMean110000': {'result': '59663.333333333336', 'version': 'v6'}, 'ciAmtOverdue2BalSum90': {'result': '-9999.0', 'version': 'v7'}, 'ciAmtOverdue2FaOvdRtExt': {'result': '-9999', 'version': 'v2'}, 'ciAmtOverdueSum1360': {'result': '-9999', 'version': 'v4'}, 'ciBalanceMax110000Sec': {'result': '-9999', 'version': 'v4'}, 'ciBalanceMean1360Cl': {'result': '-9999.0', 'version': 'v4'}, 'ciBalanceMeanCl': {'result': '-9999.0', 'version': 'v6'}, 'ciCbAccTenureSum': {'result': '-9999', 'version': 'v6'}, 'ciCntRestructure': {'result': '0', 'version': 'v2'}, 'ciDpd15EverPct': {'result': '-9999', 'version': 'v2'}, 'ciDpdCntNoOvdEver': {'result': '-9999', 'version': 'v2'}, 'ciDpdMeanClsd': {'result': '-9999.0', 'version': 'v6'}, 'ciDpdNumAdjMax110000Mean110000': {'result': '-9999.0', 'version': 'v4'}, 'ciDpdNumAdjMax1360': {'result': '-9999.0', 'version': 'v4'}, 'ciDpdNumAdjMax190': {'result': '-9999.0', 'version': 'v4'}, 'ciDpdNumAdjMean110000Clsd': {'result': '-9999.0', 'version': 'v4'}, 'ciEnqAmtMax2Mean180': {'result': '2.5141069333482315', 'version': 'v6'}, 'ciEnqAmtMeanEver': {'result': '59663.333333333336', 'version': 'v6'}, 'ciEnqCnt360': {'result': '3', 'version': 'v6'}, 'ciFlagConsLoanSum110000Ext': {'result': '1', 'version': 'v4'}, 'ciFlagEnqAnySum190Ext': {'result': '3', 'version': 'v4'}, 'ciLastOverdueMonthCntPos': {'result': '-9999', 'version': 'v2'}, 'ciTsEnqFD': {'result': '19', 'version': 'v4'}, 'ciTsEnqLD': {'result': '8', 'version': 'v4'}, 'ciTsFDpdNumAdj00D': {'result': '-9999.0', 'version': 'v4'}}, 'cuid': 27040685}; Timestamp of request: 2022-03-22 06:05:13.618000

Using the below reg ex to match and count NULL vs NOT NULL, NULL are represented as {'result': '-9999.0',... or {'result': '-9999', ...

index = "index" return object
| rex "\s*\'(?<feature>[^\']+)\':\s+\{\'result\':\s+\'(?<result>[^\']+)"
| eval type="cnt_".feature."_".if((result="-9999" OR result="-9999.0"),"NULL","NOT_NULL")
| bin _time span=1d
| stats count(eval(result="-9999" OR result="-9999.0")) AS NULL count(eval(result!="-9999" AND result!="-9999.0")) AS NOT_NULL count AS total BY _time type
| eval perc_null=(NULL/total)*100, perc_not_null=(NOT_NULL/total)*100

The problem is the regular expression is not matching all of the features, for example it is not matching cbEstimatedIncome, ciCntRestructure, even though in this tool : https://regex101.com/r/q2bKfP/1 it is shown as matching. I am struggling with Splunk, any help is appreciated.

Re: Is there a better way to search than appendcols ?

gcusello — Tue, 22 Mar 2022 06:38:15 GMT

Hi @chsuresh09,

try with this regex:

(?ms)\s*\'(?<feature>[^\']+)\':\s+\{\'result\':\s+\'(?<result>[^\']+)

Ciao.

Giuseppe

Re: Is there a better way to search than appendcols ?

chsuresh09 — Tue, 22 Mar 2022 06:51:30 GMT

Thanks Giuseppe,

It still is matching only with {'BALANCE_MAX1_10000_SEC_EXT_WOE_INT', in the log below and not with the others, for example BALANCE_MEAN1_10000_APPROVED_AMT_MEAN1_10000_POS_EXT_WOE_INT.

2022.03.22 06:42:22 ["da691fa5b2c902b3" "8ab5760debae5ab3"] [MainProcess] INFO Task completed in 178.42819169163704 ms. Return object {'requestId': 'cibilcreditscore/ceca2f65-46fb-48bb-9ea4-c146855ec921', 'entity': 'CIBIL_CHECK', 'results': {'BALANCE_MAX1_10000_SEC_EXT_WOE_INT': {'result': '-9999', 'version': 'v4'}, 'BALANCE_MEAN1_10000_APPROVED_AMT_MEAN1_10000_POS_EXT_WOE_INT': {'result': '0.0', 'version': 'v4'}, 'CIBIL_ACC_DTCLOSED_FIRSTCLEND': {'result': '9', 'version': 'v4'}, 'CIBIL_ACC_LIMIT_OPEN_REV': {'result': '0', 'version': 'v4'}, 'CIBIL_CC_AMTOVERDUE_SUM': {'result': '-9999', 'version': 'v4'}, 'CIBIL_DPD_LAST_5DPD': {'result': '0', 'version': 'v4'}, 'CIBIL_DPD_MAX_13_18_NUM': {'result': '2.0', 'version': 'v4'}, 'CIBIL_DPD_RATE_10DPD_3M': {'result': '-9999', 'version': 'v4'}, 'CIBIL_ENQ_CNT_90D': {'result': '0', 'version': 'v4'}, 'CIBIL_ENQ_LN_NOHC_CNT_24M': {'result': '1', 'version': 'v4'}, 'CIBIL_ENQ_NOHC_TMSNC_LAST': {'result': '19', 'version': 'v4'}, 'CIBIL_NOHC_INSTL_PAID_NORMALLY_60M': {'result': '1.0', 'version': 'v4'}, 'CIBIL_P_OCCUPATION': {'result': 'N/A', 'version': 'v4'}, 'CIBIL_SCORE': {'result': '757.0', 'version': 'v4'}, 'DPD_NUM_ADJ_MAX1_10000_MEAN1_10000_SEC_EXT_WOE_INT': {'result': '-9999.0', 'version': 'v4'}, 'DPD_NUM_ADJ_MEAN1_10000_EXT_WOE_INT': {'result': '0.0', 'version': 'v4'}, 'FLAG_Cons_Loan_SUM1_EXT': {'result': '1', 'version': 'v4'}, 'FLAG_ENQ_ANY_SUM1_90_EXT': {'result': '-9999', 'version': 'v4'}, 'TS_ENQ_F_D': {'result': '591', 'version': 'v4'}, 'TS_OPEN_DT_F_SEC_CLS_D_EXT': {'result': '-9999', 'version': 'v4'}, 'TS_OPEN_DT_F_SEC_STAT_UNIF_CLSD_D_EXT_WOE_INT': {'result': '-9999', 'version': 'v4'}, 'cbActiveEmi': {'result': '0.0', 'version': 'v3'}, 'cbDisposableIncome': {'result': '1066.0958185894788', 'version': 'v2'}, 'cbDpdMax18mNum': {'result': '2.0', 'version': 'v4'}, 'cbDpdRate1Dpd12m': {'result': '0.0', 'version': 'v4'}, 'cbEnqCnt90d': {'result': '0', 'version': 'v4'}, 'cbEstimatedIncome': {'result': '5066.095818589479', 'version': 'v3'}, 'ciAccVehClosedCnt': {'result': '-9999', 'version': 'v2'}, 'ciAmountMax1180Mean1180': {'result': '-9999.0', 'version': 'v6'}, 'ciAmountMean110000': {'result': '15990.0', 'version': 'v6'}, 'ciAmtOverdue2BalSum90': {'result': '-9999.0', 'version': 'v7'}, 'ciAmtOverdue2FaOvdRtExt': {'result': '-9999', 'version': 'v2'}, 'ciAmtOverdueSum1360': {'result': '-9999', 'version': 'v4'}, 'ciBalanceMax110000Sec': {'result': '-9999', 'version': 'v4'}, 'ciBalanceMean1360Cl': {'result': '-9999.0', 'version': 'v4'}, 'ciBalanceMeanCl': {'result': '-9999.0', 'version': 'v6'}, 'ciCbAccTenureSum': {'result': '12', 'version': 'v6'}, 'ciCntRestructure': {'result': '0', 'version': 'v2'}, 'ciDpd15EverPct': {'result': '-9999', 'version': 'v2'}, 'ciDpdCntNoOvdEver': {'result': '12', 'version': 'v2'}, 'ciDpdMeanClsd': {'result': '0.0', 'version': 'v6'}, 'ciDpdNumAdjMax110000Mean110000': {'result': '-9999.0', 'version': 'v4'}, 'ciDpdNumAdjMax1360': {'result': '0.0', 'version': 'v4'}, 'ciDpdNumAdjMax190': {'result': '-9999.0', 'version': 'v4'}, 'ciDpdNumAdjMean110000Clsd': {'result': '0.0', 'version': 'v4'}, 'ciEnqAmtMax2Mean180': {'result': '-9999.0', 'version': 'v6'}, 'ciEnqAmtMeanEver': {'result': '15990.0', 'version': 'v6'}, 'ciEnqCnt360': {'result': '-9999', 'version': 'v6'}, 'ciFlagConsLoanSum110000Ext': {'result': '1', 'version': 'v4'}, 'ciFlagEnqAnySum190Ext': {'result': '-9999', 'version': 'v4'}, 'ciLastOverdueMonthCntPos': {'result': '-9999', 'version': 'v2'}, 'ciTsEnqFD': {'result': '591', 'version': 'v4'}, 'ciTsEnqLD': {'result': '591', 'version': 'v4'}, 'ciTsFDpdNumAdj00D': {'result': '599.0', 'version': 'v4'}}, 'cuid': 27041120}; Timestamp of request: 2022-03-22 06:42:18.826000

Re: Is there a better way to search than appendcols ?

chsuresh09 — Tue, 22 Mar 2022 10:00:54 GMT

It looks like the grep is returning only the first occurrence.

Re: Is there a better way to search than appendcols ?

gcusello — Tue, 22 Mar 2022 10:42:18 GMT

Hi @chsuresh09,

this is because you have "results" (word used in the regex) only one time, if you want also the other values, you have to use a different regex, but you have also to manage multivalues.

try this regex

(?ms)\'(?<feature>[^\']+)\':\s+\{\'result\':\s+\'(?<result>[^\']+)

that you can test at https://regex101.com/r/q2bKfP/2

Ciao.

Giuseppe