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!

Index This | What did the zero say to the eight?

June 2025 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this month’s ...

Splunk Observability Cloud's AI Assistant in Action Series: Onboarding New Hires & ...

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

Now Playing: Splunk Education Summer Learning Premieres

It’s premiere season, and Splunk Education is rolling out new releases you won’t want to miss. Whether you’re ...