Splunk Search

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

rajeshjlnt
Path Finder

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

Tags (1)
0 Karma
1 Solution

to4kawa
Ultra Champion
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.

View solution in original post

0 Karma

to4kawa
Ultra Champion
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.

0 Karma

rajeshjlnt
Path Finder

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

0 Karma

ashajambagi
Communicator

@rajeshjlt Did you try using lookup with static data?

0 Karma

ashajambagi
Communicator

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

0 Karma

rajeshjlnt
Path Finder

@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?

0 Karma

ashajambagi
Communicator

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

0 Karma

to4kawa
Ultra Champion

please close your question.

0 Karma

to4kawa
Ultra Champion

Has this question been solved?
Please provide a solution.

0 Karma

rajeshjlnt
Path Finder

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

0 Karma

to4kawa
Ultra Champion

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.

0 Karma
Get Updates on the Splunk Community!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...