Splunk Search

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

Cosmoon
New Member

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

Tags (4)
0 Karma

MuS
SplunkTrust
SplunkTrust

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#ans...
http://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-joi...

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

0 Karma

Cosmoon
New Member

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

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

0 Karma

MuS
SplunkTrust
SplunkTrust

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

0 Karma

Cosmoon
New Member

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

0 Karma

MuS
SplunkTrust
SplunkTrust

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

MuS
SplunkTrust
SplunkTrust

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

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

Cosmoon
New Member

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.

0 Karma

MuS
SplunkTrust
SplunkTrust

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 🙂

Cosmoon
New Member

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
0 Karma

Cosmoon
New Member

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