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

[ak9092]

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.

[bowesmana · Accepted solution]

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]

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 

[bowesmana]

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]

@bowesmana perfect ..Thanks!