topic Re: How to search a weekly total and daily average per week for 4 weeks per IP address? in Splunk Search

How to search a weekly total and daily average per week for 4 weeks per IP address?

Cosmoon — Mon, 28 Sep 2020 18:15:39 GMT

Hi,

After struggling for some time now I turn to you guys for help. The community page have been a great aid but I'm stuck and hope someone can show me a way out.

Every workday I scan a different range of our network for printers, same range is scanned every Monday and so on. From the printers I find I pull the counter info. The final data that goes into splunk looks like this:

08/10/14 03:10:50
"Model"="Aficio MP 5001"
"Vendor"="RICOH"
"ipAddress"="10.101.31.200"
"MachineTotal"="173060"
"CopyTotal"="143463"
"PrintTotal"="21295"
"FaxTotal"="8302"

From this data I managed to get the week total and from that the daily average with this search:

sourcetype=txt
| eventstats latest(_time) as latest_time
| where _time<relative_time(latest_time,"-7d")
| stats range(MachineTotal) as weektotal by ipAddress
| eval weekcount=round(weekcount)
| join[search]
| fieldformat daily_average=round(weekcount/5)
| fields ipAddress, daily_average, weekcount

What I can't find out is how to do this for a 4 week period. So that I get something like: ipAddress, Week_1_daily_average, Week_1_weekcount, Week_2_daily_average, Week_2_weekcount, Week_3_daily_average, Week_3_weekcount, Week_4_daily_average, Week_4_weekcount

Thank you in advance for the help

Re: How to search a weekly total and daily average per week for 4 weeks per IP address?

MuS — Tue, 25 Nov 2014 21:21:38 GMT

Okay, now we have a question and immediatly two questions pop-up in my head:

why do you have a | join [search] in your search and
do you know the timewrap app https://apps.splunk.com/app/1645/ ? This will do time over time comparison for you

Re: How to search a weekly total and daily average per week for 4 weeks per IP address?

Cosmoon — Tue, 25 Nov 2014 21:46:04 GMT

In my actual search the fields list is longer so I also get Vendor, model etc without | join [search] those fields are empty.

I tried timewrap but I always get stuck because of the calculations I have to do first.

Re: How to search a weekly total and daily average per week for 4 weeks per IP address?

MuS — Tue, 25 Nov 2014 21:52:37 GMT

sorry but this makes no sense, having empty fields because you don't use a | join [search] ... so where are these filed coming from if not from your base search? other index? So please: put your actual search here and if possible some actual events and the expected result - thanks 🙂

Re: How to search a weekly total and daily average per week for 4 weeks per IP address?

Cosmoon — Tue, 25 Nov 2014 22:23:03 GMT

the search is:

sourcetype=txt ipAddress="*" | eventstats latest(_time) as latest_time | where _time<relative_time(latest_time,"-7d") | stats range(MachineTotal) as Weekcount by ipAddress | eval Weekcount=round(Weekcount) | join[search] |fieldformat Daycount=round(Weekcount/5) | fields HostName, ipAddress, macAddress, Firmware, Serial, SystemUpTime, Vendor, Model, Contact, Location, Daycount, Weekcount, MachineTotal, PrinterTotal, CopieTotal, FaxTotal

