topic Re: How to use substr to extract the first 3 letters of a field and use it as a grouping field? in Splunk Search

How to use substr to extract the first 3 letters of a field and use it as a grouping field?

chq_alanf — Wed, 28 Jan 2015 16:51:12 GMT

I'm not sure I asked the right question, but I'd like to use substr to extract the first 3 letters of a field and use it as a grouping field. My query is as follows:

 * | stats sum(bytes_in) as MB by user_id as substr(user_id,1,3) | eval MB=round(MB/1024/1024,2) | sort -MB head 20

The syntax validates, however, no results are returned. Taking the substr out returns results, but of every user, not grouped by branch code.

Any help would be greatly appreciated.

Re: How to use substr to extract the first 3 letters of a field and use it as a grouping field?

chanfoli — Wed, 28 Jan 2015 17:02:02 GMT

I would use | eval branch_code=substr(user_id,1,3) | to transform user_id to the 3 letter value before stats

Re: How to use substr to extract the first 3 letters of a field and use it as a grouping field?

ppablo — Wed, 28 Jan 2015 17:02:17 GMT

Hi @chq_alanf

I'm not a search expert, but from looking at Splunk documentation, it looks like "substr" is a function of eval and where, not stats.
http://docs.splunk.com/Documentation/Splunk/6.2.1/SearchReference/CommonEvalFunctions

Re: How to use substr to extract the first 3 letters of a field and use it as a grouping field?

chq_alanf — Mon, 28 Sep 2020 18:47:39 GMT

Thank You. I've tried that and many other combinations but still no results.

| stats eval(round(sum(bytes_in)/1024/1024, 0)) as MB, user_id as BRANCH by MB | eval BRANCH=substr(user_id,1,3) | sort -MB head 20

Re: How to use substr to extract the first 3 letters of a field and use it as a grouping field?

chanfoli — Wed, 28 Jan 2015 18:28:37 GMT

The problem with the above is that your stats gives you BRANCH and MB, then you try to transform a non existent field (user_id). Stats only gives you the fields that you ask for stats on.

Re: How to use substr to extract the first 3 letters of a field and use it as a grouping field?

chanfoli — Wed, 28 Jan 2015 18:40:22 GMT

Yeah I think stats is simply seeing that as a new field name in the above case.

Re: How to use substr to extract the first 3 letters of a field and use it as a grouping field?

chanfoli — Wed, 28 Jan 2015 18:44:07 GMT

* | eval branch_id=substr(user_id,1,3) | stats sum(bytes_in) as MB by branch_id | eval MB=round(MB/1024/1024,2) | sort -MB | head 20

Re: How to use substr to extract the first 3 letters of a field and use it as a grouping field?

chanfoli — Wed, 28 Jan 2015 19:16:30 GMT

Also, see my latest example below which might help. It looks like by leaving out a pipe you told sort to use the non-existent field "head".

Re: How to use substr to extract the first 3 letters of a field and use it as a grouping field?

chq_alanf — Wed, 28 Jan 2015 19:21:56 GMT

That worked.. Thank you very much!

Re: How to use substr to extract the first 3 letters of a field and use it as a grouping field?

metylkinandrey — Thu, 06 Oct 2022 10:44:13 GMT

Tell me, what should I do in my case, I need from the field: 1.SAPS-SIS.TO.LSP.SEND, or: "12.SAPS-SIS.TO.LSP.RECEIVE
Get field: "routepointIDnum": "1" or "routepointIDnum": "12"

I tried like this and it almost works:
index="main" sourcetype="testsystem-script333"
| eval routepointID_num=substr(routepointID,1,2)
| table routepointID_num

Almost because I get:
"routepointIDnum": "1." or "routepointIDnum": "12"
And I need:
"routepointIDnum": "1" or "routepointIDnum": "12"