Knowledge Management

How to combine an xyseries output with other aggregate function fields and columns for a certain identifier key

pavanml
Explorer

This is an extension to my other question in https://answers.splunk.com/answers/812982/summary-of-stats-from-multiple-events-for-each-ide.html?mi...

The input and output that I need are in the screenshot below:
alt text

I was able to use xyseries with below command to generate output with identifier and all the Solution and Applied columns for each status. However now I want additional 2 columns for each identifier which is:
* StartDateMin - minimum value of StartDate for all events with a specific identifier
* EndDateMax - maximum value of EndDate for all events with a specific identifier

index = |
| stats count by Identifier, TransactionType, Status
| eval TransactionType = TransactionType." (".Status.")"
| xyseries Identifier, TransactionType, count
| fillnull value=0

How do I embed the new columns StartDateMin and EndDateMax with query modification of above query ?
One option I can think of is to separately generate identifier with startdatemin, end datemin and then again identifier with other columns and then perform a join based on identifier but would involve repetition of lot of conditions two times.

Is there an easy way out while using the above query to do the same ?

Labels (1)
Tags (1)
0 Karma
1 Solution

manjunathmeti
SplunkTrust
SplunkTrust

Hi @pavanml,

Try this:

| makeresults 
| eval _raw="Identifier, TransactionType, Status, StartDate, EndDate
  1234, Solution, Success, 2020-03-14, 2020-03-15
  1234, Solution, Success, 2020-03-16, 2020-03-17
  1234, Applied, Fail, 2020-03-17, 2020-030-18
  4567, Solution, Fail, 2020-03-14, 2020-03-15
  4567, Solution, Excepetion, 2020-03-18, 2020-03-19" 
| multikv forceheader=1 
| eventstats count, min(StartDate) as StartDateMin, max(EndDate) as EndDateMax by Identifier 
| stats count, min(StartDateMin) as StartDateMin, max(EndDateMax) as EndDateMax by Identifier, TransactionType, Status 
| eval TransactionType = TransactionType." (".Status.")", Identifier=mvzip(Identifier, mvzip(StartDateMin, EndDateMax, ","), ",") 
| xyseries Identifier, TransactionType, count 
| fillnull value=0 
| eval split=split(Identifier, ", "), Identifier=mvindex(split, 0), StartDateMin=mvindex(split, 1), EndDateMax=mvindex(split, 2)

View solution in original post

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

Hi @pavanml,

Try this:

| makeresults 
| eval _raw="Identifier, TransactionType, Status, StartDate, EndDate
  1234, Solution, Success, 2020-03-14, 2020-03-15
  1234, Solution, Success, 2020-03-16, 2020-03-17
  1234, Applied, Fail, 2020-03-17, 2020-030-18
  4567, Solution, Fail, 2020-03-14, 2020-03-15
  4567, Solution, Excepetion, 2020-03-18, 2020-03-19" 
| multikv forceheader=1 
| eventstats count, min(StartDate) as StartDateMin, max(EndDate) as EndDateMax by Identifier 
| stats count, min(StartDateMin) as StartDateMin, max(EndDateMax) as EndDateMax by Identifier, TransactionType, Status 
| eval TransactionType = TransactionType." (".Status.")", Identifier=mvzip(Identifier, mvzip(StartDateMin, EndDateMax, ","), ",") 
| xyseries Identifier, TransactionType, count 
| fillnull value=0 
| eval split=split(Identifier, ", "), Identifier=mvindex(split, 0), StartDateMin=mvindex(split, 1), EndDateMax=mvindex(split, 2)
0 Karma

pavanml
Explorer

That worked perfectly !! Thanks a lot.

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...