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!

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...