I can make it wider by reducing the time frame, but last 7 days is usually the default. Are you sure we can't do it on the CSS part? Thanks ... View more

Hi @yuanliu  First, I would like to thank you for your help. This is "partly" related to my previous post that you solved, but I will describe it better here https://community.splunk.com/t5/Splunk-Search/How-do-I-quot-Left-join-quot-by-appending-CSV-to-an-index-in/m-p/697794#M237015 This is just an example: I have a host table containing IP and hostname, approximately 100k rows with unique IPs I have a contact table containing IP and contact, approximately 1> million rows with unique IPs Both can be accessed with DBX query,  but unfortunately they are both located in different DB connections, so it's not possible to join them at the backend. So, the workaround is to filter out subnet on the contact DB and use subsearches to join the contact DB with the Host DB Due to 50k rows limit using subsearch, I ran a separate query on the contact DB to find out the number of rows for each subnet, then I grouped them together to make sure the number of rows is below 50k. (Please see the diagram below) Group 1 = 40 rows, Group 2 = 45k rows,  and Group 3 = 30k rows. After that, I used left join for each group on the contact DB with the Host DB. Since I don't control the growth of data in the Contact DB,  I am trying to figure out a way to get an email alert if one of the groups exceeded 50k limit. I think I am able to create a scheduled report to produce the stats of each subnet in the group, but going back to my original question: I simply want to know if it's possible for Splunk to send me an email alert only if it meets certain thresholds. The subsearch is only one of my cases. Another case is  I have multiple reports that run daily, I intend to read the reports only if there is a problem, such as empty data, meeting certain thresholds, etc.       Input: Host table ip host 10.0.0.1 host1 10.0.0.2 host2 10.0.0.3 host3 10.1.0.1 host4 10.1.0.2 host5 10.1.0.3 host6 10.2.0.1 host7 10.2.0.2 host8 10.2.0.3 host9 Contact table ip contact 10.0.0.1 person1 10.0.0.2 person2 10.0.0.3 person3 10.1.0.1 person4 10.1.0.2 person5 10.1.0.3 person6 10.2.0.1 person7 10.2.0.2 person8 10.2.0.3 person9 Output:  Join host and contact DB ip host contact 10.0.0.1 host1 person1 10.0.0.2 host2 person2 10.0.0.3 host3 person3 10.1.0.1 host4 person4 10.1.0.2 host5 person5 10.1.0.3 host6 person6 10.2.0.1 host7 person7 10.2.0.2 host8 person8 10.2.0.3 host9 person9 ... View more

Hi @yuanliu  Thank you for your suggestion. The subsearch has a max 50k limit, not 5k. If one or more subsearches hit the 50k limitation, I'd want to get an email notification indicating which subsearch exceeded the 50k limit.  In the example  below, an email alert will be sent indicating that 2 subsearches exceed the 50k limit: search3 = 60k rows and search4 = 70k rows.  I can create a scheduled report that sends an email every day, but I am not sure if the report has the ability to send emails only when it meets a certain condition. search1 | join max=0 type=left ip [search ip="10.1.0.0/16" |eval this = "search 2"] | join max=0 type=left ip [search ip="10.2.0.0/16" |eval this = "search 3"] | join max=0 type=left ip [search ip="10.3.0.0/16" |eval this = "search 4"]   ... View more

Hi @PickleRick  I accepted your previous suggestion using eventstats as solution Thank you for your help.   Thanks for bringing me into attention that eventstats also have memory limitation, another limits.conf that only admin can modify 🙂      The default setting is max_mem_usage_mb=200MB.  How do I know if my dataset will ever hit 200 or not? Does streamstats have limitation? How do I use streamstats in my case?    When I changed eventstats to streamstats, it will show incremental data, instead of total, so I need to figure out on how to filter out if it's possible. Thanks Streamstats result dc ip location name dc    ip location name 1 1.1.1.1 location-1 name0 2 1.1.1.1 location-1 name1 1 1.1.1.2 location-2 name2 1 1.1.1.3 location-3 name0 2 1.1.1.3 location-3 name3 1 1.1.1.4 location-4 name4 2 1.1.1.4 location-4 name4b 1 1.1.1.5 location-0 name0 1 1.1.1.6 location-0 name0 0 1.1.1.7 location-7   ... View more

