I have a table with 3 fields (IP Address, Web Request, and Browser used).. How can I add a column to that table to count the frequency of IP addresses?
I suspect that I have to change my search around because the IP Addresses are listed multiple times, so I think I have to make them list one time then add a column to count the occurrences.
So can someone help me add a column to count the number of times the IP is clicked
Here's my search
index=access OR index=main | transaction RTG_JSession | table RTG_IPmain dc(RTG_IPmain) RTG_WebRequest RTG_Browser | where isnotnull(RTG_IPmain)
Like this:
index=access OR index=main | transaction RTG_JSession | table RTG_IPmain RTG_WebRequest RTG_Browser | where isnotnull(RTG_IPmain) | stats count values(RTG_WebRequest) values(RTG_Browser) BY RTG_IPmain
Try this
index=access OR index=main | transaction RTG_JSession | table RTG_IPmain RTG_WebRequest RTG_Browser | eventstats dc(RTG_IPmain) as Count | where isnotnull(RTG_IPmain)
Like this:
index=access OR index=main | transaction RTG_JSession | table RTG_IPmain RTG_WebRequest RTG_Browser | where isnotnull(RTG_IPmain) | stats count values(RTG_WebRequest) values(RTG_Browser) BY RTG_IPmain
I had to take the space out between values and (RTG_WebRequest) to get it to work. But its close to what I need. I see a Count column there now but I'm having the following issues..
1) Anytime the count is greater than 1, there is no RTG_WebRequest results (They are null)
2) The RTG_Browser column disappeared
Also, the search looks like it's counting the WebRequest occurrences and not the IPmain occurrences.
I had a typo in the solution but I fixed it (I had the same field name in both values
commands). Try it now.
Works perfectly! I was getting some null values in the RTG_WebRequest due to an error in my regular expression when I extracted that field. I did a lookbehind which didn't account for a string I wasn't aware of. I included that and all the fields populated exactly like they should have
Here's my search for anyone needing it in the future
index=access OR index=main | transaction RTG_JSession | table RTG_IPmain RTG_WebRequest RTG_Browser | stats count values(RTG_Browser) values(RTG_WebRequest) BY RTG_IPmain | sort -count | rename RTG_IPmain AS "IP Address" | rename count AS "JSession Count" | rename "values(RTG_Browser)" AS "Browser" | rename "values(RTG_WebRequest)" AS "Web Request"