Hi,
I have this query
index=cox UCE-|rex "UCE-(?<UCE_Code>(\d+))"|lookup UCECodes.csv UCE-Code as UCE_Code|eval ud=UCE_Code+" - "+Desc|eventstats count as grandtotal |eventstats count as ptotal by UCE_Code|eval aatype=if(match(source,"managedServer"),"managed","access")|chart values(Desc) as Description count(UCE_Code) as "UCE Code Count" count(aatype="managed") as "Managed Server Log" count(aatype="access") as "Access Log" values(eval(round(ptotal/grandtotal*100,2))) as Percentage by UCE_Code |rename UCE_Code as "UCE Code - Click for Detail"|sort - "Percentage"|rename ud as "UCE Code - Description"
And I'm interested in the aatype field. When the query runs, the resulting table has everything correct, EXCEPT the columns "Managed Server Log" and "Access Log" are all zeros. However, when I view the event fields, the aatype field shows the correct values.
What am I missing?
Here is the statistics table and event field
Hi there @dbcase
Try like this,
index=cox UCE-
| rex "UCE-(?<UCE_Code>(\d+))"
| lookup UCECodes.csv UCE-Code AS UCE_Code
| eval ud=UCE_Code+" - "+Desc
| eventstats count AS grandtotal
| eventstats count AS ptotal by UCE_Code
| eval aatype=if(match(source,"managedServer"), "managed", "access")
| chart values(Desc) AS Description, count(UCE_Code) AS "UCE Code Count", count(eval(aatype="managed")) AS "Managed Server Log", count(eval(aatype="access")) AS "Access Log", values(eval(round(ptotal/grandtotal*100,2))) AS Percentage by UCE_Code
| rename UCE_Code AS "UCE Code - Click for Detail"
| sort - "Percentage"
| rename ud AS "UCE Code - Description"
Let me know if it works.
Hi there @dbcase
Try like this,
index=cox UCE-
| rex "UCE-(?<UCE_Code>(\d+))"
| lookup UCECodes.csv UCE-Code AS UCE_Code
| eval ud=UCE_Code+" - "+Desc
| eventstats count AS grandtotal
| eventstats count AS ptotal by UCE_Code
| eval aatype=if(match(source,"managedServer"), "managed", "access")
| chart values(Desc) AS Description, count(UCE_Code) AS "UCE Code Count", count(eval(aatype="managed")) AS "Managed Server Log", count(eval(aatype="access")) AS "Access Log", values(eval(round(ptotal/grandtotal*100,2))) AS Percentage by UCE_Code
| rename UCE_Code AS "UCE Code - Click for Detail"
| sort - "Percentage"
| rename ud AS "UCE Code - Description"
Let me know if it works.
Hey alemarzu! That worked perfectly!!! Many thanks!
Nice, I'm glad it helped!