Hi @PickleRick , I tested your suggestion and it worked. Thank you for your help. 1) I added one more case where the IP has an empty name.   I added condition in the where clause (dc=0) and it worked.  I am afraid if I used isnull(name), sometimes it contains " " (empty string). Please let me know if this is doable 2)  Is it possible to do this without using eventstat?       I have already used eventstats in the search, but for a different field      Will that cause any delays or issues?   Did you ever use multiple eventstats in your search?    Thank you so much for your help ip name location 1.1.1.1 name0 location-1 1.1.1.1 name1 location-1 1.1.1.2 name2 location-2 1.1.1.2 name0 location-20 1.1.1.3 name0 location-3 1.1.1.3 name3 location-3 1.1.1.4 name4 location-4 1.1.1.4 name4b location-4 1.1.1.5 name0 location-0 1.1.1.6 name0 location-0 1.1.1.7   location-7 | makeresults format=csv data="ip, name, location 1.1.1.1, name0, location-1 1.1.1.1, name1, location-1 1.1.1.2, name2, location-2 1.1.1.2, name0, location-20 1.1.1.3, name0, location-3 1.1.1.3, name3, location-3 1.1.1.4, name4, location-4 1.1.1.4, name4b, location-4 1.1.1.5, name0, location-0 1.1.1.6, name0, location-0 1.1.1.7,,location-7" | eventstats dc(name) AS dc BY ip | where name!="name0" OR dc=0 OR (name=="name0" AND dc=1)   ... View more

Hi @PickleRick  When I ran the following command, the dc returned 6 for each row     | eventstats dc(name) as dc     dc ip location name 6 1.1.1.1 location-1 name0 6 1.1.1.1 location-1 name1 6 1.1.1.2 location-2 name2 6 1.1.1.2 location-20 name0 6 1.1.1.3 location-3 name0 6 1.1.1.3 location-3 name3 6 1.1.1.4 location-4 name4 6 1.1.1.4 location-4 name4b 6 1.1.1.5 location-0 name0 6 1.1.1.6 location-0 name0 So, the output are still missing1.1.1.5 and 1.1.1.6   Only name0 that exists on multiple rows should be removed.     Thanks for your help     | where name!="name0" OR (name=="name0" AND dc=1)     output dc ip location name 6 1.1.1.1 location-1 name1 6 1.1.1.2 location-2 name2 6 1.1.1.3 location-3 name3 6 1.1.1.4 location-4 name4 6 1.1.1.4 location-4 name4b Expected output: ip location name 1.1.1.1 location-1 name1 1.1.1.2 location-2 name2 1.1.1.3 location-3 name3 1.1.1.4 location-4 name4 1.1.1.4 location-4 name4b 1.1.1.5 location-0 name0 1.1.1.6 location-0 name0 ... View more

Hi @PickleRick  Sorry I missed another condition. I also updated the initial post. The name0 is not in order. The dedup/filter should not be applied  to IPs that doesn't contain "name0" AND it should not be applied to unique IP that has "name0" So,  unique IP like 1.1.1.5 and 1.1.1.6 that has "name0" needs to be remained in the data.   What I did now is to filter out statically, but another IP could show up with the same pattern.   Thank you again for your help Data: ip name location 1.1.1.1 name0 location-1 1.1.1.1 name1 location-1 1.1.1.2 name2 location-2 1.1.1.2 name0 location-20 1.1.1.3 name0 location-3 1.1.1.3 name3 location-3 1.1.1.4 name4 location-4 1.1.1.4 name4b location-4 1.1.1.5 name0 location-0 1.1.1.6 name0 location-0   Expected output: ip name location 1.1.1.1 name1 location-1 1.1.1.2 name2 location-2 1.1.1.3 name3 location-3 1.1.1.4 name4 location-4 1.1.1.4 name4b location-4 1.1.1.5 name0 location-0 1.1.1.6 name0 location-0   | makeresults format=csv data="ip, name, location 1.1.1.1, name0, location-1 1.1.1.1, name1, location-1 1.1.1.2, name2, location-2 1.1.1.2, name0, location-20 1.1.1.3, name0, location-3 1.1.1.3, name3, location-3 1.1.1.4, name4, location-4 1.1.1.4, name4b, location-4 1.1.1.5, name0, location-0 1.1.1.6, name0, location-0"     ... View more

Hi @PickleRick  Thank you for your help. I also updated the original post. The name0 is not in order. The dedup/filter should not be applied  to IP that doesn't contain "name0" Data: ip name location 1.1.1.1 name0 location-1 1.1.1.1 name1 location-1 1.1.1.2 name2 location-2 1.1.1.2 name0 location-20 1.1.1.3 name0 location-3 1.1.1.3 name3 location-3 1.1.1.4 name4 location-4 1.1.1.4 name4b location-4 Expected output: ip name location 1.1.1.1 name1 location-1 1.1.1.2 name2 location-2 1.1.1.3 name3 location-3 1.1.1.4 name4 location-4 1.1.1.4 name4b location-4 | makeresults format=csv data="ip, name, location 1.1.1.1, name0, location-1 1.1.1.1, name1, location-1 1.1.1.2, name2, location-2 1.1.1.2, name0, location-20 1.1.1.3, name0, location-3 1.1.1.3, name3, location-3 1.1.1.4, name4, location-4 1.1.1.4, name4b, location-4"   ... View more

