How to write a search to extract String and Total fields to include in my table of results?

[lksridhar]

Hello Everyone,

I am new to Splunk and trying to write the search below to display the below data in my dashboard, but I'm not getting the expected result.

Search:

index=ABC source=*.log | rex field=_raw ".Total +(?[0-9]+)" | dedup _raw | table String _time Total

I'm getting the string and _time data in my dashboard, but I'm not getting Total value because the total is not extracted as a default field and getting below format.

_time    String    Total
aaaa     bbbb 
aaaa     bbbb

My sample data here.

Nov 30, 2016 09:31:04 AM CST ABCD (BaseABCD.java:139)
- <==      Total: 351

Could you any one help me to write the search to display the total value in my dashboard?

Thanks.

[niketn · Accepted solution]

Your rex command should look like the following:

rex field=_raw "Total:\s(?<Total>.\d+)"

Running a dedup on _raw is not a good idea. I am not sure whether it will change anything in the search result except making the search run slower.

If you are new to regular expressions you can alternatively try erex command (read on Splunk documentation).

Also, once you have validated that rex command is working as expected then, create a field extraction using "Extract New Fields" option. Splunk will interactively create field extraction regular expressions for you based on selected value in the list of events. It will show how many events matched and unmatched the regular expression. It will also allow your to provide your own regular expression where you can override with the regular expression used in rex command.

Reason why Interactive Field Extraction is better is because this is saved as a Knowledge Object and hence the extracted field is automatically available at search time when you specify the index/sourcetype. ALso you dont need to rewrite the rex command for every search/report/alert/dashboard and maintenance/modifications will remain at a single place (Splunk > Settings > Knowledge > Fields > Field extractions)

In order to test your regular expressions on sample events you can use regex101 website.

[niketn]

Following will extract Both String and Total using rex. However, do try out Interactive Field Extraction to ensure that your regular expression is matching all required events.

rex field=_raw "^\w{3}\s\d{2}\,\s\d{4}\s\d{2}\:\d{2}\:\d{2}\s(A|P)M\sCST\s(?<String>.*) \- \<\=\= Total:\s(?<Total>\d+)"

Further looking at your sample data and search query. Try to add sourcetype in base search instead or source. If you are using source, I hope you have wildcard included.

Your base search also filter out only required contents like "CST DEBUG" and "- <== Total:" should be present in your base search.

I am not sure what does the following do in your base search SearchController OR Transaction, since it is not a field I was expecting them to be a part of the logs.

[lksridhar]

yes, search controller OR transaction is part of the log file. let me explain the customer requirement. customer want to display below table format in dashboard.

-time Result_Count total_search_time
12/6/16 10:02:04.000 AM 60 34.267

When clicking on this row the following information should display in the next screen

Sample log file details.

