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
Champion

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
Champion

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!

Mastering Data Pipelines: Unlocking Value with Splunk

 In today's AI-driven world, organizations must balance the challenges of managing the explosion of data with ...

The Latest Cisco Integrations With Splunk Platform!

Join us for an exciting tech talk where we’ll explore the latest integrations in Cisco + Splunk! We’ve ...

AI Adoption Hub Launch | Curated Resources to Get Started with AI in Splunk

Hey Splunk Practitioners and AI Enthusiasts! It’s no secret (or surprise) that AI is at the forefront of ...