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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...