HostName ipAddress macAddress Firmware Serial SystemUpTime Vendor Model Contact Location Daycount Weekcount MachineTotal PrinterTotal CopieTotal FaxTotal
P000824 10.101.225.227 0:26:73:36:2e:db 3.06 V9513401741 4:21:14:26.00   Ricoh Aficio MP C4501  VG-JP Wavre 1719    8594    170858  46986   2
P000729 10.101.31.200  0:26:73:37:ac:f9 1.02 V8013500447 19:2:52:39.00   Ricoh Aficio MP 5001               1164    5820    180623  23430   8653
P000728 10.101.55.200  0:26:73:36:bd:8b 1.02 V8013500398 61:1:49:51.00   Ricoh Aficio MP 5001               427     2137    68921   19927   0
P000730 10.101.55.201  0:26:73:37:ac:fa 1.03 V8013500458 36:0:57:52.00   Ricoh Aficio MP 5001               1503    7515    220973  11099   11344
P000825 10.102.225.227 0:26:73:36:1c:69 3.06 V9513401748 104:22:40:52.00 Ricoh Aficio MP C4501  VG-JP Wavre 2567    12835   214880  58382   2
P310055 10.102.225.249 0:26:73:80:78:21 3.18 W914P104818 60:3:32:36.00   Ricoh Aficio MP 301                163     814     6836    4067    2065

Re: How to search a weekly total and daily average per week for 4 weeks per IP address?

Cosmoon — Tue, 25 Nov 2014 22:29:21 GMT

If I do this search:

sourcetype=txt ipAddress="*" | eventstats latest(_time) as latest_time | where _time<relative_time(latest_time,"-7d") | stats range(MachineTotal) as Weekcount by ipAddress | eval Weekcount=round(Weekcount) |fieldformat Daycount=round(Weekcount/5) | fields HostName, ipAddress, macAddress, Firmware, Serial, SystemUpTime, Vendor, Model, Contact, Location, Daycount, Weekcount, MachineTotal, PrinterTotal, CopieTotal, FaxTotal

the result is this:

HostName    ipAddress   macAddress  Firmware    Serial  SystemUpTime    Vendor  Model   Contact     Location    Daycount    Weekcount   MachineTotal    PrinterTotal    CopieTotal  FaxTotal
    10.101.225.153                                  6       31               
    10.101.225.227                                  1719    8594                 
    10.101.31.200                                   1164    5820                 
    10.101.55.200                                   427         2137                 
    10.101.55.201                                   1503    7515                 
    10.102.225.227                                  2567    12835                
    10.102.225.249                                  163         814                  
    10.11.10.248                                        2869    14343                
    10.11.10.249                                        1222    6108

Re: How to search a weekly total and daily average per week for 4 weeks per IP address?

MuS — Wed, 26 Nov 2014 07:11:32 GMT

Hi Cosmoon,

okay, the missing fields in your second search come from either a different index or sourcetype, because your subsearch in the join will search through all indexes and sourcetpyes which are available to you.

Basically you can identify the sourcetype for the missing fields and add it in the base search like this

sourcetype=txt ipAddress="*" OR sourcetype=<ForTheMissingFields> | ...

and leave the join... out of your search.

Take a look at this two answer to get an idea how it can be done:
http://answers.splunk.com/answers/185829/how-to-create-a-custom-macro-function-inside-the-s.html#answer-186115
http://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-join-append-or-use-of-subsearches.html

The first link is something similar to your use case, a time over time comparison search which was originally build by using multiple sub-searches....the second link is about the topic how to compare fields from different sources, sourcetypes or indexes.

hope this gets you started ...

cheers, MuS

Re: How to search a weekly total and daily average per week for 4 weeks per IP address?

Cosmoon — Wed, 26 Nov 2014 09:46:26 GMT

Uhmm strange I have only 1 sourcetype and use 1 index.

Anyway thx for the macro link I look into that right now

Re: How to search a weekly total and daily average per week for 4 weeks per IP address?

MuS — Wed, 26 Nov 2014 09:55:13 GMT

what happens if you take out ipAddress="*" as well in the search?

Re: How to search a weekly total and daily average per week for 4 weeks per IP address?

Cosmoon — Wed, 26 Nov 2014 10:26:52 GMT

same result.
Why is it so important to get rid of that | join [search] part?

Re: How to search a weekly total and daily average per week for 4 weeks per IP address?

MuS — Wed, 26 Nov 2014 11:18:19 GMT

well, it is not important but most of the searches using join can be done with stats and will be therefore much faster 😉