Archive

How to add Count Column to a Table?

SplunkTrust
SplunkTrust

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)
Tags (1)
0 Karma
1 Solution

Esteemed Legend

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

View solution in original post

SplunkTrust
SplunkTrust

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)
0 Karma

Esteemed Legend

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

View solution in original post

SplunkTrust
SplunkTrust

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.

0 Karma

Esteemed Legend

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

0 Karma

SplunkTrust
SplunkTrust

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"
0 Karma