Archive
Highlighted

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
Highlighted

Re: How to add Count Column to a Table?

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

Highlighted

Re: How to add Count Column to a Table?

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 RTGWebRequest 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
Highlighted

Re: How to add Count Column to a Table?

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
Highlighted

Re: How to add Count Column to a Table?

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
Highlighted

Re: How to add Count Column to a Table?

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