topic Re: How to add Count Column to a Table? in Splunk Search

How to add Count Column to a Table?

skoelpin — Fri, 17 Jul 2015 14:48:45 GMT

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)

Re: How to add Count Column to a Table?

woodcock — Fri, 17 Jul 2015 15:51:43 GMT

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

Re: How to add Count Column to a Table?

skoelpin — Tue, 29 Sep 2020 06:45:00 GMT

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.

Re: How to add Count Column to a Table?

somesoni2 — Mon, 20 Jul 2015 16:00:27 GMT

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)

Re: How to add Count Column to a Table?

woodcock — Mon, 20 Jul 2015 16:28:46 GMT

I had a typo in the solution but I fixed it (I had the same field name in both values commands). Try it now.

Re: How to add Count Column to a Table?

skoelpin — Mon, 20 Jul 2015 19:15:35 GMT

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"