Can you share your experience (use case) where you change your timestamp to current search time? Thank you!! ... View more

Hi @yuanliu  1) If I left join CSV and subsearch that have the same field name, will the data from subsearch rewrite the data from the CSV in that field? In my example above is the "source" field. I added this for tracking purposes.  2) I also found out that keepempty=true doesn't always work in dedup. Have you ever experienced the same? Thank you again for your help. ... View more

Hi @yuanliu  Thanks for the suggestion. The option keepempty=true is something new I learned, I wish stats value() also has that option. However, when I tried keepempty=true, it added a lot more delay (3x) compare to using only dedup, perhaps maybe because I have so many fields. I've been using fillnull to keep empty field. The reason is although one field is empty, I still want to keep the other field. Your way of using foreach to re-assigned the field to null() is awesome. Thanks for showing me this trick.  Is there any benefits to move "UNPSEC" back to null()? I usually just gave it "N/A" for string, and 0 for numeric. I appreciate your help.  Thanks ... View more

Hi @yuanliu  Thank you again for your suggestion Below I posted my sample search closer to the real search, where I have multiple subnets in "search filter" and additional field filter. When I removed the "search ip filter" and moved it up next to index=risk,  the search is slower 3 seconds, but the results are the same. 1) What is the difference between using "| search ip=" and "ip="?   They give the same outcome 2) Sorry about not mentioning dedup. Because dedup will remove any rows that have empty/null fields, so I put the dedup after join and adding "fillnull" command If I move it to each subsearch, I would need to add fillnull command for each subsearch and it's probably adding a delay.  What do you think? I appreciate your suggestion again. Thanks Before  removing "filter ip"       | inputlookup host.csv | search (ip="10.1.0.0/16" OR ip="10.2.0.0/16" OR ip="10.3.0.0/16" OR ip="10.4.0.0/16" OR ip="10.5.0.0/16" OR ip="10.6.0.0/16") | rename ip_address as ip | join max=0 type=left ip [ search index=risk | fields ip risk score contact | where isnotnull(ip) AND isnotnull(risk) AND isnotnull(score) | search (ip="10.1.0.0/16" OR ip="10.2.0.0/16") AND (company="compA" OR company="compB") ] | join max=0 type=left ip [ search index=risk ip="10.2.0.0/16" | fields ip risk score contact | where isnotnull(ip) AND isnotnull(risk) AND isnotnull(score) | search (ip="10.3.0.0/16" OR ip="10.4.0.0/16") AND (company="compA" OR company="compB") ] | join max=0 type=left ip [ search index=risk ip="10.3.0.0/16" | fields ip risk score contact | search (ip="10.5.0.0/16" OR ip="10.6.0.0/16") AND (company="compA" OR company="compB") ] | fillnull value0 score | fillnull value="N/A" ip risk contact | dedup ip risk score contact | table ip, host, risk, score, contact         After  removing "filter ip"  (3 seconds slower)         | inputlookup host.csv | search (ip="10.1.0.0/16" OR ip="10.2.0.0/16" OR ip="10.3.0.0/16" OR ip="10.4.0.0/16" OR ip="10.5.0.0/16" OR ip="10.6.0.0/16") | rename ip_address as ip | join max=0 type=left ip [ search index=risk (ip="10.1.0.0/16" OR ip="10.2.0.0/16") AND (company="compA" OR company="compB") | fields ip risk score contact | where isnotnull(ip) AND isnotnull(risk) AND isnotnull(score) ] | join max=0 type=left ip [ search index=risk ip="10.2.0.0/16" (ip="10.3.0.0/16" OR ip="10.4.0.0/16") AND (company="compA" OR company="compB") | fields ip risk score contact | where isnotnull(ip) AND isnotnull(risk) AND isnotnull(score) ] | join max=0 type=left ip [ search index=risk ip="10.3.0.0/16" (ip="10.5.0.0/16" OR ip="10.6.0.0/16") AND (company="compA" OR company="compB") | fields ip risk score contact ] | fillnull value0 score | fillnull value="N/A" ip risk contact | dedup ip risk score contact | table ip, host, risk, score, contact           ... View more

