Getting Data In

data from csv file and working on that with splunk query

ektasiwani
Communicator

I have a csv file created by splunk search:
It contain ip count for last 7 days.
it looks like this:

_time 192.168.10.20 192.168.30.46
2015-05-08 145 45
2015-05-09 200 200
2015-05-10 300 34

Now i want to subtract each day count from sum of total count of that ip

for example:
for ip 192.168.30.46 i want out put for date 2015-05-08 as (45+200+34)-45
how can i achieve this with splunk query?

Tags (2)
0 Karma
1 Solution

vganjare
Builder

If you have access to raw events, you can try following query:

...select the raw events.. | table _time ipAddress | eval date = strftime(_time, "%Y-%m-%d") | stats count as countByDate by date,ipAddress | eventstats count as totalcount by ipAddress | eval customCount = totalcount - countByDate 

Thanks!!

View solution in original post

0 Karma

vganjare
Builder

If you have access to raw events, you can try following query:

...select the raw events.. | table _time ipAddress | eval date = strftime(_time, "%Y-%m-%d") | stats count as countByDate by date,ipAddress | eventstats count as totalcount by ipAddress | eval customCount = totalcount - countByDate 

Thanks!!

0 Karma

ektasiwani
Communicator

the query is working but the result is not what i want.
can you explain me above query?
totalcount here is not the sum of day wise counts of an ip address.
my requirement is:
if 192.168.10.3 count for 2nd of may is 340 and 3rd of feb is 45 and fourth of feb is 10

i want to show on 2nd of feb deviation is (340+45+10)-340 , so customCount should show result of this (340+45+10)-340.

0 Karma

vganjare
Builder

Hi,

My bad. Change the eventstats block to following:

| eventstats sum(countByDate) as totalcount by ipAddress

This should fix the problem. Can you please verify and confirm?

Thanks!!

0 Karma

ektasiwani
Communicator

Thanks vganjare , this is working for me.
thnku for your time and help...:)

0 Karma

vganjare
Builder

Do you have control over the csv creation? The number of IP addresses will change over the time and hence building a dynamic query will be a challenge. If you have access to raw events, then you can use eventStats sum(count) by ipAddress and then substract the current count.

Thanks!!

0 Karma

ektasiwani
Communicator

no i dont have control over ip address. i need dynamic query.
ip address number will keep on changing.
is there any way to create dynamic query for this?

0 Karma

vganjare
Builder

Do you have access to raw events?

0 Karma

ektasiwani
Communicator

ya i have access to raw events

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 ...