I'm trying to get percentages based on the number of logs per table. I want the results to look like this:
**Table Count Percentage**
Total 14392 100
TBL1 8302 57.68
TBL2 4293 29.93
TBL3 838 5.82
TBL4 639 4.44
TBL5 320 2.22
Here's my search so far:
text = "\*" (TBL1 OR TBL2 OR TBL3 OR TBL4 OR TBL5) | eventstats count AS Total
| append [search PAJYE text = "\*TBL1\*" | stats count | eval "Count Type" = "TBL1 Count" | eval "Percentage" = ((count/Total)\*100)]
| append [search PAJYE text = "\*TBL2\*" | stats count | eval "Count Type" = "TBL2 Count" | eval "Percentage" = ((count/Total)\*100)]
| append [search PAJYE text = "\*TBL3\*" | stats count | eval "Count Type" = "TBL3 Count" | eval "Percentage" = ((count/Total)\*100)]
| append [search PAJYE text = "\*TBL4\*" | stats count | eval "Count Type" = "TBL4 Count" | eval "Percentage" = ((count/Total)\*100)]
| append [search PAJYE text = "\*TBL5\*" | stats count | eval "Count Type" = "TBL5 Count" | eval "Percentage" = ((count/Total)\*100)]
| append [search PAJYE text = "\*" (TBL1 OR TBL2 OR TBL3 OR TBL4 OR TBL5) | stats count | eval "Count Type" = "Total Count" | eval "Percentage" = ((count/Total)\*100)]
| rename count as "Count"
| sort - "Count"
| table "Count Type", "Count", "Percentage"
I've tried so many different methods of trying to get this to work. My results are either a percentage column with no data, the counts get messed up, or pages of empty rows following my data.
Any help would be appreciated.
This should be easily simplified.
Try:
<search> (TBL1 OR TBL2 OR TBL3 OR TBL4 OR TBL5)|rex "TBL(?P<table_number>\d+)" |stats count by table_number | eventstats sum(count) AS total | eval percent=(count/total)*100
Once these results come in it can be refined to something useable.
Hi @hduncan7 ,
Did you have a chance to check out any answers? If it worked, please resolve this post by approving it! If your problem is still not solved, keep us updated so that someone else can help you.
Thanks for posting!
How do I approve? I don't see a button to do that?
This should be easily simplified.
Try:
<search> (TBL1 OR TBL2 OR TBL3 OR TBL4 OR TBL5)|rex "TBL(?P<table_number>\d+)" |stats count by table_number | eventstats sum(count) AS total | eval percent=(count/total)*100
Once these results come in it can be refined to something useable.