Splunk Search

How to extract the last string order a table around it ?

zacksoft
Contributor

40.118.209.1 0x735870x1 GG46989 [21/Dec/2014:00:00:00 -0500] "GET /rest/jphutenxporter/1.0/outputformatconfig/outputformatselected?_=1513833400783 HTTP/1.1" 200 49 2 "https://phuten.mayhem.com/browse/UOAI-1536" "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; WOW64; Trident/7.0; SLCC2; .NET CLR 3.0.50727; .NET CLR 3.6.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; .NET4.0E; InfoPath.3)" "38b0i3"

Hi,
I have some events of various length (like the one above). But in all events the last string (in this case 37b0i3) is always a session_id, is what I'm told. I would like to extract it and use that value in my table.
And the table should be ordered by session_id.
means something like table _time uri etc etc order by session_id.. <-- I am not sure if this is a real command.

Tags (1)
0 Karma
1 Solution

elliotproebstel
Champion

You can extract that session_id at search time using the rex command like this:

your base search 
| rex field=_raw "\"(?<session_id>[^\s]+)\"$"

If that session_id string happens to fall within another previously-extracted field, then you can replace _raw in that command with the name of the field.

And table is a valid Splunk command, but there is no table by. If you just want to list the values of all extracted fields organized by session_id, this could work for you:

your base search 
| rex field=_raw "\"(?<session_id>[^\s]+)\"$"
| stats values(*) AS * BY session_id

Or, instead of stats values(), you might prefer to use the chart command, which is documented here:
https://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Chart

View solution in original post

0 Karma

elliotproebstel
Champion

You can extract that session_id at search time using the rex command like this:

your base search 
| rex field=_raw "\"(?<session_id>[^\s]+)\"$"

If that session_id string happens to fall within another previously-extracted field, then you can replace _raw in that command with the name of the field.

And table is a valid Splunk command, but there is no table by. If you just want to list the values of all extracted fields organized by session_id, this could work for you:

your base search 
| rex field=_raw "\"(?<session_id>[^\s]+)\"$"
| stats values(*) AS * BY session_id

Or, instead of stats values(), you might prefer to use the chart command, which is documented here:
https://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Chart

0 Karma

zacksoft
Contributor

@elliotproebstel
Thank you.
This is exactly what I was looking for.
If I may ask one more thing.
This stats values(*) gives me all the values which I want , that's good but it arranges the fields alphabetically. The field like 'user' is coming in the last column. Is it possible to rearrange the fields in the stats command ?
I tried something like stats(username, ip, session, etc) to rearrange the fields in display my way, but it didn't work !

0 Karma

elliotproebstel
Champion

Absolutely! The most direct way to do it is like this:

your base search 
| rex field=_raw "\"(?<session_id>[^\s]+)\"$"
| stats values(firstfield) AS firstfield 
    values(secondfield) AS secondfield 
    values(thirdfield) AS thirdfield... 
    BY session_id

This will require you to make an explicit list of the fields you want to see and then order your values(x) AS x commands in the order in which you'd like them listed. Note that the session_id field (since you use it in the BY clause) will necessarily be the first column, and then each field you pass into stats values() will follow, in the order in which you supply them.

0 Karma

zacksoft
Contributor

Thank you @elliotproebstel

0 Karma

rtruman_splunk
Splunk Employee
Splunk Employee

Extracting the field

Select an event of the type shown above and expand it, then select extract fields from the event actions dropdown menu. Pick regular expression and highlight the intended session_id field, calling it session_id. from there you can validate and confirm this field.

Ordering a table

You can pipe all the events into a table command and then a sort command on session_id,
such as events| table _time, uri, ...., session_id |sort session_id

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...