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
Builder

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]

0 Karma
1 Solution

koshyk
Super Champion

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

View solution in original post

0 Karma

woodcock
Esteemed Legend

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
0 Karma

pavanae
Builder

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"

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

pavanae
Builder

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

0 Karma

pavanae
Builder

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

0 Karma

koshyk
Super Champion

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
0 Karma

pavanae
Builder

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)

0 Karma

pavanae
Builder

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

pavanae
Builder

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

0 Karma

pavanae
Builder

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...