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!

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

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