I have a query which contains multiple csv files as lookup tables and their result contains list of hosts and their details as follows with a count of more than 900.
host status ip X Y Z
Now is there any way that I can add a column as below
host status ip X Y Z last_seen_in_24_hours
1 yes
2 no
3 yes
How to see whether the list of hosts from the csv file were reporting into splunk from last 24 hours as above?
Note :-
I am able to verify the last reporting time individually by searching as below but not sure how to update the details in the same report by using a query?
host="1"
Updated Query :-
| inputlookup csv1 | table host, status, ip, X, Y, Z | append [|inputlookup csv2 | fields host, status, ip, X, Y, Z] | join type=outer ip [inputlookup csv3 |rename ip_adress as ip]
Adding a column is very simple. Just do an eval and assign a dummy column. Example below
|inputlookup geo_attr_countries.csv | eval last_seen_in_24_hours="hello"
For your query, the exact command would be something like
|inputlookup <your_csv>
| join type=left host [|metadata type=hosts index=* ]
|eval lastTime=if(lastTime>0,lastTime,0)
|eval timeDiff=now()-lastTime
| eval last_seen_in_24_hours=if(timeDiff>86400,"NO","YES")
| eval lastReported=strftime(lastTime,"%F %T")
| table host,last_seen_in_24_hours,lastReported
Like this (use any timepicker
😞
Your Existing Search Here
| rename COMMENT AS "It should use several '|inputlookup append=t' statements"
| appendpipe [| metadata type=hosts
| eval lastSeen=max(lastTime, recentTime)
| eval secondsPerDay = 60*60*24
| eval now=now()
| eval last_seen_in_24_hours=if(((now - lastSeen) >= secondsPerDay), "yes", "no")
| table host last_seen_in_24_hours]
| stats values(*) AS * BY host
thanks for the response woodcock. Could you provide some more information for the second line. I don't exactly understood what it meant
| rename COMMENT AS "It should use several '|inputlookup append=t' statements"
You did not show us how you are pulling in the CSV files and I suspect that you are doing it poorly with join
or something. The comment is pointing out that you should be using a string of |inputcsv append=t
commands for that part.
Please see the my updated query in the question and let me know how the final query could be to solve my question?
please refer to the below question also which I had posted for the same requirement
https://answers.splunk.com/answers/572402/search-help-my-search-is-innacurately-showing-if-h.html
Adding a column is very simple. Just do an eval and assign a dummy column. Example below
|inputlookup geo_attr_countries.csv | eval last_seen_in_24_hours="hello"
For your query, the exact command would be something like
|inputlookup <your_csv>
| join type=left host [|metadata type=hosts index=* ]
|eval lastTime=if(lastTime>0,lastTime,0)
|eval timeDiff=now()-lastTime
| eval last_seen_in_24_hours=if(timeDiff>86400,"NO","YES")
| eval lastReported=strftime(lastTime,"%F %T")
| table host,last_seen_in_24_hours,lastReported
thanks for the response Koshyk. I have a totall of around 3 csv files with different host fields. Could you modify the existing query to include all the host's from 3 csv files(all host's fields combined count 1000)
Please see the my updated query in the question and let me know how the final query could be to solve my question?
You can try these (select timerange higher than last 24 h)
Slow performance
| inputlookup yourlookup.csv | table host status ip X Y Z
| map maxsearches=1000 "| tstats max(_time) as _time WHERE index=* host=$host$ by host | eval status=\"$status$\" | eval ip=\"$ip$\" | eval X=\"$X$\" | eval Y=\"$Y$\" | eval Z=\"$Z$\" | eval last_seen_in_24_hours=if(_time>=relative_time(now(),\"-24h\"),\"yes\",\"no\") "
OR
| tstats max(_time) as _time WHERE index=* [| inputlookup yourlookup.csv | table host ] by host | eval last_seen_in_24_hours=if(_time>=relative_time(now(),"-24h"),"yes","no") | lookup yourlookup.csv host OUTPUT ip status X Y Z
Thanks for the response somesoni2. I have got an error when tried with the first answer as below
Error in 'map' command: Unable to find saved search 'maxsearches=1000'.
Please see the my updated query in the question and let me know how the final query could be to solve my question?
Give this a try
| tstats max(_time) as _time WHERE index=* [| inputlookup csv1 | table host, status, ip, X, Y, Z | inputlookup csv2 append=t ] | table host ] by host | eval last_seen_in_24_hours=if(_time>=relative_time(now(),"-24h"),"yes","no") | inputlookup csv1 append=t | table host, status, ip, X, Y, Z | inputlookup csv2 append=t | join type=outer ip [inputlookup csv3 |rename ip_adress as ip] | stats values(*) as * by host