Splunk Search

How to split time into column and other fields into row?

kalaiyarasi
Loves-to-Learn Lots

|eval TotalApps=if(match('Total',"NTB"),"1","0")

|eval In-Progress=if('Total'="NTB" AND isnull('APPL_SUB-DATE'),"1","0")

|eval Submitted=if('Total'="NTB" AND isnotnull('APPL_SUB-DATE'),"1","0")

|eval My-InfoUsed=if('Total'="NTB" AND isnotnull('APPL_SUB-DATE') AND isnotnull('MY-INF0-CONCUR-FLAG'),"1","0")
|stats sum(TotalApps) as "Total Apps" sum(In-Progress) as "In Progress" sum(Submitted) as "Apps Submitted" sum(My-InfoUsed) as "My InfoUsed" by Mon-Year
|transpose Column_name="Category"

getting results as
Category        row1

Mon-Year                Jan-2023

Total Apps                06

In Progress              06

Apps Submitted      0

My InfoUsed              0

But requirement is ,

Mon-Year        Category               Total

Jan-2023         TotalApps              06

                              In Progress            06

                              Apps Submitted    0

                              My InfoUsed             0

Labels (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
|stats sum(TotalApps) as "Total Apps" sum(In-Progress) as "In Progress" sum(Submitted) as "Apps Submitted" sum(My-InfoUsed) as "My InfoUsed" by Mon-Year
| untable Mon-Year Category Total
0 Karma

kalaiyarasi
Loves-to-Learn Lots

Hi,

For your query, getting results like below:

Mon-Year     Category       Total

Dec-2022    Total Apps      215

Dec-2022    In-Progress      200

Dec-2022     Submitted       152,""

 

To merge the Mon-Year in Single filed as it contains same value, tried these two options but not getting correct count, kindly help


|stats sum(TotalApps) as "Total Apps" sum(In-Progress) as "In Progress" sum(Submitted) as "Apps Submitted" sum(My-InfoUsed) as "My InfoUsed" by Mon-Year
| untable Mon-Year Category Total
|stats values(Category) as Category1 values(Total) as Total1 by Mon-Year

For above query, Mon-Year is merged but count is not  correct

Also tried below options
|stats sum(TotalApps) as "Total Apps" sum(In-Progress) as "In Progress" sum(Submitted) as "Apps Submitted" sum(My-InfoUsed) as "My InfoUsed" by Mon-Year
| untable Mon-Year Category Total
eval Category='Category' + ";" + 'Total'
|stats values(Category) as Category2 by Mon-Year

|eval Category1=split(Category2,";")

above query is not splitting. Kindly help to merge the same Date value in Single field.

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Values() puts the unique values in lexicographical order, try using list()

|stats list(Category) as Category1 list(Total) as Total1 by Mon-Year
0 Karma

kalaiyarasi
Loves-to-Learn Lots

Getting expected results now, thanks much

0 Karma

kalaiyarasi
Loves-to-Learn Lots

Hi,

It's working fine and many thanks for your help

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

This is the third post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...