topic Re: data from csv file and working on that with splunk query in Getting Data In

data from csv file and working on that with splunk query

ektasiwani — Fri, 15 May 2015 10:12:19 GMT

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?

Re: data from csv file and working on that with splunk query

vganjare — Fri, 15 May 2015 10:47:55 GMT

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

Re: data from csv file and working on that with splunk query

ektasiwani — Fri, 15 May 2015 11:56:52 GMT

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?

Re: data from csv file and working on that with splunk query

vganjare — Mon, 18 May 2015 04:25:33 GMT

Do you have access to raw events?

Re: data from csv file and working on that with splunk query

ektasiwani — Mon, 18 May 2015 05:33:39 GMT

ya i have access to raw events

Re: data from csv file and working on that with splunk query

vganjare — Mon, 18 May 2015 06:33:59 GMT

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

Re: data from csv file and working on that with splunk query

ektasiwani — Mon, 18 May 2015 07:42:19 GMT

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.

Re: data from csv file and working on that with splunk query

vganjare — Mon, 18 May 2015 08:00:16 GMT

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

Re: data from csv file and working on that with splunk query

ektasiwani — Mon, 18 May 2015 08:45:17 GMT

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