topic Re: evaluate values from two different lookups in Splunk Search

evaluate values from two different lookups

bic — Tue, 29 Sep 2020 15:06:47 GMT

I have a lookup file assignment_schedule containing below sample data

assignment_group   task_order   schedule                  available_mins_weekdays     available_mins_weekends
rxpress            Ecommerce    Avail:24x7 Maint:N/A      7200                        2880

I need to evaluate the number of available mins in a month and for that have another lookup , calculation will be (available_mins_weekdays/5)*weekdays

month     saturday     sunday     weekdays     days
Jan       4            5          22           31

I expect data to be in form

assignment_group     month     available mins
abc                  Jan       22*(7200/5)

I am trying below query but it is not giving me results

|inputlookup assignment_schedule
| join type=outer assignment_group  [search index="JUNK" sourcetype=applications [search index=test source=month_data.csv| eval wk_min=(available_mins_weekdays/5)*weekdays ]

Re: evaluate values from two different lookups

somesoni2 — Tue, 29 Sep 2020 15:09:59 GMT

Your query is quite confusing right now. Could you provide the main search from where data will be generated? is month_data.csv your second lookup OR it's indexed data? Meanwhile give this a try (assuming month_data.csv is second lookup)

index="JUNK" sourcetype=applications 
| lookup assignment_schedule assignment_group OUTPUT available_mins_weekdays
| eval month=strftime(_time,"%b")
| lookup month_data.csv month OUTPUT weekdays
| eval wk_min=(available_mins_weekdays/5)*weekdays
| table assignment_group month wk_min ...other fields...

Re: evaluate values from two different lookups

DalJeanis — Mon, 31 Jul 2017 15:04:00 GMT

@bic - I cleaned up the formatting and marked the code for you. I also added a closing square brace on your query, since it could not possibly run without it, but I suspect that the code isn't exactly right.

Re: evaluate values from two different lookups

woodcock — Mon, 31 Jul 2017 15:12:26 GMT

Try this:

index="JUNK" sourcetype=applications
[search index=test source=month_data.csv | eval wk_min=(available_mins_weekdays/5)*weekdays ]
| append [|inputlookup assignment_schedule | eval sourcetype="fromInputlookup" ]
| stats values(*) AS * dc(sourcetype) AS numSourcetypes BY assignment_group
| where numSourcetypes=1

This keeps only those events which are in either source but not both (outer join).

Re: evaluate values from two different lookups

DalJeanis — Mon, 31 Jul 2017 15:41:47 GMT

In general, here's how to do a cross join in splunk...

your first search or file ...
| eval CrossJoinMe = 1 
| join max=0 CrossJoinMe [your second search or file | eval CrossJoinMe = 1 ]

And here's your search...

| inputlookup assignment_schedule
| eval CrossJoinMe = 1 
| rename COMMENT as "the above gets all the assignment groups"
| rename COMMENT as "In this case, 1 means Yes I Really Want All Records Cross Joined "

| rename COMMENT as "join every record above with every record in the other table"
| rename COMMENT as " max=0 also has to be set to mean Yes I Really Want All Records Cross Joined "
| join max=0 CrossJoinMe [search index=test source=month_data.csv | streamstats count as recno | eval CrossJoinMe = 1 ] 

| rename COMMENT as "calculate minutes, order records and cut to desired fields"
| eval available_mins = round(weekdays*(available_mins_weekdays/5))
| sort 0 assignment_group recno
| table assignment group month available_mins

One additional note - I assumed, since you have a 3-character month, that the month records are in date order but without any year or actual date information. That order is what the | streamstats count as recno and | sort 0 assignment_group recno are trying to preserve. If, on the other hand, the month date is a real date, then get rid of the streamstats and change the sort to sort by the assignment group and date.