topic Re: Help me extract this data and create a table. in Splunk Search

Help me extract this data and create a table.

ekucevic — Fri, 19 Nov 2021 19:16:19 GMT

I have a log sample:

| LRU Config Message from RMQ: {"endpoint":"lru/ config", "data":{"timestamp":1637322539.953,"version":"1","aircraftTailId":"N123JB",[{"lruComponent":"Modem","lruHwPartNumber":"123456","lruHwSerialNumber":"C82821190191","lruRevisionNumber":"004","lruMacAddress":true}, {"lruComponent":"Server","lruHwPartNumber":"1244632","lruHwSerialNumber":"F39718480040","lruRevisionNumber":"004","lruMacAddress":null},

What i want to do is extract the date and create a table based on the color i highlighted above.

lruComponent | lruHwPartNumber |

Modem 123456

Re: Help me extract this data and create a table.

richgalloway — Fri, 19 Nov 2021 20:37:05 GMT

See if this sample query helps. It uses rex to extract the field values, mvzip to pair component with part number, then splits them back out for display.

| makeresults | eval _raw="LRU Config Message from RMQ: {\"endpoint\":\"lru/ config\", \"data\":{\"timestamp\":1637322539.953,\"version\":\"1\",\"aircraftTailId\":\"N123JB\",[{\"lruComponent\":\"Modem\",\"lruHwPartNumber\":\"123456\",\"lruHwSerialNumber\":\"C82821190191\",\"lruRevisionNumber\":\"004\",\"lruMacAddress\":true}, {\"lruComponent\":\"Server\",\"lruHwPartNumber\":\"1244632\",\"lruHwSerialNumber\":\"F39718480040\",\"lruRevisionNumber\":\"004\",\"lruMacAddress\":null}," ```The above is just for testing``` | rex max_match=0 "lruComponent\\\":\\\"(?<lruComponent>[^\\\"]+)\\\",\\\"lruHwPartNumber\\\":\\\"(?<lruHwPartNumber>[^\\\"]+)" | eval compNum=mvzip(lruComponent,lruHwPartNumber,",") | mvexpand compNum | eval compNum=split(compNum,",") | eval lruComponent=mvindex(compNum,0), lruHwPartNumber=mvindex(compNum,1) | table lruComponent lruHwPartNumber

Re: Help me extract this data and create a table.

ekucevic — Sun, 21 Nov 2021 15:14:02 GMT

This is exactly what i was looking for. Really appreciate it.

One more thing, i want to add in 3rd column, "lruHwSerialNumber" and also these "lruHwSerialNumber"s change over time. When i add in the serial number column will "Dedup _lruHwSerialNumber" work?

Re: Help me extract this data and create a table.

richgalloway — Sun, 21 Nov 2021 19:08:11 GMT

You can add a 3rd column, just be aware the mvzip function only accepts two arguments. You can, however, nest mvzip calls. See https://docs.splunk.com/Documentation/Splunk/8.2.3/SearchReference/MultivalueEvalFunctions#Extended_example_3 for an example of that.

Dedup should work, but I'd have to know more about how you plan to use to say for sure.

Re: Help me extract this data and create a table.

ekucevic — Mon, 22 Nov 2021 14:36:14 GMT

Lets say i dont want to add in a 3rd field, I find that when i just change one of the components of your solution from "lruHwPartNumber" to "lruHwSerialNumber" it doesnt give me any results.

Re: Help me extract this data and create a table.

richgalloway — Mon, 22 Nov 2021 14:57:37 GMT

Substituting random fields won't work if the new field is not extracted. Try this query.

| makeresults | eval _raw="LRU Config Message from RMQ: {\"endpoint\":\"lru/ config\", \"data\":{\"timestamp\":1637322539.953,\"version\":\"1\",\"aircraftTailId\":\"N123JB\",[{\"lruComponent\":\"Modem\",\"lruHwPartNumber\":\"123456\",\"lruHwSerialNumber\":\"C82821190191\",\"lruRevisionNumber\":\"004\",\"lruMacAddress\":true}, {\"lruComponent\":\"Server\",\"lruHwPartNumber\":\"1244632\",\"lruHwSerialNumber\":\"F39718480040\",\"lruRevisionNumber\":\"004\",\"lruMacAddress\":null}," ```The above is just for testing``` | rex max_match=0 "lruComponent\\\":\\\"(?<lruComponent>[^\\\"]+)\\\",\\\"lruHwPartNumber\\\":\\\"(?<lruHwPartNumber>[^\\\"]+)\\\",\\\"lruHwSerialNumber\\\":\\\"(?<lruHwSerialNumber>[^\\\"]+)" | eval compNum=mvzip(lruComponent,lruHwSerialNumber,",") | mvexpand compNum | eval compNum=split(compNum,",") | eval lruComponent=mvindex(compNum,0), lruHwSerialNumber=mvindex(compNum,1) | table lruComponent lruHwSerialNumber

Re: Help me extract this data and create a table.

ekucevic — Tue, 23 Nov 2021 20:05:35 GMT

I am going to accept this solution. Really appreciate your help.

One thing i noticed is these are periodic logs and usually the serial numbers can change. I added dedup lruHwSerialNumber thinking it would pull the different serial based on the time frame i know it changed but it doesnt seem to pull it.