Joining sourcetype a with sourcetype b where sourcetype b is a reference table with no daily changes

[rajeshjlnt]

I am building a table query to list down tickets against applications. Where tickets are stored in sourcetype 'a' and application names are stored in sourcetype 'b' with a common ID field
When time filter is all time, query works just fine. When time filter is changed to today or this week, i am not getting results. I think this is because there will be no events/new entries in sourcetype 'b' as it is a master table.

I would like to know if using 'join' for this scenario is correct or should i use some other method.
Note: Sourcetype 'a' will have daily events, sourcetype 'b' is static

[to4kawa · Accepted solution]

index=your_index sourcetype=A [index=your_index sourcetype=B | table something_contains_A ]

hi, @rajeshjlnt

Join is not mandatory.

This query is slow, so if you provide a specific log, we can make a more efficient query.

[rajeshjlnt]

Thank you for your response @to4kawa . I am not able to attach sample data ( no karma points) so typing it here,

Tickets(CreatedDaily):
Number Created On catetory_path
INC0000001 12/1/2019 0:00 application1
INC0000002 12/1/2019 0:00 application2
INC0000003 12/1/2019 0:00 application3
INC0000004 12/1/2019 0:00 application4
INC0000005 12/1/2019 0:00 application5
INC0000006 12/1/2019 0:00 application6
INC0000007 12/1/2019 0:00 application7
INC0000008 12/1/2019 0:00 application8
INC0000009 12/2/2019 0:00 application1
INC0000010 12/2/2019 0:00 application2
INC0000011 12/2/2019 0:00 application3
INC0000012 12/2/2019 0:00 application4
INC0000013 12/2/2019 0:00 application5
INC0000014 12/2/2019 0:00 application6
INC0000015 12/3/2019 0:00 application7
INC0000016 12/3/2019 0:00 application8
INC0000017 12/3/2019 0:00 application1
INC0000018 12/3/2019 0:00 application2
INC0000019 12/3/2019 0:00 application3
INC0000020 12/3/2019 0:00 application4
INC0000021 12/3/2019 0:00 application5
INC0000022 12/3/2019 0:00 application6
INC0000023 12/4/2019 0:00 application7
INC0000024 12/4/2019 0:00 application8
INC0000025 12/4/2019 0:00 application1
INC0000026 12/4/2019 0:00 application2
INC0000027 12/4/2019 0:00 application3
INC0000028 12/4/2019 0:00 application4
INC0000029 12/4/2019 0:00 application5
INC0000030 12/4/2019 0:00 application6
INC0000031 12/4/2019 0:00 application7

MasterData(Static)
catetory_path Appication ID Application Name
application1 BS_0001 Splunk
application2 BS_0002 Google
application3 BS_0003 Facebook
application4 BS_0004 Outlook
application5 BS_0005 Skype
application6 BS_0006 Word
application7 BS_0007 Excel
application8 BS_0008 Powerpoint

ResultExpected
Number Created On catetory_path Appication ID Application Name
INC0000001 12/1/2019 0:00 application1 BS_0001 Splunk
INC0000002 12/1/2019 0:00 application2 BS_0002 Google
INC0000003 12/1/2019 0:00 application3 BS_0003 Facebook
INC0000004 12/1/2019 0:00 application4 BS_0004 Outlook
INC0000005 12/1/2019 0:00 application5 BS_0005 Skype
INC0000006 12/1/2019 0:00 application6 BS_0006 Word
INC0000007 12/1/2019 0:00 application7 BS_0007 Excel
INC0000008 12/1/2019 0:00 application8 BS_0008 Powerpoint
INC0000009 12/2/2019 0:00 application1 BS_0001 Splunk
INC0000010 12/2/2019 0:00 application2 BS_0002 Google
INC0000011 12/2/2019 0:00 application3 BS_0003 Facebook
INC0000012 12/2/2019 0:00 application4 BS_0004 Outlook
INC0000013 12/2/2019 0:00 application5 BS_0005 Skype
INC0000014 12/2/2019 0:00 application6 BS_0006 Word
INC0000015 12/3/2019 0:00 application7 BS_0007 Excel
INC0000016 12/3/2019 0:00 application8 BS_0008 Powerpoint
INC0000017 12/3/2019 0:00 application1 BS_0001 Splunk
INC0000018 12/3/2019 0:00 application2 BS_0002 Google
INC0000019 12/3/2019 0:00 application3 BS_0003 Facebook
INC0000020 12/3/2019 0:00 application4 BS_0004 Outlook
INC0000021 12/3/2019 0:00 application5 BS_0005 Skype
INC0000022 12/3/2019 0:00 application6 BS_0006 Word
INC0000023 12/4/2019 0:00 application7 BS_0007 Excel
INC0000024 12/4/2019 0:00 application8 BS_0008 Powerpoint
INC0000025 12/4/2019 0:00 application1 BS_0001 Splunk
INC0000026 12/4/2019 0:00 application2 BS_0002 Google
INC0000027 12/4/2019 0:00 application3 BS_0003 Facebook
INC0000028 12/4/2019 0:00 application4 BS_0004 Outlook
INC0000029 12/4/2019 0:00 application5 BS_0005 Skype
INC0000030 12/4/2019 0:00 application6 BS_0006 Word
INC0000031 12/4/2019 0:00 application7 BS_0007 Excel

