https://community.splunk.com/t5/Reporting/Check-Critical-Assets-that-are-Logging/m-p/694416#M12575 <P>Hi All,<BR /><BR />So I have a lookup table with the following fields: FQDN, Hostname, and IP. I need to check to see which of these assets in the lookup table are logging (about 700 assets) and which aren't in the last 7 days. I used the following basic SPL to get a list of hosts which are logging:</P><P>&nbsp;</P><LI-CODE lang="markup">| tstats earliest(_time) latest(_time) count where index=* earliest=-7d by host </LI-CODE><P>&nbsp;</P><P>The issue I'm having is that the host output in the above SPL comes through in different formats, it may be a FQDN or a Hostname, or an IP address. How do I use my lookup table to check if the assets in the lookup table are logging without having to do 3 joins on FQDN, Hostname and IP? Here was a SPL query that somewhat worked but it is too inefficient:</P><P>&nbsp;</P><LI-CODE lang="markup">| inputlookup lookup.csv | eval FQDN=lower(FQDN) | eval Hostname=lower(Hostname) | join type=left FQDN [ |tstats latest(_time) as lastTime where index=* earliest=-7d by host | rename host as FQDN | eval FQDN=lower(FQDN) | eval Days_Since_Last_Log = round((now() - lastTime) / 86400) | convert ctime(lastTime) ] | join type=left Hostname [ |tstats latest(_time) as lastTime where index=* earliest=-7d by host | rename host as Hostname | eval Hostname=lower(Hostname) | eval Days_Since_Last_Log = round((now() - lastTime) / 86400) | convert ctime(lastTime) ] | join type=left IP[ |tstats latest(_time) as lastTime where index=* earliest=-7d by host | rename host as IP | eval IP=lower(IP) | eval Days_Since_Last_Log = round((now() - lastTime) / 86400) | convert ctime(lastTime) ] | rename lastTime as LastTime | fillnull value="NULL" | table FQDN, Hostname, IP, Serial, LastTime, Days_Since_Last_Log</LI-CODE><P>&nbsp;</P><P>I'm somewhat new to Splunk so thank you for the help!</P> Fri, 26 Jul 2024 14:39:29 GMT Zer0F8th 2024-07-26T14:39:29Z https://community.splunk.com/t5/Reporting/Check-Critical-Assets-that-are-Logging/m-p/694416#M12575 <P>Hi All,<BR /><BR />So I have a lookup table with the following fields: FQDN, Hostname, and IP. I need to check to see which of these assets in the lookup table are logging (about 700 assets) and which aren't in the last 7 days. I used the following basic SPL to get a list of hosts which are logging:</P><P>&nbsp;</P><LI-CODE lang="markup">| tstats earliest(_time) latest(_time) count where index=* earliest=-7d by host </LI-CODE><P>&nbsp;</P><P>The issue I'm having is that the host output in the above SPL comes through in different formats, it may be a FQDN or a Hostname, or an IP address. How do I use my lookup table to check if the assets in the lookup table are logging without having to do 3 joins on FQDN, Hostname and IP? Here was a SPL query that somewhat worked but it is too inefficient:</P><P>&nbsp;</P><LI-CODE lang="markup">| inputlookup lookup.csv | eval FQDN=lower(FQDN) | eval Hostname=lower(Hostname) | join type=left FQDN [ |tstats latest(_time) as lastTime where index=* earliest=-7d by host | rename host as FQDN | eval FQDN=lower(FQDN) | eval Days_Since_Last_Log = round((now() - lastTime) / 86400) | convert ctime(lastTime) ] | join type=left Hostname [ |tstats latest(_time) as lastTime where index=* earliest=-7d by host | rename host as Hostname | eval Hostname=lower(Hostname) | eval Days_Since_Last_Log = round((now() - lastTime) / 86400) | convert ctime(lastTime) ] | join type=left IP[ |tstats latest(_time) as lastTime where index=* earliest=-7d by host | rename host as IP | eval IP=lower(IP) | eval Days_Since_Last_Log = round((now() - lastTime) / 86400) | convert ctime(lastTime) ] | rename lastTime as LastTime | fillnull value="NULL" | table FQDN, Hostname, IP, Serial, LastTime, Days_Since_Last_Log</LI-CODE><P>&nbsp;</P><P>I'm somewhat new to Splunk so thank you for the help!</P> Fri, 26 Jul 2024 14:39:29 GMT https://community.splunk.com/t5/Reporting/Check-Critical-Assets-that-are-Logging/m-p/694416#M12575 Zer0F8th 2024-07-26T14:39:29Z https://community.splunk.com/t5/Reporting/Check-Critical-Assets-that-are-Logging/m-p/694425#M12576 <P>Hi&nbsp;<a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/270518">@Zer0F8th</a>,</P><P>you have to start from the main search, please try this:</P><LI-CODE lang="markup">| tstats count WHERE index=* earliest=-7d BY host | append [ | inputlookup lookup.csv | eval count=0 | fields FQDN count ] | append [ | inputlookup lookup.csv | eval count=0 | fields IP count ] | append [ | inputlookup lookup.csv | eval count=0 | fields Hostname count ] | eval host=coalesce(host, FQDN, IP, Hostname) | stats sum(count) AS total BY host | where total=0</LI-CODE><P>Ciao.</P><P>Giuseppe</P><P>&nbsp;</P> Fri, 26 Jul 2024 15:00:16 GMT https://community.splunk.com/t5/Reporting/Check-Critical-Assets-that-are-Logging/m-p/694425#M12576 gcusello 2024-07-26T15:00:16Z