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
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
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?
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_... 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.
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.
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
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.