[ashajambagi]

@rajeshjlt Did you try using lookup with static data?

[ashajambagi]

@rajeshjlnt how are the new entries added? You can have an automatic lookup and use it ?

[rajeshjlnt]

@ashajambagi I took a dump and created a lookup table to solve my problem. I expect new entries to this table. I do not know how to update a lookup table with new entries. So i am took the route of creating an index. Is there a way to append data to a lookup table automatically?

[ashajambagi]

There is option of automatic lookup or you can create a scheduled report to populate a lookup, using outputlookup command.

[to4kawa]

please close your question.

[to4kawa]

Has this question been solved?
Please provide a solution.

[rajeshjlnt]

@ashajambagi yes, i have tried and it works. But in my case static data is not really static, new entries will be added atleast montly.

[to4kawa]

hi, @rajeshjlnt

index=your_index source=Tickets
|inputlookup MasterData.csv 
|table Number "Created On" catetory_path "Appication ID" "Application Name"

Static source is easier with inputlookup.

index=your_index source=Tickets OR source=static
| stats list(*) as * by catetory_path
| eval raw=mvzip(Number,mvzip('Created On',mvzip('Appication ID' ,'Application Name')))
| mvexpand raw
| eval _raw=split(raw,",")
| eval Number=mvindex(_raw,0), "Created On"=mvindex(_raw,1),  "Appication ID"=mvindex(_raw,2), "Application Name"=mvindex(_raw, 3)
| table Number "Created On" catetory_path "Appication ID" "Application Name"

Maybe, It works.

| makeresults
| eval _raw="Number,Created_On,category_path
INC0000001,12/1/2019 0:00,application1
INC0000002,12/1/2019 0:00,application2
INC0000003,12/1/2019 0:00,application3
INC0000004,12/1/2019 0:00,application4
INC0000005,12/1/2019 0:00,application5
INC0000006,12/1/2019 0:00,application6
INC0000007,12/1/2019 0:00,application7
INC0000008,12/1/2019 0:00,application8
INC0000009,12/2/2019 0:00,application1
INC0000010,12/2/2019 0:00,application2
INC0000011,12/2/2019 0:00,application3
INC0000012,12/2/2019 0:00,application4
INC0000013,12/2/2019 0:00,application5
INC0000014,12/2/2019 0:00,application6
INC0000015,12/3/2019 0:00,application7
INC0000016,12/3/2019 0:00,application8
INC0000017,12/3/2019 0:00,application1
INC0000018,12/3/2019 0:00,application2
INC0000019,12/3/2019 0:00,application3
INC0000020,12/3/2019 0:00,application4
INC0000021,12/3/2019 0:00,application5
INC0000022,12/3/2019 0:00,application6
INC0000023,12/4/2019 0:00,application7
INC0000024,12/4/2019 0:00,application8
INC0000025,12/4/2019 0:00,application1
INC0000026,12/4/2019 0:00,application2
INC0000027,12/4/2019 0:00,application3
INC0000028,12/4/2019 0:00,application4
INC0000029,12/4/2019 0:00,application5
INC0000030,12/4/2019 0:00,application6
INC0000031,12/4/2019 0:00,application7"
| multikv forceheader=1
| table Number,Created_On,category_path
`comment("this is sample data")`
| join category_path [| makeresults
    | eval _raw="category_path,Appication_ID,Application_Name
application1,BS_0001,Splunk
application2,BS_0002,Google
application3,BS_0003,Facebook
application4,BS_0004,Outlook
application5,BS_0005,Skype
application6,BS_0006,Word
application7,BS_0007,Excel
application8,BS_0008,Powerpoint"
    | multikv forceheader=1 
    | table category_path,Appication_ID,Application_Name]

Other case, You need join to get the results you expected.