Dec 06, 2016 10:02:04 AM CST INFO (TransactionSearchController.java:48) - String=[Entered Search Transaction(s)]
Dec 06, 2016 10:02:05 AM CST INFO (TransactionSearchDelegateImpl.java:45) - String=[Initiating Transaction search for Query], String=[test567]
Dec 06, 2016 10:02:05 AM CST INFO (TransactionSearchDelegateImpl.java:48) - transaction search without timeout
Dec 06, 2016 10:02:05 AM CST DEBUG (BaseJdbcLogger.java:139) - ==>  Preparing: SELECT * from (SELECT DISTINCT HDR.TRAN_ID transactionId, SC.status_desc TRANSACTION_STATUS, HDR.RPT_DATE , decode (ECG_PROC.Is_Date_Calendar(HDR.SHIP_DATE), 'Y', to_char(to_date(HDR.SHIP_DATE, 'yyyymmdd'), 'mm/dd/yyyy'), HDR.SHIP_DATE) shipDate, ACCT_DS.NAME_1 reporterName, PARTY_SF.ACCT_ID shipFromAcctId, ACCT_SF.NAME_1 shipFromName, PARTY_ST.ACCT_ID shipToAcctId, ACCT_ST.NAME_1 shipToName, HDR.RPTD_DOC_NBR invoiceNumber, HDR.NET_INV_AMOUNT netInvoice, HDR.ROW_ENTRY_DATE, PRD.legal_descr_1 productDescription, DTL.RPTD_QTY quantity, DTL.SSU_QTY, DTL.RPTD_UOM uom FROM ECG_TRAN_HEADER HDR, ECG_TRAN_HEADER_STATUS HS, ECG_STATUS_CODE SC, ECG_TRAN_DETAIL DTL, ECG_TRAN_PARTY PARTY_DS, ECG_TRAN_PARTY PARTY_ST, ECG_TRAN_PARTY PARTY_SF, ECG_TRACKING_SOURCE ETS, IRD.PROD PRD, IRD.ACCT ACCT_DS, S_ACCT_EXT_DNML ACCT_ST, S_ACCT_EXT_DNML ACCT_SF, ECG.TRANSACTION_SECURITY TS ,ECG_TRAN_ERR TE ,(SELECT ACCT_ID, EMP_ID, EMP_TO_ACCT_TYPE_CODE FROM AGSHARE.EMP_TO_ACCT_MV WHERE EMP_TO_ACCT_TYPE_CODE = ? ) EMP_ACCT_GPOS_DS , AGSHARE.EMP_MV EMP_GPOS_DS WHERE HDR.TRAN_ID = PARTY_DS.TRAN_ID AND HDR.TRAN_ID = HS.TRAN_ID AND HS.STATUS_ID = SC.STATUS_ID AND PARTY_DS.PARTY_CODE = 'DS' AND HDR.TRAN_ID = PARTY_ST.TRAN_ID AND PARTY_ST.PARTY_CODE = 'ST' AND HDR.TRAN_ID = PARTY_SF.TRAN_ID AND PARTY_SF.PARTY_CODE = 'SF' AND PARTY_DS.ACCT_ID = ACCT_DS.ACCT_ID(+) AND PARTY_ST.ACCT_ID = ACCT_ST.ACCT_ID(+) AND PARTY_SF.ACCT_ID = ACCT_SF.ACCT_ID(+) AND HDR.TRAN_ID = DTL.TRAN_ID(+) AND HDR.TRAN_ID = TS.TRAN_ID(+) AND HDR.TRAN_ID = HS.TRAN_ID AND HDR.TRACKING_ID = ETS.TRACKING_ID AND HDR.TRAN_ID = TE.TRAN_ID(+) AND NOT EXISTS (SELECT 1 FROM ECG.REFERRAL REF, ECG.REFERRAL_TRAN REF_TRAN WHERE HDR.TRAN_ID = REF_TRAN.TRAN_ID AND REF_TRAN.REFERRAL_ID = REF.REFERRAL_ID AND REF.REFERRAL_STATUS = 'OPEN') AND DTL.PROD_ID = PRD.PROD_ID(+) AND HDR.SHIP_DATE >= to_char(to_date(?, 'MM/DD/YYYY'), 'YYYYMMDD') AND HDR.SHIP_DATE <= to_char(to_date(?, 'MM/DD/YYYY'), 'YYYYMMDD') AND HS.STATUS_ID in ( ? ) and TE.open_err_flag = 'Y' AND PARTY_DS.ACCT_ID = EMP_ACCT_GPOS_DS.ACCT_ID(+) AND EMP_ACCT_GPOS_DS.EMP_ID = EMP_GPOS_DS.EMP_ID(+) AND EMP_GPOS_DS.USER_ID in ( ? , ? ) ) WHERE rownum <= ? order by transactionId 
Dec 06, 2016 10:02:05 AM CST DEBUG (BaseJdbcLogger.java:139) - ==> Parameters: GPOS(String), 09/01/2015(String), 08/31/2016(String), 2(String), CTBECK1(String), LBOCH(String), 30000(Long)
Dec 06, 2016 10:02:39 AM CST DEBUG (BaseJdbcLogger.java:139) - <==      Total: 60
Dec 06, 2016 10:02:39 AM CST INFO (TransactionSearchDelegateImpl.java:54) - String=[Transaction Search Results. Transactions Count Is], String=[test567], Integer=[60]
Dec 06, 2016 10:02:39 AM CST INFO (TransactionSearchDelegateImpl.java:55) - Transaction Search Query [test567] Time (in seconds) = 34.267

Could you please help me to write the query for this and let me know if need more information on this.

[lksridhar]

Hi Niketnilay,

Thanks for quick response. I have tried with the new query, but it is not showing total value on the dashboard. I have used the below query. Could you pls check and correct the search?

index=ABC source="abcd/efgh/ijklm" SearchController OR Transaction | rex field=_raw "Total:\s(?.\d+)" | Table String, _time, Total.

[niketn]

I had checked my previous regular expression on Regex101. Not sure what is not working. Maybe some special character in the actual data that you have is not showing up properly in Splunk Answers post here. So try a simpler extract. Basically, I am trying to get everything between CST and Total as String.

index=ABC source="abcd/efgh/ijklm" SearchController OR Transaction | rex field=_raw "\sCST\s(?<String>\.*)\sTotal:\s(?.\d+)" | Table String, _time, Total

Further if you are not experienced with Regular Expressions try erex and definitely do this through Interactive Field Extraction (Splunk will write the regular expression for you).

[niketn]

@lksridhar Please let me know if this solves your problem or not.

[niketn]

Can you whether your base search is returning results or not?

index=ABC source="abcd/efgh/ijklm" SearchController OR Transaction  

I had checked the your sample data on regex101

Nov 30, 2016 09:31:04 AM CST ABCD (BaseABCD.java:139)
- <== Total: 351

and following regular expression was working fine

Total:\s(?<Total>.\d+) 

Could you also please confirm whether there is actually a newline character before - <== Total: 351 or not? If possible add sample data as is from your logs.

[lksridhar]

Thanks of your information, now i can able to see the data under the total value but i am not getting any data for under the string filed.

see the sample log files details.

Nov 30, 2016 07:34:25 AM CST INFO (TransactionController.java:159) - String=[Entered Get Transaction Authorization For Transactrion Id], Long=[111111]
Nov 30, 2016 09:31:04 AM CST DEBUG (BaseJdbcLogger.java:139) - <==      Total: 351

search:

 index=ABC source="abcd/efgh/ijklm" SearchController OR Transaction  | | rex field=_raw "Total:\s(?.\d+)" | Table String, _time, Total

Output:

-time Total String
222 1233 No Data
222 1234
223 4567

Could you please help me on this to get the string details in the result?

[niketn]

Do you want the following to be string?

DEBUG (BaseJdbcLogger.java:139)