Splunk Search

How to add Count Column to a Table?

skoelpin
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

woodcock
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

somesoni2
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

woodcock
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

skoelpin
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

woodcock
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

skoelpin
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...