Hi @yuanliu, Thank you again for your analysis and suggestion. The environment where I am working is very restrictive about making changes to limits.conf due to a resource problem. The index in the real data set I am working on has more than 1 million rows in a1 day time frame, I got it down to 150k after filtering out with specific subnets, fields, and dedups.  If this is the case, Is splitting the sub search for the join the only way to it?  Does the following search for splitting look correct? Please let me know if you have a better idea or workaround.  The real data has a lot more than 3 IP subnets 🙂    Thank you again. | inputlookup host.csv | rename ip_address as ip | eval source="csv" | join max=0 type=left ip [ search index=risk | fields ip risk score contact | search ip="10.1.0.0/16" | eval source="risk1" ] | join max=0 type=left ip [ search index=risk | fields ip risk score contact | search ip="10.2.0.0/16" | eval source="risk2" ] | join max=0 type=left ip [ search index=risk | fields ip risk score contact | search ip="10.3.0.0/16" | eval source="risk3" ] | table ip, host, risk, score, contact ... View more

Hi @yuanliu, I appreciate your help.  I accepted your solution. I tried your solution and it worked, however the subsearch index hit 50k max rows, so I split the join based on subnets. Is this the right way to do it?    See below I split into 3 join, each join has a filter based on subnets, but the issue is I don't know if the subnets will hit max 50k rows in the future, and I will have to manually adjust. Do you have any other suggestion ? Thanks again | inputlookup host.csv | rename ip_address as ip | eval source="csv" | join max=0 type=left ip [ search index=risk | fields ip risk score contact | search ip="10.1.0.0/16" | eval source="risk1" ] | join max=0 type=left ip [ search index=risk | fields ip risk score contact | search ip="10.2.0.0/16" | eval source="risk2" ] | join max=0 type=left ip [ search index=risk | fields ip risk score contact | search ip="10.3.0.0/16" | eval source="risk3" ] | table ip, host, risk, score, contact ... View more

Hello @yuanliu  Can you please help on my other question? This is more closer to the real data and it has complexity since it's involving multiple fields https://community.splunk.com/t5/Splunk-Search/How-do-I-quot-Left-join-quot-by-appending-CSV-to-an-index-in/m-p/696908#M236826 I think I solved this, but I wonder if there is a way to do it without merging the string and mvexpand I appreciate your help.  Thank you so much ... View more

Hello @yuanliu  I tested and it worked fine for the sample. I accepted your suggestion as solution. Thank you for your help. 1) Max 50k rows When I tested with the real data, I found out that the sub search CSV file is limited to 50k rows.  I need the CSV file as my baseline for left join, so if the file has 100k rows, then the expected result after left join is 100k rows (with additional column from index). a) What do you suggest to fix this issue?    (modifying limits.conf is not allowed) b) Will splitting the CSV work? 2) Join command Do you think join command can work on my case? I tested it your solution using join in real data, but it always gave me the result as inner join, instead of left join,  although I already specified join type=left In the solution you provided index will be treated as left data because it's specified first How do I make the CSV as left data?      I appreciate your help. Thanks ... View more

I appreciate the explanation and example. The search that I have is very long and doing a lot of calculation, so it's not that easy to do your suggestion I've been doing similar thing, but much simpler I just decode the URL using URL decoder, then open a new search and paste the search. Thank you for your suggestion. ... View more

Hi @KendallW  I tried your suggestion with real data (100k rows  CSV).  I expected the left side as a baseline and the number of rows will remain the same ,   but the number of rows doesn't match the CSV.  In my initial example. My expected output is 4 rows, same number of rows in the host.CSV. Please suggest.  I appreciate your help. Thank you. My expected output - yellow and green circle  ip_address host owner 10.1.1.1 host1   10.1.1.2 host2   10.1.1.3 host3 owner3 10.1.1.4 host4 owner4         ... View more

Hi @yuanliu  Thanks for your help. It's been a while since the last time I saw you, I hope you're doing fine. 1)  I ran the emulation and the result included 10.1.1.5 from the index=owner (right join).  My expected output is host.csv plus the owner data from index=owner only if the IP matches.  (See below) 2)  When I tested with real data (100k rows  CSV), the result reduced to 30k rows. I expected the left side as a baseline and the number of rows will remain the same.    Should I flip the logic?   (inputlookup first, then append the index) I actually tried to flip the logic, but after the append, the number of rows still doesn't match the CSV. (it's close) I appreciate your help. Thank you Emulation result My expected output - yellow and green circle  ip_address host owner 10.1.1.1 host1   10.1.1.2 host2   10.1.1.3 host3 owner3 10.1.1.4 host4 owner4     ... View more