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.
Product1 5 5
Product2 1 4
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
...
| streamstats count
| eval Product="Product".count
| table Product *
If you want to make it simply from the top, this is it.
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
@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.
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
Put this after stats
.
| eval passPct = Pass_Count * 100 / Total_Checks, failPct = failCount * 100 / Total_Checks
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?
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.
Please share your current search query.
Here is my current search
index=abc source="bcd" earliest=-24h@h | where like('Source_Team', "%BDLM%") | rex max_match=0 "(?PPass*)" | eval count=mvcount(Method) | stats sum(count) as Compliant | appendcols [ search index=abc source="bcd" earliest=-24h@h | where like('Source_Team', "%BDLM%") | rex max_match=0 "(?PFail*)" | eval count=mvcount(Method) | stats sum(count) as Non-Compliant]