Splunk help - query latest event based on a field and count value based on another field

[cheriemilk]

Hi Team,

I have below events, want to find out the latest event for each kf7 value, and then stats count based on ktf1.

2019-12-04 18:30:08,094 # kf7=adequatT1_301 # ktf1=[liveProfile];;
2019-12-04 18:38:14,890 # kf7=adequatT1_301 # ktf1=[liveProfile,Others];;
2019-12-04 18:39:14,890 # kf7=adequatT1_301 # ktf1=[liveProfile,compensation,Others];;
2019-12-04 18:40:14,890 # kf7=adequatT1_302 # ktf1=[liveProfile,talentflag];;
2019-12-04 18:45:14,890 # kf7=adequatT1_302 # ktf1=[performance]

so the expected status result should comes from below two events
2019-12-04 18:39:14,890 # kf7=adequatT1_301 # ktf1=[liveProfile,compensation,Others]
2019-12-04 18:45:14,890 # kf7=adequatT1_302 # ktf1=[performance]

The stats result should be below:
ktf1, count
liveProfile, 1
compensation, 1
Others, 1
performance, 1

I have draft the query like below, but can't figure out how to stats by ktf1 as next step. Could you please help?

| makeresults
| eval log=" 2019-12-04 18:30:08,094 # kf7=adequatT1_301 # ktf1=[liveProfile];;
2019-12-04 18:38:14,890 # kf7=adequatT1_301 # ktf1=[liveProfile,Others];;
2019-12-04 18:39:14,890 # kf7=adequatT1_301 # ktf1=[liveProfile,compensation,Others];;
2019-12-04 18:40:14,890 # kf7=adequatT1_302 # ktf1=[liveProfile,talentflag];;
2019-12-04 18:45:14,890 # kf7=adequatT1_302 # ktf1=[performance] "
| makemv delim=";;" log
| mvexpand log| rex field=log "(?.?)#.*kf7=(?.?)\ #.ktf1=[(?.?)]" | makemv delim="," ktf1
| eval ktf1= ltrim(ktf1)
| stats max(time) by kf7

[aberkow]

Hey - so when I ran your search I hit an error with the regex so I wrote my own, and also your two 301 kf7 values have the same timestamp so you should probably figure out how you want to handle that case. That being said, something like this does get your expected output:

{code}
| makeresults
| eval log=" 2019-12-04 18:30:08,094 # kf7=adequatT1_301 # ktf1=[liveProfile];;
2019-12-04 18:38:14,890 # kf7=adequatT1_301 # ktf1=[liveProfile,Others];;
2019-12-04 18:39:14,890 # kf7=adequatT1_301 # ktf1=[liveProfile,compensation,Others];;
2019-12-04 18:40:14,890 # kf7=adequatT1_302 # ktf1=[liveProfile,talentflag];;
2019-12-04 18:45:14,890 # kf7=adequatT1_302 # ktf1=[performance] "
| makemv delim=";;" log
| mvexpand log
| rex field=log "(?.?)\s#\skf7=(?.)\s#\sktf1=[(?.*)]"
| eval _time=strptime(timestamp, "%Y-%m-%d %H:%M:%S")
| stats latest(ktf1value) as ktf1value by ktf7value
| eval ktf1value=split(ktf1value, ",")
| mvexpand ktf1value
| stats count by ktf1value
{code}

I parse out the timestamp, ktf7value, and ktf1value, convert the timestamp back into the _time field as you need that for any time calls (latest/timechart/etc), take the latest ktf1value by ktf7, split out the fields to make them into a multivalue field to expand them to count by them. https://regex101.com/ is a good site for testing regex for what it's worth.

Hope this helps!