topic Re: How to format raw events into splunk table when the events are already in tabular format in Getting Data In

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

ak9092 — Tue, 07 Jul 2020 06:02:46 GMT

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.

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

bowesmana — Tue, 07 Jul 2020 07:51:01 GMT

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

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

ak9092 — Thu, 09 Jul 2020 06:09:59 GMT

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

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

bowesmana — Thu, 09 Jul 2020 21:50:12 GMT

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

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

ak9092 — Fri, 10 Jul 2020 12:06:19 GMT

@bowesmana perfect ..Thanks!