topic Re: how to add a column "seen in last 24 hours" to my current report and verify the list of hosts from the csv file were reporting into splunk from last 24 hours or not? in Getting Data In

how to add a column "seen in last 24 hours" to my current report and verify the list of hosts from the csv file were reporting into splunk from last 24 hours or not?

pavanae — Tue, 29 Sep 2020 15:43:00 GMT

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 :-

Re: how to add a column "seen in last 24 hours" to my current report and verify the list of hosts from the csv file were reporting into splunk from last 24 hours or not?

somesoni2 — Mon, 11 Sep 2017 21:31:05 GMT

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\") "

| 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

Re: how to add a column "seen in last 24 hours" to my current report and verify the list of hosts from the csv file were reporting into splunk from last 24 hours or not?

koshyk — Mon, 11 Sep 2017 21:35:16 GMT

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

Re: how to add a column "seen in last 24 hours" to my current report and verify the list of hosts from the csv file were reporting into splunk from last 24 hours or not?

woodcock — Mon, 11 Sep 2017 21:36:38 GMT

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

Re: how to add a column "seen in last 24 hours" to my current report and verify the list of hosts from the csv file were reporting into splunk from last 24 hours or not?

pavanae — Tue, 12 Sep 2017 14:02:10 GMT

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"

Re: how to add a column "seen in last 24 hours" to my current report and verify the list of hosts from the csv file were reporting into splunk from last 24 hours or not?

pavanae — Tue, 12 Sep 2017 14:05:48 GMT

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)

Re: how to add a column "seen in last 24 hours" to my current report and verify the list of hosts from the csv file were reporting into splunk from last 24 hours or not?

pavanae — Tue, 12 Sep 2017 14:09:30 GMT

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

Re: how to add a column "seen in last 24 hours" to my current report and verify the list of hosts from the csv file were reporting into splunk from last 24 hours or not?

woodcock — Tue, 12 Sep 2017 14:18:27 GMT

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.

Re: how to add a column "seen in last 24 hours" to my current report and verify the list of hosts from the csv file were reporting into splunk from last 24 hours or not?

pavanae — Tue, 12 Sep 2017 14:30:23 GMT

Please see the my updated query in the question and let me know how the final query could be to solve my question?

Re: how to add a column "seen in last 24 hours" to my current report and verify the list of hosts from the csv file were reporting into splunk from last 24 hours or not?

pavanae — Tue, 12 Sep 2017 14:30:35 GMT

Please see the my updated query in the question and let me know how the final query could be to solve my question?

Re: how to add a column "seen in last 24 hours" to my current report and verify the list of hosts from the csv file were reporting into splunk from last 24 hours or not?

pavanae — Tue, 12 Sep 2017 14:31:27 GMT

Please see the my updated query in the question and let me know how the final query could be to solve my question?

Re: how to add a column "seen in last 24 hours" to my current report and verify the list of hosts from the csv file were reporting into splunk from last 24 hours or not?

somesoni2 — Tue, 12 Sep 2017 14:46:51 GMT

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

Re: how to add a column "seen in last 24 hours" to my current report and verify the list of hosts from the csv file were reporting into splunk from last 24 hours or not?

pavanae — Tue, 12 Sep 2017 22:49:22 GMT

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