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

[chq_alanf]

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.

[chanfoli]

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

[ppablo]

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

[chanfoli]

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

[chanfoli · Accepted solution]

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

[metylkinandrey]

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"

[chq_alanf]

That worked.. Thank you very much!

[chq_alanf]

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

[chanfoli]

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.

[chanfoli]

* | 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