topic Re: How to split time into column and other fields into row? in Splunk Search

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

kalaiyarasi — Tue, 24 Jan 2023 15:12:54 GMT

|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

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

ITWhisperer — Tue, 24 Jan 2023 15:26:58 GMT

|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

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

kalaiyarasi — Wed, 25 Jan 2023 14:15:43 GMT

Hi,

It's working fine and many thanks for your help

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

kalaiyarasi — Fri, 27 Jan 2023 08:21:01 GMT

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.

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

ITWhisperer — Fri, 27 Jan 2023 08:41:37 GMT

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

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

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

kalaiyarasi — Fri, 27 Jan 2023 10:56:12 GMT

Getting expected results now, thanks much