Splunk Search

Is there a better way to search than appendcols ?

chsuresh09
Explorer

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

Labels (2)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

View solution in original post

chsuresh09
Explorer

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. 

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @chsuresh09,

try with this regex:

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

Ciao.

Giuseppe

chsuresh09
Explorer

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

0 Karma

chsuresh09
Explorer

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

chsuresh09
Explorer

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.

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

chsuresh09
Explorer

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).

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @chsuresh09,

please try this:

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

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

Ciao.

Giuseppe

chsuresh09
Explorer

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'},

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...