Splunk Search

join between 2 lookups and using a date/time field that is not splunks default


I need to create a trend chart between 2 lookups which shows a difference between total closed and total opened. I have written the querys independently which work fine but im struggling to join them together and then on top of that i need to use a date field to gather the totals to make a trend. for total closed i have the below:

| inputlookup test_case | search  test_issue_url__c!=null if_parent_case__c="0" type!="test Migration" type!="testimplementation" system_area_1__c!="test1" system_area_1__c!="test2*" closeddate!=null | eval dateclosed=substr(closeddate,1,10) | where dateclosed>"2019-06-01 00:00:00.000" |stats count by dateclosed | sort dateclosed

the below is the total open

| inputlookup test_case | search test_latest_status__c!=Closed test_latest_status__c!=Approved test_latest_status__c!="Cancelled" (test_issue_url__c!="null" OR test_issue_id__c!="null") test_labels__c!="*test1*" test_labels__c!=*test2*  test_issue_key__c=ip-* test_latest_status__c!=null system_area_1__c!=testing1 system_area_1__c!=testing2* owner_role__c=*support* if_parent_case__c<=0000001 | eval Product_Type=case(
OR system_area_1__c=="testing4" 
OR system_area_1__c=="testing5" 
OR system_area_1__c=="testing6" 
OR system_area_1__c=="testing7" 
OR system_area_1__c=="testing8" 
OR system_area_1__c=="testing9" 
OR system_area_1__c=="testing10" 
OR system_area_1__c=="testing11" 
OR system_area_1__c=="testing12" 
OR system_area_1__c=="testing14" 
OR system_area_1__c=="testing15" 
OR system_area_1__c=="testing16" 
OR system_area_1__c=="testing17" 
OR system_area_1__c=="testing18" 
OR system_area_1__c=="testing19"
OR system_area_1__c=="testing20"
OR system_area_1__c=="testing21"
OR system_area_1__c=="testing22"
OR system_area_1__c=="testing23" 
OR system_area_1__c=="testing24","testcolumn"
,system_area_1__c=="testing1","test1", system_area_2__c=="testing2","test2" )  | eval closedtest=case(test_latest_status__c=="Closed","testClosed") | replace "Highest" with "1", High with 2, Medium with 3, Low with 4 in test_priority__c | rename sla_days_remaining__c as "SLA Days Remaining", test_priority__c as "test Priority", test_issue_url__c as "test URL", is_sla_case__c as "Is SLA test", test_issue_Key__c as "test Key" | table subject "test URL" "test Key" "test Priority" "SLA Days Remaining" "Is SLA test" | sort -"Is SLA test" "SLA Days Remaining" "test Priority" | stats count

I need to join them together and add a trend graph based on a date/time column that is not used with splunks time picker and break it down by total per day.

Any help on how i can do this would be appreciated

0 Karma

Esteemed Legend

The trick that you are missing is to do this in a (hidden) base search that is not used anywhere but creates tokens for you like this:
Add this to your existing search:

|makeresults | addinfo

Then store Timepicker details in tokens with XML like this:

<eval token="earliest">info_min_time</eval>
<eval token="latest">info_max_time</eval>

Then to modify your other data based on another date-ish field (like closeddate), do this:

|inputlookup | eval _time = strptime(substr(closeddate,1,10), "%your%format%string%here%") | where (_time >= $info_min_time$ AND _time <= $info_max_time$)
0 Karma


Thanks @woodcock

This didnt work for me but i found another simple way to join them together by append but still doesnt give me exactly what i need. Ill place another question on the community for this as its separate to this

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!