Best way to report specific multiple values from an event into a table.

[DarthHerm]

I'm continuing to work on dashboards to report on user activity on our application. Going through the knowledgebase, bootcamp slides, and google, trying to determine the best route to report on the values in logs files such as this one. 

The dashboards I am creating is showing activity in the various modules, what values are getting select and what is being pulled up. I looked at spath and mvexpand and wasn't getting the results I was hoping for, might have been I wasn't formatting the search correctly and also how green myself and work is to Splunk. Creating field extractions has worked for the most part to pull the specific values I wanted to report but further on, I'm finding incorrect values being pulled in. Below is one such event that's been sanitized and it's in valid JSON format. 

I'm trying to do a table event showing the userName, date and time, serverHost, SparklingTypeId, PageSize, and PageNumber. The other values not so much. 

Is spath and MV expand along with eval statements the best course? I was using field extractions in a couple other modules but then found incorrect values were being added. 

{"auditResultSets":null,"schema":"com","storedProcedureName":"SpongeGetBySearchCriteria","commandText":"com.SpongeGetBySearchCriteria","Locking":null,"commandType":4,"parameters":[{"name":"@RETURN_VALUE","value":0},{"name":"@SpongeTypeId","value":null},{"name":"@CustomerNameStartWith","value":null},{"name":"@IsAssigned","value":null},{"name":"@IsAssignedToIdIsNULL","value":false},{"name":"@SpongeStatusIdsCSV","value":",1,"},{"name":"@RequestingValueId","value":null},{"name":"@RequestingStaffId","value":null},{"name":"@IsParamOther","value":false},{"name":"@AssignedToId","value":null},{"name":"@MALLLocationId","value":8279},{"name":"@AssignedDateFrom","value":null},{"name":"@AssignedDateTo","value":null},{"name":"@RequestDateFrom","value":null},{"name":"@RequestDateTo","value":null},{"name":"@DueDateFrom","value":null},{"name":"@DueDateTo","value":null},{"name":"@ExcludeCustomerFlagTypeIdsCSV","value":",1,"},{"name":"@PageSize","value":25},{"name":"@PageNumber","value":1},{"name":"@SortColumnName","value":"RequestDate"},{"name":"@SortDirection","value":"DESC"},{"name":"@HasAnySparkling","value":null},{"name":"@SparklingTypeId","value":null},{"name":"@SparklingSubTypeId","value":null},{"name":"@SparklingStatusId","value":null},{"name":"@SparklingDateFrom","value":null},{"name":"@SparklingDateTo","value":null},{"name":"@SupervisorId","value":null},{"name":"@Debug","value":null}],"serverIPAddress":"255.255.000.000","serverHost":"WEBSERVER","clientIPAddress":"255.255.255.255","sourceSystem":"WebSite","module":"Vendor.Product.BLL.Community","accessDate":"2025-04-30T15:34:33.3568918-06:00","userId":3231,"userName":"PeterVenkman","traceInformation":[{"type":"Page","class":"Vendor.Product.Web.UI.Website.Community.Operations.SpongeSearch","method":"Page_Load"},{"type":"Manager","class":"Vendor.Product.BLL.Community.SpongeManager","method":"SpongeSearch"}]}

[livehybrid · Accepted solution]

Hi @DarthHerm 

Is this what you are looking for? 

From your original search you should be able to do:

| eval params=json_array_to_mv(json_extract(_raw,"parameters")) 
| eval newParams="{}" 
| foreach mode=multivalue params 
    [| eval newParams=json_set(newParams,json_extract(<<ITEM>>,"name"),json_extract(<<ITEM>>,"value"))] 
| spath input=newParams 
| table accessDate, userName, serverHost, @SparklingTypeId, @PageSize, @PageNumber

Below is a full example to get you started:

