how to use append to list out the values respective to the results we get?

[bollam]

I have got multiple events in a log file which contains few key-value pairs and i want to look at only the existence of three key-value pairs (name and location) in all the events.

Name="xyz", age="aa" and location="zzz"

I want to find the percentage of events where "age" does not exist by location and all the "Name" by location

I have following query which gives me the result but not what im expecting.

Key "Name" is being in under location where the event "age" does not exist. The column should be blank under location if "age" does not exist.

Also I need recent five "Name" in which age does not exist.

index=main
| stats count as Total, count(eval(NOT isnull(age))) as MATCHED count(eval(isnull(age))) as UNMATCHED by location
| eval pctMatched = round(MATCHED/Total*100, 1)
| eval pctUnmatched = round(UNMATCHED/Total*100, 1)
| eval PercentExists = pctMatched + "%"
| eval PercentDoesNotExist = pctUnmatched + "%"
| table cluster percentTagged percentUntagged
| appendcols [ search index=main
| stats count by location, Name
| sort -count
| streamstats count as rank by location | where rank <= 5
| stats list(Name) as Name, list(count) as count by location
| fields - location, count
| table Name ]

[woodcock]

Try this:

index=main
| stats count AS Total, count(age) AS MATCHED BY Name location
| eval UNMATCHED = Total - MATCHED
| multireport

[ stats sum(*) AS * BY location
| eval PercentExists = round(100 * MATCHED / Total, 1) + "%"
| eval PercentDoesNotExist = round(100 * UNMATCHED / Total, 1) + "%" ]

[ eval PercentExists = round(100 * MATCHED / Total, 1)
| sort 0 - pctMatched
| dedup 5 location
| eval Top_5_Unmatched_Names = "Name=" . Name . ", Total=" . Total . ", PercentExists=" . PercentExists . "%"
| table Top_5_Unmatched_Names location ]

| stats list(*) AS * BY location

[woodcock]

I reedited/resubmitted this about 20 times so if it didn't work, make sure you refresh and try the updated answer.