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

[asharmaeqfx]

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

[anmolpatel]

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.

[to4kawa · Accepted solution]

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.

[to4kawa]

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

[asharmaeqfx]

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

[anmolpatel]

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

[asharmaeqfx]

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