| windbag 
| head 1 
| eval _raw="{\"auditResultSets\":null,\"schema\":\"com\",\"storedProcedureName\":\"SpongeGetBySearchCriteria\",\"commandText\":\"com.SpongeGetBySearchCriteria\",\"Locking\":null,\"commandType\":4,\"parameters\":[{\"name\":\"@RETURN_VALUE\",\"value\":0},{\"name\":\"@SpongeTypeId\",\"value\":null},{\"name\":\"@CustomerNameStartWith\",\"value\":null},{\"name\":\"@IsAssigned\",\"value\":null},{\"name\":\"@IsAssignedToIdIsNULL\",\"value\":false},{\"name\":\"@SpongeStatusIdsCSV\",\"value\":\",1,\"},{\"name\":\"@RequestingValueId\",\"value\":null},{\"name\":\"@RequestingStaffId\",\"value\":null},{\"name\":\"@IsParamOther\",\"value\":false},{\"name\":\"@AssignedToId\",\"value\":null},{\"name\":\"@MALLLocationId\",\"value\":8279},{\"name\":\"@AssignedDateFrom\",\"value\":null},{\"name\":\"@AssignedDateTo\",\"value\":null},{\"name\":\"@RequestDateFrom\",\"value\":null},{\"name\":\"@RequestDateTo\",\"value\":null},{\"name\":\"@DueDateFrom\",\"value\":null},{\"name\":\"@DueDateTo\",\"value\":null},{\"name\":\"@ExcludeCustomerFlagTypeIdsCSV\",\"value\":\",1,\"},{\"name\":\"@PageSize\",\"value\":25},{\"name\":\"@PageNumber\",\"value\":1},{\"name\":\"@SortColumnName\",\"value\":\"RequestDate\"},{\"name\":\"@SortDirection\",\"value\":\"DESC\"},{\"name\":\"@HasAnySparkling\",\"value\":null},{\"name\":\"@SparklingTypeId\",\"value\":null},{\"name\":\"@SparklingSubTypeId\",\"value\":null},{\"name\":\"@SparklingStatusId\",\"value\":null},{\"name\":\"@SparklingDateFrom\",\"value\":null},{\"name\":\"@SparklingDateTo\",\"value\":null},{\"name\":\"@SupervisorId\",\"value\":null},{\"name\":\"@Debug\",\"value\":null}],\"serverIPAddress\":\"255.255.000.000\",\"serverHost\":\"WEBSERVER\",\"clientIPAddress\":\"255.255.255.255\",\"sourceSystem\":\"WebSite\",\"module\":\"Vendor.Product.BLL.Community\",\"accessDate\":\"2025-04-30T15:34:33.3568918-06:00\",\"userId\":3231,\"userName\":\"PeterVenkman\",\"traceInformation\":[{\"type\":\"Page\",\"class\":\"Vendor.Product.Web.UI.Website.Community.Operations.SpongeSearch\",\"method\":\"Page_Load\"},{\"type\":\"Manager\",\"class\":\"Vendor.Product.BLL.Community.SpongeManager\",\"method\":\"SpongeSearch\"}]}" 
| fields _raw 
| spath 
| eval params=json_array_to_mv(json_extract(_raw,"parameters")) 
| eval newParams="{}" 
| foreach mode=multivalue params 
    [| eval newParams=json_set(newParams,json_extract(<<ITEM>>,"name"),json_extract(<<ITEM>>,"value"))] 
| spath input=newParams 
| table accessDate, userName, serverHost, @SparklingTypeId, @PageSize, @PageNumber

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[PickleRick]

Ugh. Unfortunately you have your data in this highly inconvenient form of fielname=something,fieldvalue=something fromwhich you should deduce something=something. Yes, you can do spath and actually foreach might prove to be better than mvexpand but it won't be pretty.

The main problem with this data format is that in order to do anything reasonable with it (including initial filtering) is to process it and transform to something completely different. If your dataset size isn't that big and you're not gonna filter the events anyway, you can get by with it. But if you wanted to select just one user... you'd still need to dig through all your events. That's not a very efficient way to do so.

So while I usually say that as a rule of thumb do not fiddle with raw regexes over structured data in this case if you are absolutely sure that the format is always like this ( {a:b,c:d} -> b=d ) you could hazard doing a regex-based extraction as long as you're aware of the risks.

Alternatively you could use summary indexing to just once per event transform it to the desired format with properly rendered key=value pairs and then search from the summary index.