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
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
Uhmm strange I have only 1 sourcetype and use 1 index.
Anyway thx for the macro link I look into that right now
what happens if you take out ipAddress="*"
as well in the search?
same result.
Why is it so important to get rid of that | join [search] part?
well, it is not important but most of the searches using join
can be done with stats
and will be therefore much faster 😉
Okay, now we have a question and immediatly two questions pop-up in my head:
| join [search]
in your search
and timewrap
app https://apps.splunk.com/app/1645/ ? This will do time over time comparison for youIn 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.
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 🙂
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
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