Splunk Search

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

chq_alanf
Explorer

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.

1 Solution

chanfoli
Builder

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

View solution in original post

chanfoli
Builder

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

0 Karma

ppablo
Retired

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
Builder

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

0 Karma

chanfoli
Builder

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

metylkinandrey
Communicator

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"

0 Karma

chq_alanf
Explorer

That worked.. Thank you very much!

0 Karma

chq_alanf
Explorer

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
0 Karma

chanfoli
Builder

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.

0 Karma

chanfoli
Builder
* | 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
Get Updates on the Splunk Community!

Fastest way to demo Observability

I’ve been having a lot of fun learning about Kubernetes and Observability. I set myself an interesting ...

September Community Champions: A Shoutout to Our Contributors!

As we close the books on another fantastic month, we want to take a moment to celebrate the people who are the ...

Splunk Decoded: Service Maps vs Service Analyzer Tree View vs Flow Maps

It’s Monday morning, and your phone is buzzing with alert escalations – your customer-facing portal is running ...