topic Re: Adding First Column to the Table in Splunk Search

Adding First Column to the Table

dustintroop — Sun, 24 May 2020 09:33:28 GMT

I'm generating the output for Column 2, 3 from search. I want to add column to the start of the table. In the below example, I want to add the First Column "Product" with values "Product1" & "Product2". Please shed some light here. Appreciate your help in advance.

example

Product Pass Count Fail Count

Product1 5 5
Product2 1 4

Re: Adding First Column to the Table

richgalloway — Sun, 24 May 2020 14:11:08 GMT

Please share your current search query.

Re: Adding First Column to the Table

dustintroop — Wed, 30 Sep 2020 05:31:45 GMT

Here is my current search

Re: Adding First Column to the Table

richgalloway — Sun, 24 May 2020 19:57:44 GMT

One can add fields ("columns ") at any time and then use the table command to put them in the desired order. In this case, however, the two searches can be combined and all fields generated at once with stats. The forum mangled the regular expressions (use the code 101010 button to prevent that) so you'll have to fix them before running this example.

index=abc source="bcd" earliest=-24h@h 
| where like('Source_Team', "%BDLM%") 
| rex max_match=0 "(?<Pass>Pass*)" 
| rex max_match=0 "(?<Fail>Fail*)" 
| eval passCount=mvcount(Pass), failCount=mvcount(Fail) 
| stats sum(passCount) as Compliant, sum(failCount) as Non-Compliant by Product

Re: Adding First Column to the Table

dustintroop — Sun, 24 May 2020 20:25:16 GMT

@richgalloway. Thanks a bunch for consolidating the search. It is working when i remove the "by product" string from the search at the end. So, i'm not able to add the First column

The Events against which i'm performing this search doesn't have a field called product. I want to add the first column manually [since this field didn't exists anywhere] to the table.

Re: Adding First Column to the Table

richgalloway — Sun, 24 May 2020 20:42:55 GMT

Not having the Product field in the raw data complicates matters a bit. It's easy enough to use eval Product= foo to add a field, but how will Splunk know which product name to associate with each event? Is there some other field that be used to determine the product?

Re: Adding First Column to the Table

to4kawa — Sun, 24 May 2020 20:55:24 GMT

...
| streamstats count
| eval Product="Product".count
| table Product *

If you want to make it simply from the top, this is it.

Re: Adding First Column to the Table

dustintroop — Sun, 24 May 2020 21:18:35 GMT

Thanks again @richgalloway. You have driven me to the solution. I have a unique identifier for a each product in the "Source_Team" field. So i renamed both the field name as well as the values in that fields to have what i desired to display.

Appreciate your Valuable Time & help.

Re: Adding First Column to the Table

dustintroop — Wed, 30 Sep 2020 05:31:48 GMT

One last help. how can i add Pass percentage and Fail Percentage in the table ?. Below is the current search.

index=abcd source="bcd" earliest=-24h@h
| where like('Source_Team', "%BDLM%")
| rex max_match=0 "(?Pass*)"
| rex max_match=0 "(?Fail*)"
| rex max_match=0 "(?(Pass|Fail))"
| eval passCount=mvcount(Pass), failCount=mvcount(Fail), TotalCount=mvcount(BOTH)
| stats sum(TotalCount) as Total_Checks, sum(passCount) as Pass_Count, sum(failCount) as Fail_Count by Product
| fillnull value=0 Pass_Count,Fail_Count,Total_Checks

Re: Adding First Column to the Table

richgalloway — Sun, 24 May 2020 23:35:33 GMT

Put this after stats.

| eval passPct = Pass_Count * 100 / Total_Checks, failPct = failCount * 100 / Total_Checks