topic Re: Splunk query to exclude the searched strings based on date and display in table in Splunk Search

Splunk query to exclude the searched strings based on date and display in table

asharmaeqfx — Wed, 30 Sep 2020 04:22:58 GMT

Hi Splukers,

I have a requirement to search for some filenames and display the missing files as per the date. Thus, i made up a query to look like

index=123 host=htrstef87 "string_1," "created" NOT client_ip="192.168.17.5" "String_examp_*" "xml.7z.pgp" | eval keyword=case(searchmatch("string_1L_2"),"string_1L_2",searchmatch("string_1L_21"),"string_1L_21",searchmatch("string_1L_22"),"string_1L_22",searchmatch("string_1L_23"),"string_1L_23",searchmatch("string_1L_24"),"string_1L_24") | eval Filestatus=if(like(keyword, "string_1L%"), "fileFound", "Filenotfound") |eval DateReport= date_month."-".date_year| stats values(keyword), values(FileName), values(Filesize) by DateReport | where Filesize>0

This displays all the filenames with all the data. But the requirement is to match the keyword and check them every month at certain date and send them if any files are missing or no bytes (filesize).

Any help is much appreciated.

note: I am running splunk 6.5.3 and thus queries like where(in) does not work for me.

Thanks,
Amit

Re: Splunk query to exclude the searched strings based on date and display in table

anmolpatel — Mon, 02 Mar 2020 23:39:50 GMT

Write a saved search that executes on a cron schedule ?
The result can then be displayed based on requirement (dashboard panel / email etc.)

Re: Splunk query to exclude the searched strings based on date and display in table

asharmaeqfx — Wed, 04 Mar 2020 02:49:22 GMT

yes but i still does not show me missing files. Rather all the files which came fine.

Re: Splunk query to exclude the searched strings based on date and display in table

to4kawa — Thu, 05 Mar 2020 07:25:56 GMT

Sample query:

| makeresults count=1000
| eval Filenames=trim("string_1L_2".(random() % 4),"0")
| streamstats count
| stats count by Filenames
| append [ | makeresults
| eval counter=mvappend("0",mvrange(1,4),"5")
| mvexpand counter
| eval Filenames=trim("string_1L_2".counter,"0")
| table Filenames]
| stats dc(count) as FileExists by Filenames
| eval FileExists=case(FileExists=1,"yes",FileExists=0,"no")

Recommend:

index=123 host=htrstef87 "string_1," "created" NOT client_ip="192.168.17.5" "String_examp_*" "xml.7z.pgp" 
| eval keyword=case(searchmatch("string_1L_2"),"string_1L_2",searchmatch("string_1L_21")
    ,"string_1L_21",searchmatch("string_1L_22"),"string_1L_22",searchmatch("string_1L_23")
    ,"string_1L_23",searchmatch("string_1L_24"),"string_1L_24") 
| eval DateReport=strftime(_time, "%Y-%m") 
| eval Filename=coalesce(FileName,keyword) 
| stats count by DateReport Filename 
| append 
    [| makeresults 
    | eval counter=mvrange(0,5) 
    | mvexpand counter 
    | eval Filename=trim("string_1L_2".counter,"0") 
    | table Filename] 
| stats dc(count) as FileExists by Filename 
| eval FileExists=case(FileExists=1,"yes",FileExists=0,"no")

I'm not sure your original field name. if there is a few typo, please amend it.

Re: Splunk query to exclude the searched strings based on date and display in table

to4kawa — Thu, 05 Mar 2020 07:25:57 GMT

index=123 host=htrstef87 "string_1," "created" NOT client_ip="192.168.17.5" "String_examp_*" "xml.7z.pgp" 
| eval DateReport= strftime(_time, "%Y-%m") 
| stats count(eval(isnull(FileName))) as missing_File_count count(eval(searchmatch("string_1L"))) as File_count by DateReport

Re: Splunk query to exclude the searched strings based on date and display in table

asharmaeqfx — Wed, 30 Sep 2020 04:29:33 GMT

This works and displays the same result. But my requirement is there are five strings and i need see from the query and check if they are missing. Currently i need the keywords in a column and one more column shows whether it exists or not

Keywords/Filenames FileExists
string_1L_2 Yes
string_1L_21 No
string_1L_22 Yes
string_1L_23 No
string_1L_25 Yes

Re: Splunk query to exclude the searched strings based on date and display in table

anmolpatel — Thu, 05 Mar 2020 23:51:50 GMT

I've taken @to4kawa 's initial query and made modification to what you're looking for

| makeresults count=10000 
| eval COMMENT= "Randomly generate file names with the below string names"
| eval File="string_1L_".(random() % 8)
| eval File="string_L_".(random() % 8)

| eval COMMENT= "For filename with string_1L_2 create file in series from 20-28"
| append 
    [| makeresults 
    | eval counter=mvappend("0",mvrange(1,8),"8") 
    | mvexpand counter 
    | eval File=trim("string_1L_2".counter,"1") 
    | table File] 
| stats count by File

| eval COMMENT = " Have replaced searchmatch function with match function using regex to check if you've a file with the particular string. This avoids multiple checks. You can replace the regex .*string_1L_2.* with .*string_1L_ .* and this will match with all values that get generated above, to test further."
| eval FileExists=if(match(File, ".*string_1L_2.*"),"Yes", "No")
| table File FileExists

The eval I've above for File Exists, effectively replaces what you're doing here:

| eval keyword=case(searchmatch("string_1L_2"),"string_1L_2",searchmatch("string_1L_21"),"string_1L_21",searchmatch("string_1L_22"),"string_1L_22",searchmatch("string_1L_23"),"string_1L_23",searchmatch("string_1L_24"),"string_1L_24") 
| eval Filestatus=if(like(keyword, "string_1L%"), "fileFound", "Filenotfound")

You can add in your Filesize check after that.