Getting Data In

How to format raw events into splunk table when the events are already in tabular format

ak9092
Path Finder

Hi ,

I have following data coming into splunk in one event and i want these event to be formatted in proper splunk table.

 

200 - OK 

---------------------------------------------------------------------------------------------

| LM      | GRP    |    SR  | ID     | STATE  | SDL | QUEUE | SK | FAIL |

|-------------------------------------------------------------------------------------------|

| gpp1 | GROUP | hv | 231 | START | --- | HV | 15272 | 1 |

| gpp2 | GROUP | hv | 233 | START | --- | HV | 15226 | 2 |

| gpp2 | GROUP | hv | 234 | START | --- | HV | 11546 | 2 |

| gpp1 | GROUP | hq | 240 | STOP   | --- |        | 0             | 1 |

| gpp2 | GROUP | hq | 242 | START | --- |        | 0             | 1 |

 

So i want to create fields with above table headers like LM,GRP,ID,STATE,etc. and these fields should consist all the values present underneath it

I tried extracting the fields and also used multikv command but no luck.

Labels (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

This will create many rows for each event, but may help you get where you're going - it pre-massages the data before the multikv

| makeresults
| eval _raw="200 - OK 

---------------------------------------------------------------------------------------------

| LM      | GRP    |    SR  | ID     | STATE  | SDL | QUEUE | SK | FAIL |

|-------------------------------------------------------------------------------------------|

| gpp1 | GROUP | hv | 231 | START | --- | HV | 15272 | 1 |

| gpp2 | GROUP | hv | 233 | START | --- | HV | 15226 | 2 |

| gpp2 | GROUP | hv | 234 | START | --- | HV | 11546 | 2 |

| gpp1 | GROUP | hq | 240 | STOP   | --- |        | 0             | 1 |

| gpp2 | GROUP | hq | 242 | START | --- |        | 0             | 1 |"
| eval t=replace(_raw,"\n", ",")
| eval rows=split(t,",")
| eval cr=mvcount(_raw), ct=mvcount(rows)
| fields - t
| eval _raw=rows
| rex field=_raw mode=sed "s/ \|/,/g"
| rex field=_raw mode=sed "s/[\s]* ,/,/g"
| multikv forceheader=3
| table LM GRP SR ID STATE SDL QUEUE SK FAIL

Hope this helps

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

This will create many rows for each event, but may help you get where you're going - it pre-massages the data before the multikv

| makeresults
| eval _raw="200 - OK 

---------------------------------------------------------------------------------------------

| LM      | GRP    |    SR  | ID     | STATE  | SDL | QUEUE | SK | FAIL |

|-------------------------------------------------------------------------------------------|

| gpp1 | GROUP | hv | 231 | START | --- | HV | 15272 | 1 |

| gpp2 | GROUP | hv | 233 | START | --- | HV | 15226 | 2 |

| gpp2 | GROUP | hv | 234 | START | --- | HV | 11546 | 2 |

| gpp1 | GROUP | hq | 240 | STOP   | --- |        | 0             | 1 |

| gpp2 | GROUP | hq | 242 | START | --- |        | 0             | 1 |"
| eval t=replace(_raw,"\n", ",")
| eval rows=split(t,",")
| eval cr=mvcount(_raw), ct=mvcount(rows)
| fields - t
| eval _raw=rows
| rex field=_raw mode=sed "s/ \|/,/g"
| rex field=_raw mode=sed "s/[\s]* ,/,/g"
| multikv forceheader=3
| table LM GRP SR ID STATE SDL QUEUE SK FAIL

Hope this helps

ak9092
Path Finder

Hi @bowesmana , this seems to be doing the trick but i am unable to filter events by any of that extracted fields.

So for Example - i want to get event which are in STOP state, but when i tried executing below query it didn't returned any results

....|table LM GRP SR ID STATE SDL QUEUE SK FAIL | where STATE=STOP 

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

Firstly your where clause needs "STOP" to be quoted, as it's an eval statement, not a search, so it thinks STOP is a fieldname.

However, it still won't work as the STOP has a space prefix. You can replace the second rex statement with

| rex field=_raw mode=sed "s/([\s]* ,|, )/,/g"

i.e. adding a second clause to remove the space at the start of the field name

Should work 

ak9092
Path Finder

@bowesmana perfect ..Thanks!

0 Karma
Get Updates on the Splunk Community!

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...