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
Got questions? Get answers!

Join the Splunk Community Slack to learn, troubleshoot, and make connections with fellow Splunk practitioners in real time!

Meet up IRL or virtually!

Join Splunk User Groups to connect and learn in-person by region or remotely by topic or industry.

Get Updates on the Splunk Community!

Enterprise Security Watch Party: What’s New in 8.8 - Recap

Splunk Community HQ hosted a Splunk Enterprise Security Watch Party: What’s New In ES 8.8 last Thursday. I had ...

Move CrowdStrike FDR ingestion to Data Inputs with a guided migration

CrowdStrike Falcon Data Replicator data is essential for security investigations, detections, and response. It ...

Make Your Machine Data AI-Ready in Splunk

Every team is getting ready for AI agents.    Watch On-Demand   In this Tech Talk, Splunk platform specialists ...