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
Super Champion

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
Super Champion

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

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
Super Champion

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
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!